Pagination on MS SQL Server 2008: performance issue

Methods for pagination on SQL Server:

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

ROW_NUMBER() OVER function for pagination

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:

Source code

SELECT e.*
FROM Employees e
JOIN
(SELECT EmployeeID, rownum= ROW_NUMBER()
FROM Employees
ORDER BY LastName
) AS empOrdered
ON empOrdered.EmployeeID = e.EmployeeID
WHERE empOrdered.rownum BETWEEN 1000 AND 1050
ORDER BY empOrdered.rownum ASC

if we need to sort by another field Lastname, not a PK then SQL will look like

Source code

SELECT e.*
FROM Employees e
JOIN
(SELECT EmployeeID, ROW_NUMBER() Over (ORDER BY Lastname) AS rownum
FROM Employees
) AS empOrdered
ON empOrdered.EmployeeID = e.EmployeeID
WHERE empOrdered.rownum BETWEEN 1000 AND 1050
 
ORDER BY empOrdered.rownum ASC

or similar SQL using CTE:

Source code

WITH EmpOrdered
AS
(
SELECT id,
LastName, ..other_fields..,
ROW_NUMBER() OVER (ORDER BY LastName) AS rownum
FROM Employees
)
SELECT *
FROM EmpOrdered
WHERE rownum  BETWEEN 1000 AND 1050
 
ORDER BY empOrdered.rownum ASC

In another scenario we may use only one field in CTE to select and order so it will use only the index over that field:

Source code

WITH EmpOrdered AS
(   
SELECT
Lastname,
ROW_NUMBER() Over (ORDER BY Lastname) AS rownum  
FROM Employees
)
SELECT e.*
FROM Employees e
INNER JOIN EmpOrdered ON e.Lastname = EmpOrdered.Lastname
WHERE EmpOrdered.rownum BETWEEN 1000 AND 1050
ORDER BY EmpOrdered.rownum ASC

This method can be further improved using ROWCOUNT. Read this article: A More Efficient Method for Paging Through Large Result Sets

Read more:

http://www.codeproject.com/KB/aspnet/PagingLarge.aspx

http://www.4guysfromrolla.com/webtech/042606-1.shtml

http://www.codeproject.com/KB/aspnet/PagingLarge.aspx