7

In Sql Server 2008, many options are available for database paging via stored procedure. For example, see here and here.

OPTIONS:

  1. ROW_NUMBER() function
  2. ROWCOUNT
  3. CURSORS
  4. temporary tables
  5. Nested SQL queries
  6. OTHERS

Paging using ROW_NUMBER() is known to have performance issues:

Please advise, which paging method has the best performance (for large tables with JOINs) ?

Please also provide links to relevant article(s), if possible.

Thank You.

dev
  • 1,291
  • 3
  • 18
  • 26

2 Answers2

3

One question you have to answer is if you want to display the total number of rows to the end user. To calculate the number of the last page, you also need the last row number.

If you can do without that information, a temporary table is a good option. You can select the pirmary key and use LIMIT to retrieve keys up to the key you're interested in. If you do this right, the typical use case will only retrieve the first few pages.

If you need the last page number, you can use ROW_NUMBER(). Using a temporary table won't be much faster because you can't use the LIMIT clause, making this strategy the equivalent of a ROW_NUMBER() calculation.

Andomar
  • 232,371
  • 49
  • 380
  • 404
  • +1 That is an important distinction to make - in practice I always need the total row count. Performance tests that do not include that are not useful to me. – D'Arcy Rittich Jul 03 '09 at 12:02
  • Thanks. Total number of rows will be required to generate page number. ROWCOUNT method seems to be the fastest, but requires unique sorting column (which is not unique in my case) – dev Aug 24 '09 at 05:55
0

We can get a rowcount using following query.

WITH data AS
(
      SELECT ROW_NUMBER() OVER (order by memberid ) AS rowid, memberid 
      FROM Customer
)
SELECT *, (select count(*) from data) AS TotalCount 
FROM data 
WHERE rowid > 20 AND  rowid <= 30
sjngm
  • 12,423
  • 14
  • 84
  • 114
raju
  • 11
  • 1
  • The OP knows that, he's asking what the best way is and explicitly links out to another place that describes problems (which links to yet another solution) with this approach. – Chris J Dec 16 '11 at 11:05