Paginating a SQL query result set

  • gjryan59

    SSC-Addicted

    Points: 464

    Comments posted to this topic are about the item Paginating a SQL query result set

  • akljfhnlaflkj

    SSC Guru

    Points: 76202

    Seems kind of elaborate to replace:

    select top 10 column1, column2, column3 from table where column1 > 99 order by column1

    or

    select top 10 column1, column2, column3 from table where column1 > (99 + (page * 10) )order by column1

  • gbritton1

    SSCertifiable

    Points: 6520

    OFFSET/FETCH is the prefered method for pagination, I believe

  • trimjib

    SSC Veteran

    Points: 225

    As Ryan pointed out the real problem is with the query dataset not being utilized for subsequent page calls. The cost is in preparing the dataset, not in grabbing a few rows from it.

    Does Offset/Fetch notify SQL to optimize that aspect of pagination?

  • gjryan59

    SSC-Addicted

    Points: 464

    The reason it is written the way it is, is that the underling data may change between page changes on the mobile devices that this was written for. Hence not cashing the dataset and just paging through it.

  • gbritton1

    SSCertifiable

    Points: 6520

    trimjib (11/2/2015)


    As Ryan pointed out the real problem is with the query dataset not being utilized for subsequent page calls. The cost is in preparing the dataset, not in grabbing a few rows from it.

    Does Offset/Fetch notify SQL to optimize that aspect of pagination?

    The previous results would be cached.

Viewing 6 posts - 1 through 6 (of 6 total)

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