By: Ben Snaidero | Last Updated: 2012-05-15 | Comments (7) | Paging
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.
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
--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;
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|
|Query Middle Page|
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.
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.
- 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
Last Updated: 2012-05-15
About the author
View all my tips