Efficient paging with large tables in sql 2008

Question

for tables with > 1,000,000 rows and possibly many many more !

haven't done any benchmarking myself so wanted to get the experts opinion.

Looked at some articles on row_number() but it seems to have performance implications

What are the other choices/alternatives ?

Lao

would you mind sharing the articles that show row_number() doesn't perform... – Forwardness

All but the most trivial queries require indexes and up to date statistics to perform well. – Forwardness

Duplicate: #1897936 – Stylize

here's the 1st link on performance on google sqlblogcasts.com/blogs/tonyrogerson/archive/2008/05/17/… – Lao

@marc_s - did you see the link above, here's another one - this one from a microsoftie weblogs.asp.net/eporter/archive/2006/10/17/… – Lao

@Kumar: thanks, interesting links! – Impetrate

Answer 1

We use row_number() to great effect and there hasn't really been any performance issues with it. The basic structure of our paginated queries looks like this:

WITH result_set AS (
  SELECT
    ROW_NUMBER() OVER (ORDER BY <ordering>) AS [row_number],
    x, y, z
  FROM
    table
  WHERE
    <search-clauses>
) SELECT
  *
FROM
  result_set
WHERE
  [row_number] BETWEEN a AND b

It works fine for us on tables with > 1,000,000 rows.

Extortion

i understand performance is a function of server load & server resources among others, pls see the two links in my comments above for an alternate pov – Lao

As I said, we actually use this in production on tables with > 1,000,000 records. Results come back in < 100ms (depending on the search criteria, of course). Of course performance is going to be a function of server load, and also your specific usage scenario. – Extortion

I suppose you have sufficient amounts of RAM, so that table is fully cached. I just tried with a simple table 'person (email, firstname, lastname)' containing 3.000.000 records but to paginate to last records it takes 6 seconds with above query. I am using sql server 2008 r2. Did I miss something. – Eucharis

I've used this method but prefer to only select row number and recordID into the CTE to save RAM, instead of the entire result set. The idea is then to use an INNER JOIN with the CTE to select the full result set of the recordIDs between the desired row numbers. I'm not sure if that is faster or not, but it does save RAM. – Illuminant