Which paging method (Sql Server 2008) for BEST performance ?

_{Thursday, July 2, 2009 6:29 PM}

Hello,

In Sql Server 2008, many options are available for database paging via stored procedure.
http://www.codeproject.com/KB/aspnet/PagingLarge.aspx
http://www.beansoftware.com/ASP.NET-Tutorials/Paging-Stored-Procedures.aspx

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: http://weblogs.asp.net/eporter/archive/2006/10/17/ROW_5F00_NUMBER_28002900_-OVER-Not-Fast-Enough-With-Large-Result-Set.aspx

**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.

_{Thursday, July 2, 2009 7:38 PM ✅Answered | 3 votes}

I would argue ROW_NUMBER() does not have a performance issue. 

In any case, SQL Server does not allow you to pick "I need rows 1000-1050".  The only way to do that is to select all the rows ordered the way you want, then only return rows 1000-1050.  The best approach for this is to use ROW_NUMBER().  Depending on your code and order and complexity of the query, performance can be extremely slow.

You can avoid some of this, but only selecting the KEYS, instead of the entire row.  Then using the key values to return 1000-1050.  For example:

SELECT ees.*
FROM Employees ees
JOIN
(SELECT EmployeeID, rownum= ROW_NUMBER()
FROM Employees
ORDER BY EmployeeLastName, EmployeeFirstname)
eelist ON eelist.EmployeeID = ees.EmployeeID
WHERE eelist.rownum BETWEEN 1000 AND 1050

You can further increase the speed by using the SET ROWCOUNT as described in: http://www.4guysfromrolla.com/webtech/042606-1.shtml

_{Monday, August 24, 2009 7:26 PM ✅Answered}

In an OLTP environment the "total rows" would be always changing.  If you are trying to give the user an option of going to the "last" page, you will be sorry if you pre-calculate the value of the last page.

In any concept of paging, you will need a unique identifier for records.  The columns sorted by do not need to be unique.

_{Friday, January 6, 2012 7:34 PM ✅Answered}

I don't think there is "one size fits all" answer.

If you must be able to select "result 100 - 109" on a set of possibly duplicate rows, then ROW_NUMBER() is probably the best approach (assuming you are on SQL Server 2005 or later).

If the only purpose of paging is to fetch the next block of 10 rows (or the previous block of 10 rows), and each row in your set has a unique id, then a sorted TOP 10 based on the key (assuming the key is indexed) would most likely outperform all other approaches.

So it really depends on your requirements. Do you need to know the total number of rows? If so, do you need the exact number, or will an approximation do just fine? Do you need to number all rows, or is getting the next block fine too? How many block do you have to be able to jump ahead? Only one, just a few, or to an arbitrary "page"? How do you want to handle new and deleted rows between a paging action? Etc.

In conclusion: it depends.

Gert-Jan

_{Friday, January 20, 2012 3:47 PM ✅Answered}

OPTIONS:

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

I vote for ROW_NUMBER().

Related articles:

http://www.sqlusa.com/bestpractices/sequencesubset/

http://www.sqlusa.com/bestpractices2005/addrownumbertotable/

UPDATE - SQL Server 2012 has built-in paging: OFFSET AND FETCH.

Kalman Toth
New Book: Beginner Database Design & SQL Programming Using Microsoft SQL Server 2016

_{Monday, August 24, 2009 2:40 PM}

Thanks. Total number of rows will be required to generate page number.

In above code, will using CTE improve performance ?

ROWCOUNT method seems to be the fastest, but requires unique sorting column.
Sorting columns are not unique in my case. Can I still use it ?

_{Friday, January 6, 2012 6:55 AM}

Hi Guys,

SET ROWCOUNT is to increare the performace of Query. It Causes SQL Server to stop processing the query after the specified number of rows are returned.

For Example:

SET ROWCOUNT 1  (Causes SQL Server to stop processing the query after the 1 of rows are returned)

To set this option off so that all rows are returned, specify SET ROWCOUNT 0.

http://msdn.microsoft.com/en-us/library/ms188774.aspx

Thanks Shiven:) If Answer is Helpful, Please Vote

_{Saturday, October 6, 2012 10:10 PM | 1 vote}

SQL Server 2012 has introduced a built-in paging method OFFSET AND FETCH.

BOL example:

USE AdventureWorks2012;
GO
-- Return all rows sorted by the column DepartmentID.
SELECT DepartmentID, Name, GroupName
FROM HumanResources.Department
ORDER BY DepartmentID;

-- Skip the first 5 rows from the sorted result set and return all remaining rows.
SELECT DepartmentID, Name, GroupName
FROM HumanResources.Department
ORDER BY DepartmentID OFFSET 5 ROWS;

-- Skip 0 rows and return only the first 10 rows from the sorted result set.
SELECT DepartmentID, Name, GroupName
FROM HumanResources.Department
ORDER BY DepartmentID
    OFFSET 0 ROWS
    FETCH NEXT 10 ROWS ONLY;

http://msdn.microsoft.com/en-us/library/ms188385.aspx#Offset

Kalman Toth
**New Book: Beginner Database Design & SQL Programming Using Microsoft SQL Server 2016
**

_{Wednesday, March 6, 2013 7:25 AM}

Establish permanent table using row number and over clause dynamic or otherwise ... Persist runid, row number result , pk Id .... (Assuming single table) given runid you have created a fixed index to quickly join a send back paged result.