Effective server side paging in SQL Server 2000
by Paul Kiddie • July 14, 2008 • asp.net, c#, sql • 2 Comments
Recently a client asked me to implement paging on the administrators side of a CV/online application submission system. This essentially lists all the current applications in the system and other data (time of submission, personal details etc). The system has been well received and little thought was paid when developing it in order to scale it up in the long term, hence this webpage wasn’t necessarily as performant as it could be.
The table is implemented as a ASP.net repeater bound to Gentle.NET objects. The repeater itself doesnt have any inbuilt paging.
I could have used the PagedDataSource and hook it up to my collection of applications but the problem here is that the SQL command invoked by Gentle.NET would still return a full data set (the main bottleneck, currently stands at around 5000 applicants) which would be post-filtered. Instead, I wanted a server side solution, actually, a SQL Server 2000 side solution. It seems implementing server side paging in SQL Server 2005 is a bit easier with the use of the ROW_NUMBER function (http://www.davidhayden.com/blog/dave/archive/2005/12/30/2652.aspx)
Anyway, I came across this ingenious solution for SQL Server 2000 which doesn’t require Stored Procedures or the construction of a temporary table at http://www.themanaged.net/archive/2007/02/13/10.aspx, which solved my problems through effective use of the TOP command, and whats more is robust to filtering/sorting, ordering and joins!
hi, the link mentioned is no longer working. Would you happen to have the solution with you? Thanks.
hi MK.
Sorry about that. The approach I used in the end was a query in a query, of the form:
SELECT TOP recordCount * FROM table_name WHERE id NOT IN (SELECT TOP (pageNo-1*recordCount) id FROM table_name);
where recordCount is the number of rows you want to return, pageNo is the page of data you want to return, starting with pageNo=1 for the first page of data, table_name is the database table, and id is the autoincrementing unique id.
This solved the performance issues I was having with the naive query returning the entire dataset which was very large, which was then filtered on the web server data grid.