SQL SERVER – Server Side Paging in SQL Server Denali Performance Comparison

Earlier, I have written about SQL SERVER – Server Side Paging in SQL Server 2011 – A Better Alternative. I got many emails asking for performance analysis of paging. Here is the quick analysis of it.

The real challenge of paging is all the unnecessary IO reads from the database. Network traffic was one of the reasons why paging has become a very expensive operation. I have seen many legacy applications where a complete resultset is brought back to the application and paging has been done. As what you have read earlier, SQL Server 2011 offers a better alternative to an age-old solution.

This article has been divided into two parts:

Test 1: Performance Comparison of the Two Different Pages on SQL Server 2011 Method

In this test, we will analyze the performance of the two different pages where one is at the beginning of the table and the other one is at its end.

Test 2: Performance Comparison of the Two Different Pages Using CTE (Earlier Solution from SQL Server 2005/2008) and the New Method of SQL Server 2011

We will explore this in the next article. This article will tackle test 1 first.

Test 1: Retrieving Page from two different locations of the table.

Run the following T-SQL Script and compare the performance.

SET STATISTICS IO ON;
USE AdventureWorks2008R2
GO
DECLARE @RowsPerPage INT = 10, @PageNumber INT = 5
SELECT *
FROM Sales.SalesOrderDetail
ORDER BY SalesOrderDetailID
OFFSET
@PageNumber*@RowsPerPage ROWS
FETCH NEXT 10 ROWS ONLY
GO
USE AdventureWorks2008R2
GO
DECLARE @RowsPerPage INT = 10, @PageNumber INT = 12100
SELECT *
FROM Sales.SalesOrderDetail
ORDER BY SalesOrderDetailID
OFFSET
@PageNumber*@RowsPerPage ROWS
FETCH NEXT 10 ROWS ONLY
GO

You will notice that when we are reading the page from the beginning of the table, the database pages read are much lower than when the page is read from the end of the table. This is very interesting as when the the OFFSET changes, PAGE IO is increased or decreased. In the normal case of the search engine, people usually read it from the first few pages, which means that IO will be increased as we go further in the higher parts of navigation.

I am really impressed because using the new method of SQL Server 2011,  PAGE IO will be much lower when the first few pages are searched in the navigation.

Test 2: Retrieving Page from two different locations of the table and comparing to earlier versions.

In this test, we will compare the queries of the Test 1 with the earlier solution via Common Table Expression (CTE) which we utilized in SQL Server 2005 and SQL Server 2008.

Test 2 A : Page early in the table
-- Test with pages early in table
USE AdventureWorks2008R2
GO
DECLARE @RowsPerPage INT = 10, @PageNumber INT = 5
;WITH CTE_SalesOrderDetail AS
(
SELECT *, ROW_NUMBER() OVER(
ORDER BY SalesOrderDetailID) AS RowNumber
FROM Sales.SalesOrderDetail PC)
SELECT *
FROM CTE_SalesOrderDetail
WHERE RowNumber >= @PageNumber*@RowsPerPage+1 AND RowNumber <= (@PageNumber+1)*@RowsPerPage
ORDER BY SalesOrderDetailID
GO
SET STATISTICS IO ON;
USE AdventureWorks2008R2
GO
DECLARE @RowsPerPage INT = 10, @PageNumber INT = 5
SELECT *
FROM Sales.SalesOrderDetail
ORDER BY SalesOrderDetailID
OFFSET
@PageNumber*@RowsPerPage ROWS
FETCH NEXT 10 ROWS ONLY
GO

Test 2 B : Page later in the table
-- Test with pages later in table
USE AdventureWorks2008R2
GO
DECLARE @RowsPerPage INT = 10, @PageNumber INT = 12100
;WITH CTE_SalesOrderDetail AS
(
SELECT *, ROW_NUMBER() OVER(
ORDER BY SalesOrderDetailID) AS RowNumber
FROM Sales.SalesOrderDetail PC)
SELECT *
FROM CTE_SalesOrderDetail
WHERE RowNumber >= @PageNumber*@RowsPerPage+1 AND RowNumber <= (@PageNumber+1)*@RowsPerPage
ORDER BY SalesOrderDetailID
GO
SET STATISTICS IO ON;
USE AdventureWorks2008R2
GO
DECLARE @RowsPerPage INT = 10, @PageNumber INT = 12100
SELECT *
FROM Sales.SalesOrderDetail
ORDER BY SalesOrderDetailID
OFFSET
@PageNumber*@RowsPerPage ROWS
FETCH NEXT 10 ROWS ONLY
GO

From the resultset, it is very clear that in the earlier case, the pages read in the solution are always much higher than the new technique introduced in SQL Server 2011 even if we don’t retrieve all the data to the screen.

If you carefully look at both the comparisons, the PAGE IO is much lesser in the case of the new technique introduced in SQL Server 2011 when we read the page from the beginning of the table and when we read it from the end.

I consider this as a big improvement as paging is one of the most used features for the most part of the application. The solution introduced in SQL Server 2011 is very elegant because it also improves the performance of the query and, at large, the database.

Reference : Pinal Dave (http://blog.SQLAuthority.com)

About these ads

16 thoughts on “SQL SERVER – Server Side Paging in SQL Server Denali Performance Comparison

  1. Pingback: SQL SERVER – Server Side Paging in SQL Server 2011 – Part2 Journey to SQL Authority with Pinal Dave

  2. one question:
    what is the best way of returning the total number of records before the pagination both in SQL 2008 and 2011?

    if I have a complex query, I do not want to duplicate the logic to get the recordset and the total count.
    If I use CTE I can do one or the other…

  3. The RowNumber filter is not correct. It msut look like this for the “old” method:

    WHERE RowNumber >= (@PageNumber*@RowsPerPage)-@RowsPerPage+1 AND RowNumber <= (@PageNumber+1)*@RowsPerPage

  4. Pingback: SQL SERVER – Server Side Paging in SQL Server CE (Compact Edition) « Journey to SQLAuthority

  5. Thanks for the reply, Does it stores the records, if so what will be life time? Does it clears when data changes are done or connection closed?

  6. Pingback: SQL SERVER – Weekly Series – Memory Lane – #008 « SQL Server Journey with SQL Authority

  7. Pingback: SQL SERVER – Weekly Series – Memory Lane – #050 | Journey to SQL Authority with Pinal Dave

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s