SQL Server 2012使用OFFSET/FETCH NEXT分页及性能测试

时间:2022-06-15 02:00:08

最近在网上看到不少文章介绍使用SQL Server 2012的新特性:OFFSET/FETCH NEXT 实现分页。多数文章都是引用或者翻译的这一篇《SQL Server 2012 - Server side paging demo using OFFSET/FETCH NEXT》,原文地址

邀月对此也做了性能测,《SQL Server 2012服务端使用OFFSET/FETCH NEXT实现分页》,不过老外或者邀月的代码都并没有真正显示出OFFSET/FETCH NEXT的性能比起原有的ROW_NUMBER()方式好多少。

我试了下,发现主要是在取COUNT(*)上,如果OFFSET/FETCH NEXT也同时取COUNT(*),那么执行计划里一样是聚集索引Scan或者表扫描。如果不同时取COUNT(*),那么性能提升相当可观。

初始化脚本如下,生成200w条记录,用CTE递归插入,邀月的版权,^_^

SQL Server 2012使用OFFSET/FETCH NEXT分页及性能测试
USE DBAdmin
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 USE [DBAdmin]
GO CREATE TABLE [dbo].[Customers](
[CustomerID] [int] IDENTITY(1,1) NOT NULL,
[CustomerNumber] [char](8) NULL,
[CustomerName] [varchar](50) NULL,
[CustomerCity] [varchar](20) NULL,
CONSTRAINT [PK_Customers] PRIMARY KEY CLUSTERED
( [CustomerID] ASC ) ) ON [PRIMARY] GO TRUNCATE table Customers
GO DBCC DROPCLEANBUFFERS
DBCC FREEPROCCACHE
GO
/*****运用CTE递归插入,速度较快,邀月注***********************/
WITH Seq (num,CustomerNumber, CustomerName, CustomerCity) AS
(SELECT 1,cast('00000000'as CHAR(8)),cast('Customer 0' AS NVARCHAR(50)),cast('X-City' as NVARCHAR(20))
UNION ALL
SELECT num + 1,Cast(REPLACE(STR(num, 8), ' ', '0') AS CHAR(8)),
cast('Customer ' + STR(num,8) AS NVARCHAR(50)),
cast(CHAR(65 + (num % 26)) + '-City' AS NVARCHAR(20))
FROM Seq
WHERE num <= 2000000
)
INSERT INTO Customers (CustomerNumber, CustomerName, CustomerCity)
SELECT CustomerNumber, CustomerName, CustomerCity
FROM Seq
OPTION (MAXRECURSION 0)
SQL Server 2012使用OFFSET/FETCH NEXT分页及性能测试

2005或者2008下,使用ROW_NUMBER()分页,我把Count(*)的部分修改了:

SQL Server 2012使用OFFSET/FETCH NEXT分页及性能测试
/*
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 ,@Total int
SELECT @page = 700, @size = 10 select @Total = COUNT(*)
FROM Customers
WHERE CustomerCity IN ('A-City','B-City') ;WITH cte AS (
SELECT TOP (@page * @size)
CustomerID,
CustomerNumber,
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 CustomerID,CustomerNumber,CustomerName,CustomerCity,@Total
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分页及性能测试

CPU及IO:

SQL Server 2012使用OFFSET/FETCH NEXT分页及性能测试
DBCC execution completed. If DBCC printed error messages, contact your system administrator.
DBCC execution completed. If DBCC printed error messages, contact your system administrator.
SQL Server parse and compile time:
CPU time = 0 ms, elapsed time = 47 ms. SQL Server Execution Times:
CPU time = 0 ms, elapsed time = 0 ms.
Table 'Customers'. Scan count 5, logical reads 12600, physical reads 1, read-ahead reads 12468, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0. SQL Server Execution Times:
CPU time = 483 ms, elapsed time = 1615 ms. (10 row(s) affected)
Table 'Customers'. Scan count 5, logical reads 12648, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0. SQL Server Execution Times:
CPU time = 671 ms, elapsed time = 183 ms.
SQL Server parse and compile time:
CPU time = 0 ms, elapsed time = 0 ms. SQL Server Execution Times:
CPU time = 0 ms, elapsed time = 0 ms.
SQL Server 2012使用OFFSET/FETCH NEXT分页及性能测试

2012下,使用OFFSET/FETCH NEXT分页,同样,我把Count(*)的部分修改了以及ORDER BY CustomerName:

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,@Total int
SELECT @page = 700, @size = 10 select @Total = COUNT(*)
FROM Customers
WHERE CustomerCity IN ('A-City','B-City') SELECT
*,@Total--,COUNT(*) OVER(PARTITION BY '') AS Total
FROM Customers
WHERE CustomerCity IN ('A-City','B-City')
ORDER BY CustomerName
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分页及性能测试

CPU及IO:

SQL Server 2012使用OFFSET/FETCH NEXT分页及性能测试
DBCC execution completed. If DBCC printed error messages, contact your system administrator.
DBCC execution completed. If DBCC printed error messages, contact your system administrator.
SQL Server parse and compile time:
CPU time = 0 ms, elapsed time = 26 ms. SQL Server Execution Times:
CPU time = 0 ms, elapsed time = 0 ms.
Table 'Customers'. Scan count 5, logical reads 12600, physical reads 1, read-ahead reads 12468, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0. SQL Server Execution Times:
CPU time = 266 ms, elapsed time = 1688 ms. (10 row(s) affected)
Table 'Customers'. Scan count 1, logical reads 2, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0. SQL Server Execution Times:
CPU time = 639 ms, elapsed time = 175 ms.
SQL Server parse and compile time:
CPU time = 0 ms, elapsed time = 0 ms. SQL Server Execution Times:
CPU time = 0 ms, elapsed time = 0 ms.
SQL Server 2012使用OFFSET/FETCH NEXT分页及性能测试

可以看到IO有很大的变化,而CPU略有提升。当然也可以在CustomerCity上加上如下索引:

CREATE NONCLUSTERED INDEX IX_Customers_CustomerCity
ON [dbo].[Customers] ([CustomerCity])
INCLUDE ([CustomerNumber],[CustomerName])

加完索引后依然是OFFSET/FETCH NEXT的性能要更好。新特性使得分页的性能提升不少,.net程序员们的福音啊