New Paging Functionality in SQL Server Denali

Posted on September 27, 2011

0



 

Introducing OFFSET

 

There is new functionality in SQL Server Denali to streamline and simplify returning “Pages” of data to an application.

In SQL Server 2008 and 2005, your code would likely look like this:

use AdventureWorks
GO
 
–Define the common table expression that filters the results
–and applies a row number
WITH CTE AS
(
SELECT
*,
ROW_NUMBER() OVER (ORDER BY OrderDate) AS RowNumber
FROM
Sales.SalesOrderHeader
WHERE
OrderDate BETWEEN ’2004-02-01′ AND ’2004-02-28′
)
 
–Select the page of data
SELECT
*
FROM
CTE
WHERE
RowNumber BETWEEN 11 AND 20
GO

 

SQL Server “Denali” introduces the OFFSET keyword.  The code above is now translated into the following:

use AdventureWorks
GO
 
SELECT
    *
FROM
    Sales.SalesOrderHeader
WHERE
    OrderDate BETWEEN ’2004-02-01′ AND ’2004-02-28′
ORDER BY
    OrderDate OFFSET 10 ROWS FETCH NEXT 10 ROWS ONLY
GO

 

The OFFSET command is actually an argument to the ORDER BY clause.  To use it, you simply specify the OFFSET argument, followed by the first row you want returned, then the keyword ROW (or ROWS – there is no difference), then the key words FETCH NEXT followed by the number of rows to return and finally the keyword ROW (or ROWS) and the key word ONLY.

You can also return all of the records starting from a given OFFSET by omitting the “FETCH NEXT N ROWS ONLY” argument as shown below:

use AdventureWorks
GO
 
SELECT
    *
FROM
    Sales.SalesOrderHeader
ORDER BY
    OrderDate OFFSET 10 ROWS
GO

 

You can also use scalar sub-queries to specify the OFFSET or FETCH NEXT arguments.  For example, the following query will return the last 1000 rows in the table sorted by OrderDate.

use AdventureWorks
GO
 
SELECT
    *
FROM
    Sales.SalesOrderHeader
ORDER BY
    OrderDate OFFSET
    (SELECT (COUNT(SalesOrderId) - 1000) FROM Sales.SalesOrderHeader) ROWS
GO

 

 

 

Performance of OFFSET vs. CTE

 

The query plans for each of these are very similar:

image

image

The difference in the cost of each plan is also miniscule

Original Method 0.634015
New Method 0.633909

 

Memory

 

The interesting difference between the two plans is in the memory grant.  In the above scenario, the common table expression version requested a memory grant of 16k vs. 1k for the OFFSET version.  I realize that these are small numbers, but we are dealing with a very small dataset.  What would happen if we dropped the WHERE clause and then grew the table several times?  The result is that the common table expression version increased its memory grant request from 16k to 212 MB while the OFFSET version still only requests 1k.

What does this mean in terms of actual performance?  The first and most obvious consideration is consuming more memory than what’s available and spilling your temporary results to disk.  This can obviously impact performance as disk I/O is perhaps the most expensive operation available.  The second consideration is waiting for the memory grant itself.  On heavily loaded systems you may end up with blocked processes waiting for memory to be granted.  I will cover this in more detail in another post, but for now, if you are so inclined, you can check out the DMVs sys.dm_exec_query_resource_semaphores, sys.dm_exec_query_memory_grants and sys.dm_os_wait_stats for more information on the specifics.

 

Summary

 

While the old standby methods of returning chunks of data in previous versions of SQL Server still work, the OFFSET argument of the ORDER BY clause offers much more optimized and streamlined way of achieving the same results.

About these ads
Tagged: , ,
Posted in: Data Paging, Denali, SQL Server Programming, T-SQL