Comparing performance for different SQL Server paging methods

Problem

Every DBA has worked on an application that does some sort of search and returns paged lists to the user. Each version of SQL Server has introduced new ways to return these paged lists (aside from the obvious of returning the entire list and let the application server handle the paging). This tip will look at the different methods that can be used in each version, starting with SQL Server 2000, and compare their performance in a SQL 2012 database.

Solution

For this comparison we are going to start all the way back with SQL Server 2000. In this version there wasn’t any specific functionality to help us with this task so the only option was to use a temporary table with an additional column to perform the paging. Moving on to SQL Server 2005 and 2008, we could make use of the ROW_NUMBER() function as well as a common table expression (CTE) or derived table. Finally, with SQL Server 2012, we have the new OFFSET and FETCH clause which we can use to perform the paging. In case you aren’t familiar with any of the options described above the following tips give a more detailed description as this tip will just focus on the performance of each method being run in a SQL Server 2012 environment. As well, the code samples used for this are based on queries from these tips.

• Page through SQL Server results with the ROWNUMBER() function
• SQL Server stored procedures to page large tables or queries
• SQL Server 2012 server side paging
• Overview of OFFSET and FETCH feature of SQL Server 2012

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.

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.

Next Steps

• Expand this test to see if there is any trend on a very large data set as your query pages further and further into the result set
• Test on your own sample data with more complex queries (ie. multiple joins)
• Investigate other uses for ROW_NUMBER(), CTEs and new OFFSET and FETCH clause

Ben Snaidero

Ben Snaidero has been a Database Administrator for just over 10 years. Starting out working mainly with Oracle he got into SQL Server in 2005 and has worked primarily with SQL Server for the last 3 years. His main focus with both Oracle and SQL Server is in the area of performance tuning.

• MSSQLTips Awards: Achiever (75+ tips) – 2018 | Author of the Year Contender – 2016-2017