16

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 ?

Kumar
  • 10,997
  • 13
  • 84
  • 134
  • would you mind sharing the articles that show row_number() doesn't perform... – Mitch Wheat Mar 15 '10 at 04:13
  • All but the most trivial queries require indexes and up to date statistics to perform well. – Mitch Wheat Mar 15 '10 at 04:22
  • Duplicate: http://stackoverflow.com/questions/1897436/row-number-over-not-fast-enough-with-large-result-set-any-good-solution – OMG Ponies Mar 15 '10 at 04:24
  • here's the 1st link on performance on google http://sqlblogcasts.com/blogs/tonyrogerson/archive/2008/05/17/non-recursive-common-table-expressions-performance-sucks-2-row-number-is-executed-number-of-cte-references-x-number-of-rows-from-the-anchor.aspx – Kumar Mar 15 '10 at 04:43
  • @marc_s - did you see the link above, here's another one - this one from a microsoftie http://weblogs.asp.net/eporter/archive/2006/10/17/ROW_5F00_NUMBER_28002900_-OVER-Not-Fast-Enough-With-Large-Result-Set.aspx – Kumar Mar 15 '10 at 06:36
  • @Kumar: thanks, interesting links! – marc_s Mar 15 '10 at 07:34

1 Answers1

19

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.

Dean Harding
  • 71,468
  • 13
  • 145
  • 180
  • 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 – Kumar Mar 16 '10 at 00:55
  • 2
    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. – Dean Harding Mar 16 '10 at 01:05
  • 1
    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. – broadband Dec 27 '13 at 11:34
  • 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. – ingredient_15939 Mar 25 '15 at 19:37