Optimising Server-Side Paging - Part II

  • Great article Paul. This is useful stuff.

    Jason...AKA CirqueDeSQLeil
    I have given a name to my pain...MCM SQL Server, MVP
    Posting Performance Based Questions - Gail Shaw[/url]
    Learn Extended Events

  • CirquedeSQLeil (5/24/2010)

    Great article Paul. This is useful stuff.

    Appreciated Jason, thanks.

  • Thanks, you've won me over.

  • Great article. I have been trying to implement your ideas but have come unstuck with what I am doing. The issue revolves around implemenmting the key seek method when trying to get distinct results from the first query. ie

    with Keys as (

    SELECT distinct



    when using distinct you can't use the row_number over mothod as you don't get sequential results, though this can be resolved by adding another section below the keys part and moving the row_number to that.

    distinctKeys AS


    SELECT TOP (@page * @recsPerPage)

    rn = ROW_NUMBER() OVER (ORDER by '+@SQLOrderByTemp+')

    FROM keys sk


    With that working the problem is taht I can't figure out how to get the count of keys quickly. Because I am using the distinct in a way that means that I can't just get the pk back but have to use a number of columns you can't guarantee that the count will match the select.

    The only way to do this seems to ve to move the count sub query to the distinctKeys section and remove the TOP part of the Keys section. This works but obviously I am now having to get all the records back from keys which is not very efficient on large sets.

    I hope that makes sense! Any help on this would be much appreciated.



  • Hi Tim,

    I almost see what you're getting at, but it would make it a lot easier to help if you could provide a CREATE TABLE statement, and a few rows of sample data to illustrate.



  • ha I knew that was coming. This is a very rudimentry example, but it gives you an idea of the issue

    --create table #test (clientID int,contactID int,firstname varchar(255),lastname varchar(255),linkID int)

    --insert into #test VALUES (1,1,'tim','s',1)

    --insert into #test VALUES (1,1,'tim','s',2)

    --insert into #test VALUES (1,1,'tim','s',2)

    --select * FROM #test


    keys AS


    SELECT DISTINCT contactID,firstname,lastname,linkID

    ,(SELECt count(*) FROM #test) as allrecords

    FROM #test


    SELECT * FROM keys

    is that enough to make more sense of what I was saying?



  • Anyone got any thoughts on this?

Viewing 7 posts - 16 through 21 (of 21 total)

You must be logged in to reply to this topic. Login to reply