Paging Large Datasets - SQL Server (Best Practice)

Question

I am interested in what the best practices are for paging large datasets (100 000+ records) using ASP.NET and SQL Server.

I have used SQL server to perform the paging before, and although this seems to be an ideal solution, issues arise around dynamic sorting with this solution (case statements for the order by clause to determine column and case statements for ASC/DESC order). I am not a fan of this as not only does it bind the application with the SQL details, it is a maintainability nightmare.

Opened to other solutions...

Thanks all.

Answer 1

If you use the Order by technique, every time you page through, you will cause the same load on the server because you running the query, then filtering the data.

When I have had the luxury of non-connection-pooled environments, I have created and left the connection open until paging is dnoe. Created a #Temp table on the connection with just the IDs of the rows that need to get back, and added and IDENTITY field to this rowset. Then do paging using this table to get the fastest returns.

If you are restricted to a connection-pooled environment, then the #Temp table is lost as soon as the connection is closed. In that case, you will have to cache the list of Ids on the server - never send them to the client to be cached.

Answer 2

Robert Cary has a good article on paging techniques at SQLServerCentral.com (free registration required to view).

Answer 3

I wanted to add a quick suggestion to Raj's answer. If you create a temp table with the format ##table, it will survive. However, it will also be shared across all connections.

If you create an Index on the column that will be sorted, the cost of this method is far lower.

Erick