Paging using CTE with Key Seek method vs OFFSET FETCH

• PiMané

  SSChampion

  Points: 13965

  More actions

  October 17, 2012 at 5:29 am

  #393406

  Hi,

  I use CTE with Key Seek method for paging but since SQL 2012 has the OFFSET FETCH statement I decided to make some tests in the AdventureWorks2012 database:

  SET STATISTICS IO ON;

  SET STATISTICS TIME ON;

  PRINT '-------------------------------'

  DBCC DROPCLEANBUFFERS

  DBCC FREEPROCCACHE

  PRINT 'START -------------------------------'

  ;WITH data AS (

  SELECT TOP 20 p.BusinessEntityID, ROW_NUMBER() OVER (ORDER BY p.FirstName) row FROM Person.Person p WHERE LastName LIKE 'P%'

  ) SELECT p.BusinessEntityID, p.FirstName, p.LastName, a.City FROM data d INNER JOIN Person.Person p ON d.BusinessEntityID = p.BusinessEntityID

  LEFT JOIN Person.BusinessEntityAddress bea ON bea.BusinessEntityID = p.BusinessEntityID

  LEFT JOIN Person.Address a ON bea.AddressID = a.AddressID

  WHERE row BETWEEN 10 AND 20

  PRINT 'END -------------------------------'

  DBCC DROPCLEANBUFFERS

  DBCC FREEPROCCACHE

  PRINT 'START -------------------------------'

  ;WITH data AS (

  SELECT p.BusinessEntityID FROM Person.Person p

  WHERE LastName LIKE 'P%'

  ORDER BY p.LastName

  OFFSET 10 ROWS

  FETCH NEXT 10 ROWS ONLY)

  SELECT p.BusinessEntityID, p.FirstName, p.LastName, a.City FROM data d INNER JOIN Person.Person p ON d.BusinessEntityID = p.BusinessEntityID

  LEFT JOIN Person.BusinessEntityAddress bea ON bea.BusinessEntityID = p.BusinessEntityID

  LEFT JOIN Person.Address a ON bea.AddressID = a.AddressID

  PRINT 'END -------------------------------'

  DBCC DROPCLEANBUFFERS

  DBCC FREEPROCCACHE

  PRINT 'START -------------------------------'

  SELECT p.BusinessEntityID, p.FirstName, p.LastName, a.City FROM Person.Person p

  LEFT JOIN Person.BusinessEntityAddress bea ON bea.BusinessEntityID = p.BusinessEntityID

  LEFT JOIN Person.Address a ON bea.AddressID = a.AddressID

  WHERE p.LastName LIKE 'P%'

  ORDER BY p.FirstName

  OFFSET 10 ROWS

  FETCH NEXT 10 ROWS ONLY

  PRINT 'END -------------------------------'

  It seems the new OFFSET FETCH is faster but also it has more reads:

  START -------------------------------

  SQL Server Execution Times:

  CPU time = 0 ms, elapsed time = 0 ms.

  (11 row(s) affected)

  Table 'Address'. Scan count 0, logical reads 22, physical reads 8, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

  Table 'BusinessEntityAddress'. Scan count 11, logical reads 22, physical reads 6, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

  Table 'Person'. Scan count 1, logical reads 43, physical reads 14, read-ahead reads 8, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

  (1 row(s) affected)

  SQL Server Execution Times:

  CPU time = 0 ms, elapsed time = 275 ms.

  END -------------------------------

  SQL Server Execution Times:

  CPU time = 0 ms, elapsed time = 0 ms.

  DBCC execution completed. If DBCC printed error messages, contact your system administrator.

  SQL Server Execution Times:

  CPU time = 0 ms, elapsed time = 2 ms.

  DBCC execution completed. If DBCC printed error messages, contact your system administrator.

  SQL Server Execution Times:

  CPU time = 0 ms, elapsed time = 0 ms.

  START -------------------------------

  SQL Server Execution Times:

  CPU time = 0 ms, elapsed time = 0 ms.

  (10 row(s) affected)

  Table 'Address'. Scan count 0, logical reads 20, physical reads 10, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

  Table 'BusinessEntityAddress'. Scan count 10, logical reads 20, physical reads 7, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

  Table 'Person'. Scan count 1, logical reads 32, physical reads 16, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

  (1 row(s) affected)

  SQL Server Execution Times:

  CPU time = 0 ms, elapsed time = 315 ms.

  END -------------------------------

  SQL Server Execution Times:

  CPU time = 0 ms, elapsed time = 0 ms.

  DBCC execution completed. If DBCC printed error messages, contact your system administrator.

  SQL Server Execution Times:

  CPU time = 0 ms, elapsed time = 2 ms.

  DBCC execution completed. If DBCC printed error messages, contact your system administrator.

  SQL Server Execution Times:

  CPU time = 0 ms, elapsed time = 0 ms.

  START -------------------------------

  SQL Server Execution Times:

  CPU time = 0 ms, elapsed time = 0 ms.

  (10 row(s) affected)

  Table 'Address'. Scan count 0, logical reads 531, physical reads 1, read-ahead reads 280, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

  Table 'BusinessEntityAddress'. Scan count 219, logical reads 1103, physical reads 1, read-ahead reads 112, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

  Table 'Person'. Scan count 1, logical reads 10, physical reads 1, read-ahead reads 8, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

  (1 row(s) affected)

  SQL Server Execution Times:

  CPU time = 0 ms, elapsed time = 198 ms.

  END -------------------------------

  It has much more reads... is this normal? It takes less time but much more reads.

  Which one is better?!?

  Thanks,

  Pedro

  ---

  
  If you need to work better, try working less...