What is the best procedure to implement paging in a gridview considering size of Record ?

Question

Hi all,

I have a table in sq server db having more than 1 million rows. I need to show those data in a gridview with pagination in an asp.net page.

Since the record amount is larger I need to boost performance of the page for showing data, implementing pagination.

What procedure should I follow to implement pagination?

Please help.

Answer 1

There are a number of ways to do it but the general concept is "fetch on demand". You do not need to fetch record 200 to 250 when displaying the first 50 in the first page.

There are some very intelligent implementations which mix Ajax in to improve user experience and also look-ahead and/or background fetching, e.g. fetch 100 for page-size of 50 so viewing next page is faster.

You might want to start from more straightforward ones, Google around you should be able to find.

A couple here:
Effective Paging with Large Amount of Data in ASP.NET
Effective Data Paging Using SQL Server 2005 and Microsoft's Enterprise Library

Answer 2

My favorite way is to use a page index and page size values as input parameters to return only a subset of records from the database. Check this book out, which explains all of this in detail and more. The arguments sortexpression, pageIndex, and pageSize can come right from your GridView and hopefully to a BLL and then DAL layer to the stored procedure or SQL Text query.

For SQL Server 2000 use temporary tables.

For Sql Server 2005 you can use the ROW_NUMBER() function, which returns a consecutively numbered sequence. This sequence starts from 1 and provides a unique number for each row returned. You use the page index and page size values to calculate the lower and upper bound of the rows to return from the results of the ROW_NUMBER() function. Here is an example using a stored procedure:

CREATE PROCEDURE dbo.GetSomeItems
(
   @PageIndex   int,
   @PageSize    int
)
AS
SET NOCOUNT ON

SELECT * FROM
(
    SELECT SomeItems.*,
      ROW_NUMBER() OVER (ORDER BY SomeCol) AS RowNum
    FROM SomeItems
) Records
    WHERE Records.RowNum BETWEEN (@PageIndex*@PageSize+1) AND ((@PageIndex+1)*@PageSize)
ORDER BY Records.SomeCol

Or using a SQL Text query:

public override List<SomeItemDetails> GetSomeItems(string sortExpression, int pageIndex, int pageSize)
{
    using (SqlConnection cn = new SqlConnection(this.ConnectionString))
    {
      sortExpression = EnsureValidCompoundSortExpression(sortExpression); 
      int lowerBound = pageIndex * pageSize + 1;
      int upperBound = (pageIndex + 1) * pageSize;
      string sql = string.Format(@"SELECT * FROM
       (
          SELECT SomeItems.*,
             ROW_NUMBER() OVER (ORDER BY {0}) AS RowNum
             FROM SomeItems
       ) Records
          WHERE Records.RowNum BETWEEN {1} AND {2}
          ORDER BY {0}", sortExpression, lowerBound, upperBound);
      SqlCommand cmd = new SqlCommand(sql, cn);
      cn.Open();
      return GetSomeItemCollectionFromReader(ExecuteReader(cmd));
    }
}