Example below of the PageIndexChanging event
Protected Sub grdMyProducts_PageIndexChanging(ByVal sender As Object, ByVal e As System.Web.UI.WebControls.GridViewPageEventArgs) Handles grdMyProducts.PageIndexChanging grdMyProducts.PageIndex = e.NewPageIndex grdMyProducts.DataBind() End Sub
If your query like mine that is used to retrieve the data for your grid takes 5 seconds to execute, it will execute for 5 seconds if you go to page 2 because it retrieved all the data from SQL and then change the page index to display only 20 records depending on the page you clicked on.
In SQL Server 2012, they've added two new arguments called OFFSET and FETCH NEXT to retrieve a fixed number of records/rows. When I used this arguments for the same query to the database but the query only returned the 20 records I want to display on my grid, it execute in 1 second only.
Here's the syntax for a simple SELECT using OFFSET and FETCH NEXT
SELECT myColumn1, myColumn2, myColumn3 FROM myTable ORDER BY myColumn OFFSET myStartRow - 1 ROWS FETCH NEXT myNumOfRows ROWS ONLY
Now to show you the difference between my two queries.
SQL Server 2008 R2 Query. This query takes 5 seconds to execute.
SELECT myColumn1, myColumn2, myColumn3 FROM myTable ORDER BY myColumn1
SQL Server 2012 Query. This query takes 1 second to execute.
DECLARE @myPageSize AS INT = 20 DECLARE @myPageNumber AS INT = 1 SELECT myColumn1, myColumn2, myColumn3 FROM myTable ORDER BY myColumn1 OFFSET @myPageSize * (@myPageNumber - 1) ROWS FETCH NEXT @myPageSize ROWS ONLY
Below are more examples for the use of OFFSET and/or FETCH NEXT. There are 2 ways to select the first 20 rows, by using:
FETCH NEXT
SELECT myColumns FROM mtTable ORDER BY myColumn1 DESC OFFSET 0 ROWS FETCH NEXT 25 ROWS ONLY
FETCH FIRST
SELECT myColumns FROM myTable ORDER BY myColumn1 DESC OFFSET 0 ROWS FETCH FIRST 25 ROWS ONLY
You can also select all the records exempt for the first 20 records by excluding FETCH NEXT
SELECT myColumns FROM myTable ORDER BY myColumn1 DESC OFFSET 20 ROWS
A FETCH clause requires an OFFSET clause but the OFFSET clause doesn't require a FETCH clause.
: