Earlier, I have written about SQL SERVER – Server Side Paging in SQL Server 2012 – 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 2012 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 2012, 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 2012 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 2012 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 2012 is very elegant because it also improves the performance of the query and, at large, the database.
Reference : Pinal Dave (https://blog.sqlauthority.com)
16 Comments. Leave new
Pinal,
Thanks for the detailed artile and the script. This would be of big help while developing applications that need paging.
would like to see the execution plans in comparison (old vs. new).
Pinal Can i do server side Paging in SSMS 2008 ?
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…
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
Even this is not correct. This one works out for me:
WHERE RowNumber >= (@PageNumber*@RowsPerPage)-@RowsPerPage+1 AND RowNumber <= (@PageNumber)*@RowsPerPage
still doesn’t allow dynamic order by, so it’s still messy if we allow users to decide which column to sort by.
Why does sql query takes less time when executed second time? What will be the scope?
It is cached in the memory and SQL Server will reuse the cached plan
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?
In Test 1, Last page takes 1359 IO reads and in Test 2 B, same query reads 1020 IO pages . How this is possible?
Is there a version SQL Server 2011?
That was my question too.
That was typo so I fixed it by replacing it by 2012.
Did you try to clean cache in each batch. Actually, when i clean plan cache in every case. the result will be same in all of queries such as: subquery, cte, offset.. fetch
I mean the IO statistic.