Learn more about SQL Server tools

mssqltips logo
 

Tutorials          DBA          Dev          BI          Career          Categories          Webcasts          Whitepapers          Today's Tip          Join

Tutorials      DBA      Dev      BI      Categories      Webcasts

DBA    Dev    BI    Categories

Next Webcast - Simple SQL Server Reporting - Click Here to Register
 

Comparing performance for different SQL Server paging methods


By:   |   Read Comments (7)   |   Related Tips: More > Paging

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.

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

SQL2000 - Paging Method - 1

SQL2000 - Paging Method - 2

SQL2000 - Paging Method - 3


SQL 2005/2008 Paging Method Using Derived Table

SQL2005-2008 - Derived table - 1

SQL2005-2008 - Derived table - 2


SQL 2005/2008 Paging Method Using CTE

SQL2005-2008 - CTE - 1

SQL2005-2008 - CTE - 2


SQL SERVER 2012

SQL2012 - Paging Method


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.


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


Last Update:


next webcast button


next tip button



About the author
MSSQLTips author Ben Snaidero Ben Snaidero has been a SQL Server and Oracle DBA for over 10 years and focuses on performance tuning.

View all my tips





Post a comment or let the author know this tip helped.

All comments are reviewed, so stay on subject or we may delete your comment. Note: your email address is not published. Required fields are marked with an asterisk (*).

*Name    *Email    Email me updates 


Signup for our newsletter
 I agree by submitting my data to receive communications, account updates and/or special offers about SQL Server from MSSQLTips and/or its Sponsors. I have read the privacy statement and understand I may unsubscribe at any time.



    



Sunday, August 26, 2018 - 8:43:05 AM - Dari Back To Top

Hi, 

In the SQL 2005/2008, can you use TOP for optimize the SELECTs?


Thursday, March 13, 2014 - 2:09:48 PM - Payal Zilaro Back To Top

Does the table have an index on the columns that is being used for ordering? 

I wrote a sample query to do performance comparison on a large table ~100 mill records returning records at about record 1 million, and the performance of CTE was several magnitudes faster. The  SQL 2012 paging offset has the same costs as returning all the records from the first page until the page being queried on. In my example, the table did have an index on the columns used for ordering.


Saturday, February 08, 2014 - 3:56:08 AM - Bron Back To Top

Thanks for doing this post - we were considering how 2012 could improve our app - this is one we'll definitely want to leverage.  Fero's point about COUNT is important since usually that has to be done in conjuction with paging but I don't think there is any alternative way to get a total row count and therefore doesn't really affect the results here.


Wednesday, February 06, 2013 - 6:26:24 AM - Fero Back To Top

With CTE how do you know whether there is another page ? What I found the largest bottleneck by far is COUNT(). I am not sure how to improve speed of count, especially if you need to know whether there is another page after this on or not. 


Wednesday, May 16, 2012 - 3:22:33 AM - Ahmad Back To Top

Goodish! Concise and Concrete!

Thank you!


Tuesday, May 15, 2012 - 5:43:16 PM - Dave Ballantyne Back To Top

f you use TOP operator with te 2005/2008 method, it is roughly comparable to OFFSET

http://sqlblogcasts.com/blogs/sqlandthelike/archive/2012/04/26/offset-without-offset.aspx

 


Tuesday, May 15, 2012 - 9:09:03 AM - Ranga Back To Top

Awesome! Very well written tip. Thanks for sharing.


Learn more about SQL Server tools