0

I have an SQL Server 2008 R2 database, and I have a table with a large number of rows, but only a few columns. The table currently has 84m records.

I'm looking for the most efficient way to query this table to get paged data sets. Really, I only need 30 records per page.

I can't use OFFSET/FETCH with this version of SQL Server. I also don't have admin access to change the table in any way.

The only other way I've found is using a sub query and ROW_NUMBER(), but that seems to query the whole data set before returning a subset. This is very slow indeed.

What is the best (fastest) way to query this table for paged results..?

Stephen Last
  • 5,491
  • 9
  • 44
  • 85
  • 1
    Reducing the size of your set before numbering the rows (by whatever means appropriate) would be your best bet. Nobody is going to laboriously page through 84M rows, after all, so there is no point to calculating what rows are on page 2 000 000. Even if a query returns a large total number of rows, typically only the very first or very last pages are going to be relevant; anything more just means the user needs a more specific filter. – Jeroen Mostert Sep 10 '20 at 12:48
  • Upload the execution plan of your `ROW_NUMBER()` attempt to [Paste The Plan](https://www.brentozar.com/pastetheplan/) and add the link to your question. Like OFFSET/FETCH, the duration will depend on indexing and the offset into the resultset. – Dan Guzman Sep 10 '20 at 12:49
  • I think Jeroen gave the best anwser, but maybe you'd be able to leverage partitioning the table and using [seek method](https://blog.jooq.org/2013/10/26/faster-sql-paging-with-jooq-using-the-seek-method/) – lukaszberwid Sep 10 '20 at 12:51
  • https://use-the-index-luke.com/sql/partial-results/fetch-next-page – jarlh Sep 10 '20 at 12:58
  • have a look at this post: https://stackoverflow.com/questions/187998/row-offset-in-sql-server – rBalzer Sep 10 '20 at 13:42

0 Answers0