技术开发 频道

SQL Server 2012分页技术OFFSET/FETCH

  【IT168 技术】在文中称,要SQL Server 2012使用OFFSET/FETCH NEXT分页,比SQL Server 2005/2008中的RowNumber()有显著改进。今天特地作了简单测试。现将过程分享如下:

  沿用上文的测试数据库和表:

  IF OBJECT_ID('DemoPager2012') IS NOT NULL

  DROP DataBase DemoPager2012

  GO

  CREATE Database DemoPager2012

  GO

  USE DemoPager2012

  GO

  
/*

  Setup script
to create the sample table and fill it with

  sample data.

  
*/

  
IF OBJECT_ID('Customers','U') IS NOT NULL

  DROP TABLE Customers

  CREATE TABLE Customers ( CustomerID
INT primary key identity(1,1),

  CustomerNumber CHAR(
4),

  CustomerName VARCHAR(
50),

  CustomerCity VARCHAR(
20) )

  GO

  TRUNCATE table Customers

  GO

  DBCC DROPCLEANBUFFERS

  DBCC FREEPROCCACHE

  
/*****运用CTE递归插入,速度较快,注***********************/

  
WITH Seq (num,CustomerNumber, CustomerName, CustomerCity) AS

  (
SELECT 1,cast('0000'as CHAR(4)),cast('Customer 0' AS NVARCHAR(50)),cast('X-City' as NVARCHAR(20))

  UNION ALL

  
SELECT num + 1,Cast(REPLACE(STR(num, 4), ' ', '0') AS CHAR(4)),

  cast(
'Customer ' + STR(num,6) AS NVARCHAR(50)),

  cast(CHAR(
65 + (num % 26)) + '-City' AS NVARCHAR(20))

  FROM Seq

  WHERE num
<= 10000

  )

  INSERT INTO Customers (CustomerNumber, CustomerName, CustomerCity)

  
SELECT CustomerNumber, CustomerName, CustomerCity

  FROM Seq

  
OPTION (MAXRECURSION 0)

 

  插入1万条数据后,在SQL Server 2008 R2中执行Row_Number():

  /*

  Server side paging demo using ROW_NUMBER()
- SQL Server

  
2005/2008 version.

  
*/

  DBCC DROPCLEANBUFFERS

  DBCC FREEPROCCACHE

  
SET STATISTICS IO ON;

  
SET STATISTICS TIME ON;

  GO

  DECLARE @page
INT, @size INT

  
SELECT @page = 3, @size = 10

  ;
WITH cte AS (

  
SELECT TOP (@page * @size)

  CustomerID,

  CustomerName,

  CustomerCity,

  ROW_NUMBER() OVER(ORDER BY CustomerName )
AS Seq,

  COUNT(
*) OVER(PARTITION BY '') AS Total

  FROM Customers

  WHERE CustomerCity IN (
'A-City','B-City')

  ORDER BY CustomerName
ASC

  )

  
SELECT * FROM cte

  WHERE seq BETWEEN (@page
- 1 ) * @size + 1 AND @page * @size

  ORDER BY seq;

  GO

  
SET STATISTICS IO OFF ;

  
SET STATISTICS TIME OFF;

  GO

SQL Server 2012分页OFFSET/FETCH NEXT

SQL Server 2012分页OFFSET/FETCH NEXT

${PageNumber}

  SQL Server 2012中执行OFFSET/FETCH NEXT语句如下:

  /*

  Server side paging demo using the
new enhancements added

  in SQL Server
2012

  
*/

  DBCC DROPCLEANBUFFERS

  DBCC FREEPROCCACHE

  
SET STATISTICS IO ON;

  
SET STATISTICS TIME ON;

  GO

  DECLARE @page
INT, @size INT

  
SELECT @page = 3, @size = 10

  
SELECT

  
*,

  COUNT(
*) OVER(PARTITION BY '') AS Total

  FROM Customers

  WHERE CustomerCity IN (
'A-City','B-City')

  ORDER BY CustomerID

  OFFSET (@page
-1) * @size ROWS

  FETCH
NEXT @size ROWS ONLY;

  GO

  
SET STATISTICS IO OFF;

  
SET STATISTICS TIME OFF;

  GO

SQL Server 2012分页OFFSET/FETCH NEXT

SQL Server 2012分页OFFSET/FETCH NEXT

  在SQL Server 2012中执行如下语句:

  DBCC DROPCLEANBUFFERS

  DBCC FREEPROCCACHE

  
SET STATISTICS IO ON;

  
SET STATISTICS TIME ON;

  GO

  DECLARE @page
INT, @size INT

  
SELECT @page = 3, @size = 10

  ;
WITH cte AS (

  
SELECT TOP (@page * @size)

  CustomerID,

  CustomerName,

  CustomerCity,

  ROW_NUMBER() OVER(ORDER BY CustomerName )
AS Seq,

  COUNT(
*) OVER(PARTITION BY '') AS Total

  FROM Customers

  WHERE CustomerCity IN (
'A-City','B-City')

  ORDER BY CustomerName
ASC

  )

  
SELECT * FROM cte

  WHERE seq BETWEEN (@page
- 1 ) * @size + 1 AND @page * @size

  ORDER BY seq;

  
SELECT

  
*,

  COUNT(
*) OVER(PARTITION BY '') AS Total

  FROM Customers

  WHERE CustomerCity IN (
'A-City','B-City')

  ORDER BY CustomerID

  OFFSET (@page
-1) * @size ROWS

  FETCH
NEXT @size ROWS ONLY;

  GO

  
SET STATISTICS IO OFF;

  
SET STATISTICS TIME OFF;

  GO

 

  结果:

SQL Server 2012分页OFFSET/FETCH NEXT

  结论:无论是从逻辑读取数还是响应时间、实际执行行数等关键参数看,SQL Server 2012提供的OFFSET/FETCH NEXT分页方式都比Row_Number()方式有了较大的提升。

0
相关文章