• Effective server side paging in SQL Server 2000

    by  • 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!

    About

    .NET developer at thetrainline.com, previously web developer at MRM Meteorite. Awarded a PhD in misbehaviour detection in wireless ad-hoc networks.A keen C# ASP.net developer bridging the gap with APIs and JavaScript frameworks, one web app at a time.

    http://www.paulkiddie.com

    2 Responses to Effective server side paging in SQL Server 2000

    1. MK
      March 23, 2009 at 2:47 pm

      hi, the link mentioned is no longer working. Would you happen to have the solution with you? Thanks.

    2. March 24, 2009 at 5:02 pm

      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.

    Leave a Reply

    Your email address will not be published. Required fields are marked *