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)
Filed under: SQL, SQL Authority, SQL Optimization, SQL Performance, SQL Query, SQL Server, SQL Tips and Tricks, T SQL, Technology
