前些天看到一篇文章《SQL Server 2012 - Server side paging demo using OFFSET/FETCH NEXT》,原文地址。作者在文中称,要SQL Server 2012使用OFFSET/FETCH NEXT分页,比SQL Server 2005/2008中的RowNumber()有显著改进。今天特地作了简单测试。现将过程分享如下:
附:我的测试环境为:
SQL Server 2012,命名实例
Microsoft SQL Server 2012 - 11.0.2100.60 (Intel X86)
Feb 10 2012 19:13:17
Copyright (c) Microsoft Corporation
Enterprise Edition on Windows NT 6.0 <X86> (Build 6002: Service Pack 2)
沿用上文的测试数据库和表:
- 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) )
- GOTRUNCATE 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语句如下:
- /*
- 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中执行如下语句:
- 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分页方式都比Row_Number()方式有了较大的提升。
邀月注:本文版权由邀月和CSDN共同所有,转载请注明出处。
助人等于自助! 3w@live.cn
助人等于自助! 3w@live.cn