SQL Server 2012使用OFFSET/FETCH NEXT分页及性能测试
2023-09-11 14:19:23 时间
最近在网上看到不少文章介绍使用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递归插入,邀月的版权,^_^
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)
2005或者2008下,使用ROW_NUMBER()分页,我把Count(*)的部分修改了:
/* 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
CPU及IO:
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.
2012下,使用OFFSET/FETCH NEXT分页,同样,我把Count(*)的部分修改了以及ORDER BY CustomerName:
/* 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
CPU及IO:
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.
可以看到IO有很大的变化,而CPU略有提升。当然也可以在CustomerCity上加上如下索引:
CREATE NONCLUSTERED INDEX IX_Customers_CustomerCity ON [dbo].[Customers] ([CustomerCity]) INCLUDE ([CustomerNumber],[CustomerName])
加完索引后依然是OFFSET/FETCH NEXT的性能要更好。新特性使得分页的性能提升不少,.net程序员们的福音啊
相关文章
- SQL SERVER 2012启动失败 because upgrade step 'SSIS_hotfix_install.sql' 失败
- SQL Server基础Sql语句复习
- SQL SERVER全面优化-------Expert for SQL Server 诊断系列
- 【HTML5】Server-Sent服务器发送事件
- Sql Server 存储过程基础
- Oracle与Sql Server复制表结构及数据
- WebShop WebSocket server 和WebSocket客户端的一对多关系维护
- 使用Java程序通过http post访问Application server
- Sql Server生成测试数据
- SQL SERVER服务器链接连接(即sql server的跨库连接)
- 在与 SQL Server 建立连接时出现与网络相关的或特定于实例的错误
- SQL SERVER服务器链接连接(即sql server的跨库连接)
- Atitit 前端测试最简化内嵌web服务器 php 与node.js 目录 1.1. php内置Web Server1 1.2. Node的2 Node的比较麻烦些。。Php更加简单
- 30秒,2种方法解决SQL Server的内存管理问题
- Could not load the Tomcat server configuration at /Servers/Tomcat v7.0 Server at localhost-config.
- Server SAN:弄潮儿云计算时代
- SQL Server CONVERT() 函数
- 安装CDH6.3版本的时候遇到 ‘ERROR main:com.cloudera.server.cmf.Main: Server failed’的解决方案
- Kubernetes 核心组件:API Server 概念/功能