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:

[codesyntax lang=”sql”]

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

[/codesyntax]

if we need to sort by another field Lastname, not a PK then SQL will look like
[codesyntax lang=”sql”]

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
[/codesyntax]

 
or similar SQL using CTE:
[codesyntax lang=”sql”]
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
[/codesyntax]

 
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:
[codesyntax lang=”sql”]
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
[/codesyntax]
 
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

Leave a Reply Cancel Reply