Paging large amounts of data in ASP.net / SQL2005

If you have a huge dataset, doing standard paging in ASP.net is definitely not the solution. In the normal paging methods all rows are retrieved in the application and the selection is done in code. So if you have 1.000.000 records and a page size of 20, every time you swap page you read 1.000.000 records and then select 20 of these (this is of course if you do paging the “Visual” way).

However if you have SQL 2005 there is a much, much better way of doing paging, albeit it requires some more coding, both on the web-frontend and in the database. The solution is based on the Common Table Expressions (CTE) and the Ranking functions (more specifically the ROW_NUMBER() function) introduced in SQL 2005.

[sql]
CREATE PROCEDURE [schema].[table_List]
@PageIndex int, — Page number from 0 to inf.
@PageSize int — The size of each page
AS
BEGIN
WITH Data AS (SELECT ROW_NUMBER() OVER (ORDER BY [sort order]) AS RowNumber, * FROM [schema].[table] WHERE [condition])
SELECT * FROM Data WHERE
(@PageIndex IS NULL) OR (@PageSize IS NULL) OR
(
RowNumber >= @PageIndex * @PageSize AND
RowNumber < (@PageIndex + 1) * @PageSize
)
END
[/sql]

What this does is create a CTE with all the data (same as in the "old" version) and add a ranking to this based on some sorting condition (fx. sort by primary key) and apply the ROW_NUMBER() function to this. So if we have a table like this:

And apply the SP “scheme” above with sorting by Name to this we would get:

Now we have a way of selecting a subset of the dataset based on absolute position within the dataset (which paging is actually all about).

Another thing we (most likely) need is to a method to return the total number of rows the query “would have returned”. This is to be able to display a “Page n of m”. It is actually possible to use the ObjectDataSource instead of the SqlDataSource (which you most likely would do already, if using the DataSource server controls at all) and have it “select a specific page” by using parameters along with a “select count method” (the SelectCountMethod) thereby making it possible to use standard ASP.NET server controls to paging in the database.

However while using DataSource server controls lets you do some RAD style VB-ish development – no I’m not biased – it is at the cost of “loosing control” of binding (fx. I have never figured out how to force a bind to occur with DataSource controls – I actually was in a situation where this was needed once). Besides this it is also at the cost of performing two calls to the SQL database (most likely): one for retrieving page count another for retrieving the specific page. But if using manual binding this can easier be accomplished in one step, simply by modifying the stored procedure to:

[sql]
CREATE PROCEDURE [schema].[table_List]
@PageIndex int, — Page number from 0 to inf.
@PageSize int — The size of each page
AS
BEGIN

SELECT COUNT([pk]) AS TotalCount FROM [schema].[table] WHERE [condition];

WITH Data AS (SELECT ROW_NUMBER() OVER (ORDER BY [sort order]) AS RowNumber, * FROM [schema].[table] WHERE [condition])
SELECT * FROM Data WHERE
(@PageIndex IS NULL) OR (@PageSize IS NULL) OR
(
RowNumber >= @PageIndex * @PageSize AND
RowNumber < (@PageIndex + 1) * @PageSize
)
END
[/sql]

If you anyway parse your queries (like I hope you do) into some business objects, then if using a IDataReader (and the Data Access Application Block from the Enterprise Library) could give you something like (0 = page index, 20 = page size):

[csharp]
Database db = DatabaseFactory.CreateDatabase(“con.string name”);
int totalCount = 0;
using (IDataReader reader = db.ExecuteReader(“some sp”, 0 /* page index*/, 20 /* pages ize */))
{
if (reader.Read())
{
totalCount = Convert.ToInt32(reader["TotalCount"]);
}
reader.NextResult();
while (reader.Read())
{
/* Instantiate and initialize objects */
}
}
[/csharp]

So now all we have to do is create some custom paging mechanisms in ASP.net that handles the page selection for us and then executes the SP when binding the data (this could be the topic of another post).