MSSQL 分页性能对比
2011-08-09 16:32 Dreamer57 阅读(418) 评论(0) 收藏 举报10W条数据，每页20条，翻到4000页时的结果
提供给伸手党
--SET STATISTICS IO ON
--SET STATISTICS PROFILE ON
--SET STATISTICS TIME ON
DECLARE
@PageSize INT = 20
,@CurrentPage INT = 4000
-- TOP NOT IN 【排名第三】
-- 缺点 NOT IN 但也没办法 此例是以 时间排序
PRINT('TOP NOT IN 缺点 NOT IN 但也没办法 此例是以 时间排序')
CHECKPOINT
DBCC DROPCLEANBUFFERS
DBCC FREEPROCCACHE
DBCC FREESYSTEMCACHE('ALL')
SELECT TOP(@PageSize)
[ID]
,[Name]
,[CreateDate]
FROM [dbo].[dr_Test]
WHERE [ID] NOT IN (SELECT TOP(@PageSize * (@CurrentPage - 1)) [ID] FROM [dbo].[dr_Test] ORDER BY [ID] DESC)
ORDER BY [ID] DESC
-- 效果
-- SQL Server 分析和编译时间:
-- CPU 时间 = 0 毫秒，占用时间 = 11 毫秒。
-- SQL Server 执行时间:
-- CPU 时间 = 0 毫秒，占用时间 = 0 毫秒。
--TOP NOT IN 缺点 NOT IN 但也没办法 此例是以 时间排序
-- SQL Server 执行时间:
-- CPU 时间 = 0 毫秒，占用时间 = 0 毫秒。
-- SQL Server 执行时间:
-- CPU 时间 = 0 毫秒，占用时间 = 28 毫秒。
--DBCC 执行完毕。如果 DBCC 输出了错误信息，请与系统管理员联系。
-- SQL Server 执行时间:
-- CPU 时间 = 0 毫秒，占用时间 = 0 毫秒。
--DBCC 执行完毕。如果 DBCC 输出了错误信息，请与系统管理员联系。
-- SQL Server 执行时间:
-- CPU 时间 = 0 毫秒，占用时间 = 0 毫秒。
--DBCC 执行完毕。如果 DBCC 输出了错误信息，请与系统管理员联系。
-- SQL Server 执行时间:
-- CPU 时间 = 0 毫秒，占用时间 = 1 毫秒。
--(20 行受影响)
--表 'dr_Test'。扫描计数 2，逻辑读取 901 次，物理读取 3 次，预读 568 次，lob 逻辑读取 0 次，lob 物理读取 0 次，lob 预读 0 次。
-- 表变量
-- 缺点 INSERT 消耗大
DECLARE @TempT1 TABLE
(
SID INT IDENTITY(1,1)
,ID INT NOT NULL
,Name VARCHAR(20) NOT NULL
,CreateDate VARCHAR(19) NOT NULL
)
CHECKPOINT
DBCC DROPCLEANBUFFERS
DBCC FREEPROCCACHE
DBCC FREESYSTEMCACHE('ALL')
INSERT INTO @TempT1
SELECT TOP(@PageSize * (@CurrentPage - 1)) [ID],[Name]
,[CreateDate] FROM [dbo].[dr_Test] ORDER BY [ID] DESC
CHECKPOINT
DBCC DROPCLEANBUFFERS
DBCC FREEPROCCACHE
DBCC FREESYSTEMCACHE('ALL')
SELECT TOP(@PageSize)
[ID]
,[Name]
,[CreateDate]
FROM [dbo].[dr_Test]
WHERE ID NOT IN(SELECT [ID] FROM @TempT1)
-- 效果
-- SQL Server 执行时间:
-- CPU 时间 = 63 毫秒，占用时间 = 250 毫秒。
-- SQL Server 执行时间:
-- CPU 时间 = 0 毫秒，占用时间 = 12 毫秒。
--DBCC 执行完毕。如果 DBCC 输出了错误信息，请与系统管理员联系。
-- SQL Server 执行时间:
-- CPU 时间 = 0 毫秒，占用时间 = 0 毫秒。
--DBCC 执行完毕。如果 DBCC 输出了错误信息，请与系统管理员联系。
-- SQL Server 执行时间:
-- CPU 时间 = 0 毫秒，占用时间 = 0 毫秒。
--DBCC 执行完毕。如果 DBCC 输出了错误信息，请与系统管理员联系。
-- SQL Server 执行时间:
-- CPU 时间 = 0 毫秒，占用时间 = 0 毫秒。
--表 '#31B762FC'。扫描计数 0，逻辑读取 80486 次，物理读取 0 次，预读 0 次，lob 逻辑读取 0 次，lob 物理读取 0 次，lob 预读 0 次。
--表 'dr_Test'。扫描计数 1，逻辑读取 451 次，物理读取 3 次，预读 568 次，lob 逻辑读取 0 次，lob 物理读取 0 次，lob 预读 0 次。
--(79980 行受影响)
--(7 行受影响)
-- SQL Server 执行时间:
-- CPU 时间 = 390 毫秒，占用时间 = 679 毫秒。
-- SQL Server 执行时间:
-- CPU 时间 = 0 毫秒，占用时间 = 10 毫秒。
--DBCC 执行完毕。如果 DBCC 输出了错误信息，请与系统管理员联系。
-- SQL Server 执行时间:
-- CPU 时间 = 0 毫秒，占用时间 = 0 毫秒。
--DBCC 执行完毕。如果 DBCC 输出了错误信息，请与系统管理员联系。
-- SQL Server 执行时间:
-- CPU 时间 = 0 毫秒，占用时间 = 0 毫秒。
--DBCC 执行完毕。如果 DBCC 输出了错误信息，请与系统管理员联系。
-- SQL Server 执行时间:
-- CPU 时间 = 16 毫秒，占用时间 = 0 毫秒。
--(20 行受影响)
--表 '#31B762FC'。扫描计数 1，逻辑读取 10140 次，物理读取 0 次，预读 12 次，lob 逻辑读取 0 次，lob 物理读取 0 次，lob 预读 0 次。
--表 'dr_Test'。扫描计数 1，逻辑读取 2 次，物理读取 2 次，预读 0 次，lob 逻辑读取 0 次，lob 物理读取 0 次，lob 预读 0 次。
-- 相当恐怖
-- ROW_NUMBER 【排名第一】
-- 优点 生成自增长编号方便排序，与利用表变量，提高效率
DECLARE @TempA INT = (@PageSize * (@CurrentPage - 1)) + 1
CHECKPOINT
DBCC DROPCLEANBUFFERS
DBCC FREEPROCCACHE
DBCC FREESYSTEMCACHE('ALL')
SELECT
[ID]
,[Name]
,[CreateDate]
FROM (SELECT ROW_NUMBER() OVER(ORDER BY ID DESC) AS RowNum,
[ID]
,[Name]
,[CreateDate]
FROM [dbo].[dr_Test]
) AS T1
WHERE RowNum BETWEEN @TempA AND @TempA - 1 + @PageSize
-- 效果
-- SQL Server 执行时间:
-- CPU 时间 = 281 毫秒，占用时间 = 306 毫秒。
-- SQL Server 执行时间:
-- CPU 时间 = 0 毫秒，占用时间 = 0 毫秒。
-- SQL Server 执行时间:
-- CPU 时间 = 0 毫秒，占用时间 = 33 毫秒。
--DBCC 执行完毕。如果 DBCC 输出了错误信息，请与系统管理员联系。
-- SQL Server 执行时间:
-- CPU 时间 = 0 毫秒，占用时间 = 0 毫秒。
--DBCC 执行完毕。如果 DBCC 输出了错误信息，请与系统管理员联系。
-- SQL Server 执行时间:
-- CPU 时间 = 0 毫秒，占用时间 = 0 毫秒。
--DBCC 执行完毕。如果 DBCC 输出了错误信息，请与系统管理员联系。
-- SQL Server 执行时间:
-- CPU 时间 = 0 毫秒，占用时间 = 0 毫秒。
--(20 行受影响)
--表 'dr_Test'。扫描计数 1，逻辑读取 451 次，物理读取 3 次，预读 568 次，lob 逻辑读取 0 次，lob 物理读取 0 次，lob 预读 0 次。
-- 不要犹豫了，就用它了！
-------------------------- 下面是以 自增长 ID 排序 ------------------------------
-- TOP ID 范围 【排名第二】
--
CHECKPOINT
DBCC DROPCLEANBUFFERS
DBCC FREEPROCCACHE
DBCC FREESYSTEMCACHE('ALL')
SELECT TOP(@PageSize)
[ID]
,[Name]
,[CreateDate]
FROM [dbo].[dr_Test]
WHERE [ID] < (SELECT ISNULL(MIN([ID]),(SELECT MAX([ID]) FROM [dbo].[dr_Test])) FROM (SELECT TOP(@PageSize * (@CurrentPage - 1)) [ID] FROM [dbo].[dr_Test] ORDER BY [ID] DESC) T1)
ORDER BY [ID] DESC
-- 效果
-- SQL Server 执行时间:
-- CPU 时间 = 63 毫秒，占用时间 = 176 毫秒。
-- SQL Server 执行时间:
-- CPU 时间 = 15 毫秒，占用时间 = 12 毫秒。
--DBCC 执行完毕。如果 DBCC 输出了错误信息，请与系统管理员联系。
-- SQL Server 执行时间:
-- CPU 时间 = 0 毫秒，占用时间 = 0 毫秒。
--DBCC 执行完毕。如果 DBCC 输出了错误信息，请与系统管理员联系。
-- SQL Server 执行时间:
-- CPU 时间 = 0 毫秒，占用时间 = 0 毫秒。
--DBCC 执行完毕。如果 DBCC 输出了错误信息，请与系统管理员联系。
-- SQL Server 执行时间:
-- CPU 时间 = 0 毫秒，占用时间 = 0 毫秒。
--(20 行受影响)
--表 'dr_Test'。扫描计数 3，逻辑读取 455 次，物理读取 3 次，预读 568 次，lob 逻辑读取 0 次，lob 物理读取 0 次，lob 预读 0 次。
-- 比 NOT IN 好（废话）
-- 表变量
-- 缺点 INSERT 消耗大
DECLARE @TempT2 TABLE
(
SID INT IDENTITY(1,1)
,ID INT NOT NULL
,Name VARCHAR(20) NOT NULL
,CreateDate VARCHAR(19) NOT NULL
)
CHECKPOINT
DBCC DROPCLEANBUFFERS
DBCC FREEPROCCACHE
DBCC FREESYSTEMCACHE('ALL')
INSERT INTO @TempT2
SELECT TOP(@PageSize * (@CurrentPage - 1)) [ID],[Name]
,[CreateDate] FROM [dbo].[dr_Test] ORDER BY [ID] DESC
CHECKPOINT
DBCC DROPCLEANBUFFERS
DBCC FREEPROCCACHE
DBCC FREESYSTEMCACHE('ALL')
SELECT TOP(@PageSize)
[ID]
,[Name]
,[CreateDate]
FROM [dbo].[dr_Test]
WHERE [ID] < (SELECT ISNULL(MIN([ID]),(SELECT MAX([ID]) FROM [dbo].[dr_Test])) FROM @TempT2)
ORDER BY [ID] DESC
-- 效果
-- SQL Server 执行时间:
-- CPU 时间 = 16 毫秒，占用时间 = 214 毫秒。
-- SQL Server 执行时间:
-- CPU 时间 = 0 毫秒，占用时间 = 16 毫秒。
--DBCC 执行完毕。如果 DBCC 输出了错误信息，请与系统管理员联系。
-- SQL Server 执行时间:
-- CPU 时间 = 16 毫秒，占用时间 = 0 毫秒。
--DBCC 执行完毕。如果 DBCC 输出了错误信息，请与系统管理员联系。
-- SQL Server 执行时间:
-- CPU 时间 = 0 毫秒，占用时间 = 0 毫秒。
--DBCC 执行完毕。如果 DBCC 输出了错误信息，请与系统管理员联系。
-- SQL Server 执行时间:
-- CPU 时间 = 0 毫秒，占用时间 = 0 毫秒。
--表 '#32AB8735'。扫描计数 0，逻辑读取 80486 次，物理读取 0 次，预读 0 次，lob 逻辑读取 0 次，lob 物理读取 0 次，lob 预读 0 次。
--表 'dr_Test'。扫描计数 1，逻辑读取 451 次，物理读取 3 次，预读 568 次，lob 逻辑读取 0 次，lob 物理读取 0 次，lob 预读 0 次。
--(79980 行受影响)
--(7 行受影响)
-- SQL Server 执行时间:
-- CPU 时间 = 421 毫秒，占用时间 = 996 毫秒。
-- SQL Server 执行时间:
-- CPU 时间 = 0 毫秒，占用时间 = 77 毫秒。
--DBCC 执行完毕。如果 DBCC 输出了错误信息，请与系统管理员联系。
-- SQL Server 执行时间:
-- CPU 时间 = 0 毫秒，占用时间 = 1 毫秒。
--DBCC 执行完毕。如果 DBCC 输出了错误信息，请与系统管理员联系。
-- SQL Server 执行时间:
-- CPU 时间 = 0 毫秒，占用时间 = 0 毫秒。
--DBCC 执行完毕。如果 DBCC 输出了错误信息，请与系统管理员联系。
-- SQL Server 执行时间:
-- CPU 时间 = 0 毫秒，占用时间 = 0 毫秒。
--(20 行受影响)
--表 'dr_Test'。扫描计数 2，逻辑读取 4 次，物理读取 3 次，预读 0 次，lob 逻辑读取 0 次，lob 物理读取 0 次，lob 预读 0 次。
--表 '#32AB8735'。扫描计数 1，逻辑读取 507 次，物理读取 0 次，预读 9 次，lob 逻辑读取 0 次，lob 物理读取 0 次，lob 预读 0 次。
--(10 行受影响)
-- SQL Server 执行时间:
-- CPU 时间 = 32 毫秒，占用时间 = 211 毫秒。
以上个人测试不代表正确结果
浙公网安备 33010602011771号