Comparing performance for different SQL Server paging methods

For this test we will run each paging method through a SQL Server 2012 database and compare their explain plans as well as some basic performance statistics using SQL Profiler. Here is the complete test script. Note: To update the query to fetch the first 50 records simply update the start/end variables.

--SQL 2000 Paging Method
DECLARE @Start INT
DECLARE @End INT
SELECT @Start = 14000,@End = 14050

            
CREATE TABLE #employees (RowNumber INT IDENTITY(1,1),
                LastName VARCHAR(100),FirstName VARCHAR(100),
                EmailAddress VARCHAR(100))

  
INSERT INTO #employees (LastName, FirstName, EmailAddress)
SELECT LastName, FirstName, EmailAddress 
  FROM Employee 
 ORDER BY LastName, FirstName, EmailAddress
SELECT LastName, FirstName, EmailAddress 
  FROM #employees
 WHERE RowNumber > @Start AND RowNumber <= @End

  
DROP TABLE #employees

  
GO

  
--SQL 2005/2008 Paging Method Using Derived Table
DECLARE @Start INT
DECLARE @End INT
SELECT @Start = 14000,@End = 14050

  
SELECT LastName, FirstName, EmailAddress
FROM (SELECT LastName, FirstName, EmailAddress,
      ROW_NUMBER() OVER (ORDER BY LastName, FirstName, EmailAddress) AS RowNumber
      FROM Employee) EmployeePage
WHERE RowNumber > @Start AND RowNumber <= @End
ORDER BY LastName, FirstName, EmailAddress
GO

  
--SQL 2005/2008 Paging Method Using CTE
DECLARE @Start INT
DECLARE @End INT
SELECT @Start = 14000,@End = 14050;

  
WITH EmployeePage AS
(SELECT LastName, FirstName, EmailAddress,
 ROW_NUMBER() OVER (ORDER BY LastName, FirstName, EmailAddress) AS RowNumber
 FROM Employee)
SELECT LastName, FirstName, EmailAddress
FROM EmployeePage
WHERE RowNumber > @Start AND RowNumber <= @End
ORDER BY LastName, FirstName, EmailAddress
GO

  
--SQL SERVER 2012
SELECT LastName, FirstName, EmailAddress
FROM Employee
ORDER BY LastName, FirstName, EmailAddress
OFFSET 14000 ROWS
FETCH NEXT 50 ROWS ONLY;

Explain Plans

First let's take a look at the explain plans for each of these queries. The following explain plans appear in the same order as the queries in the test script above.

SQL 2000 Paging Method

---

SQL 2005/2008 Paging Method Using Derived Table

---

SQL 2005/2008 Paging Method Using CTE

---

SQL SERVER 2012

---

After analyzing these explain plans it's pretty safe to assume that when we look at the profiler results, the SQL Server 2000 query is probably going to perform the worst given that it basically has to run two queries and the second query is a complete scan of a temporary table. Interesting thing I noted after reviewing these explain plans was that the SQL Server 2005/2008 version generates the exact same query plan for both the CTE and Derived table version. Comparing these two plans to the SQL Server 2012 version we might expect that the 2012 version will perform slightly better given that it only has to perform one sort. Let's take a look at the performance numbers to confirm.

SQL Profiler Results

To analyze the performance we'll run the above queries for the first page returned as well as for a page of data somewhere in the middle of the result set. I ran the queries above 10 times and the numbers in the chart below represent the averages of the statistics collected with SQL Profiler.

Query First Page
Version	CPU (ms)	Reads	Writes	Duration
2000	328	24427	444	327
2005/2008 Derived	125	9242	0	178
2005/2008 CTE	127	9242	0	173
2012	46	8733	0	44
Query Middle Page
Version	CPU (ms)	Reads	Writes	Duration
2000	312	24658	444	313
2005/2008 Derived	157	9472	0	173
2005/2008 CTE	156	9472	0	175
2012	125	8963	0	135

Looking at these SQL Profiler results we can confirm what we had suspected after viewing the explain plans. The SQL Server 2000 version performs much poorer than the other methods and the new OFFSET and FETCH clause performs only slightly better that it's 2005/2008 conterpart using ROW_NUMBER(). The only exception to this is that when querying the first page of the result set, which all applications usually do, it performs quite a bit better than all the other methods. Almost three times faster than using the ROW_NUMBER() function.

Summary

As we can see from all the results, the new SQL Server 2012 feature is the best option for implementing a server-side page of a query result. The other added benefit is that it is also by far the most straightforward and easy to understand of all the methods.

from : https://www.mssqltips.com/sqlservertip/2696/comparing-performance-for-different-sql-server-paging-methods/