SQLSERVER 2005分页脚本性能实测
网上有很多的分页T-SQL代码，分散在各处，主要的问题是：测试时数据量太小，最多只有2万多条，不同方法的体现出性能差别有疑惑，另外当初在学习sqlserver 2005 时，一位同学信誓旦旦说分页
在SQLSERVER 2005中可以使用EXCEPT关键字，性能最好，理由是EXCEPT是集合运算。当时信以为真。工作以后，发现在SQLSERVER 2005中的分页存储过程都没有用到EXCEPT方法，就更疑惑了。
这次系统的看《Inside Microsoft® SQL Server™ 2005 T-SQL Querying 》这本书时，发现有个创建数据库脚本，数据时随机的，把它作为测试数非常不错，脚本如下（稍微做调整）：
--在我电脑上该数据库的创建持续1分钟多 SET NOCOUNT ON; USE master; GO IF DB_ID('Performance') IS NOT NULL DROP DATABASE Performance; ELSE CREATE DATABASE Performance; GO USE Performance; GO --创建辅助表Nums，1百万行数据 IF OBJECT_ID('dbo.Nums') IS NOT NULL DROP TABLE dbo.Nums; GO CREATE TABLE dbo.Nums(n INT NOT NULL PRIMARY KEY); DECLARE @max AS INT, @rc AS INT; SET @max = 1000000; SET @rc = 1; INSERT INTO Nums VALUES(1); WHILE @rc * 2 <= @max BEGIN INSERT INTO dbo.Nums SELECT n + @rc FROM dbo.Nums; SET @rc = @rc * 2; END INSERT INTO dbo.Nums SELECT n + @rc FROM dbo.Nums WHERE n + @rc <= @max; GO -- 如果存在dbo.Orders表则删除 IF OBJECT_ID('dbo.Orders') IS NOT NULL DROP TABLE dbo.Orders; GO -- 定义写变量，以此来创建随机的数据，不明白就忽略算了
DECLARE @numorders AS INT, @numcusts AS INT, @numemps AS INT, @numshippers AS INT, @numyears AS INT, @startdate AS DATETIME; SELECT @numorders = 1000000, @numcusts = 20000, @numemps = 500, @numshippers = 5, @numyears = 4, @startdate = '20030101'; -- 创建Orders表 CREATE TABLE dbo.Orders ( orderid INT NOT NULL, custid CHAR(11) NOT NULL, empid INT NOT NULL, shipperid VARCHAR(5) NOT NULL, orderdate DATETIME NOT NULL, filler CHAR(155) NOT NULL DEFAULT('a') ); --随机的填入一些数据，1百万行数据 INSERT INTO dbo.Orders(orderid, custid, empid, shipperid, orderdate) SELECT n AS orderid, 'C' + RIGHT('000000000' + CAST( 1 + ABS(CHECKSUM(NEWID())) % @numcusts AS VARCHAR(10)), 10) AS custid, 1 + ABS(CHECKSUM(NEWID())) % @numemps AS empid, CHAR(ASCII('A') - 2 + 2 * (1 + ABS(CHECKSUM(NEWID())) % @numshippers)) AS shipperid, DATEADD(day, n / (@numorders / (@numyears * 365.25)), @startdate) as orderdate FROM dbo.Nums WHERE n <= @numorders ORDER BY CHECKSUM(NEWID());
--为orderid创建主键，为custid,empid添加索引，并包含shipperid,orderdate列 ALTER TABLE dbo.Orders ADD CONSTRAINT PK_Orders_orderid PRIMARY KEY CLUSTERED(orderid); CREATE INDEX idx_Orders_custid_empid ON dbo.Orders(custid,empid) ;
第一种分页方法：使用TOP与NOT IN来分页。注意，获取T-SQL脚本运行的时间，单击SSMS工具栏上的【包含客户端统计信息】按钮。
CREATE PROCEDURE usp_EvaluatePerformanceBy_Top_In @pagesize INT, @pagenum INT AS SELECT TOP(@pagesize) o1.orderid,o1.custid,o1.empid FROM dbo.Orders o1 WHERE o1.orderid NOT IN( SELECT TOP((@pagenum-1)*@pagesize) o2.orderid FROM dbo.Orders o2 );
GO
--当读取1万条附近的20条数据时花的时间为 EXEC usp_EvaluatePerformanceBy_Top_In @pagesize=20,@pagenum=5000
时间为257.400毫秒
--当读取第20万条附近的20条数据时花的时间为 EXEC usp_EvaluatePerformanceBy_Top_In @pagesize=20,@pagenum=10000
时间为：152.700，sqlserver 利用了缓存的可执行计划，故时间要少
--当读取第80万条附近的20条数据时花的时间为 EXEC usp_EvaluatePerformanceBy_Top_In @pagesize=20,@pagenum=40000
所花的时间为：240.200,同样sqlserver利用了缓存的可执行计划，时间变化不大
总结一下，当利用top和not in 来分页，且要查找的列都已在索引中时，
1万条数据附近，为257.400ms
20万数据条附近，为152.700ms
80万条数据附近，为240.200ms
第二种分页方法使用CTE和Row_Number函数，请看如下的T-SQL代码
--清空可执行计划缓存 DBCC FREEPROCCACHE; DBCC FREESYSTEMCACHE ('ALL'); --使用CTE和ROW_NUMBER()来分页 CREATE PROCEDURE usp_EvaluatePerformanceBy_Row_Number @pagesize INT, @pagenum INT AS WITH Tmp AS ( SELECT ROW_NUMBER() OVER (ORDER BY orderid ASC) AS colnum, orderid, custid, empid FROM dbo.Orders o1 ) SELECT orderid,custid,empid FROM Tmp WHERE colnum>(@pagenum-1)*@pagesize AND colnum<=@pagenum*@pagesize; GO --当读取1万条附近的20条数据时花的时间为 EXEC usp_EvaluatePerformanceBy_Row_Number @pagesize=20,@pagenum=500
所花费的时间为：21.500
--当读取第20万条附近的20条数据时花的时间为 EXEC usp_EvaluatePerformanceBy_Row_Number @pagesize=20,@pagenum=10000
所花费的时间为：44.900
--当读取第80万条附近的20条数据时花的时间为 EXEC usp_EvaluatePerformanceBy_Row_Number @pagesize=20,@pagenum=40000
所花费的时间为：118.400
总结一下，当利用CTE和ROW_NUMBER 来分页，且要查找的列都在索引中时，
1万条数据附近，为21.500ms
20万数据条附近，为44.900ms
80万条数据附近，为118.400ms
第三种分页的方法是使用EXCEPT，请看如下的T-SQL代码
--清空可执行计划缓存 DBCC FREEPROCCACHE; DBCC FREESYSTEMCACHE ('ALL'); --利用except来求分页 CREATE PROCEDURE usp_EvaluatePerformanceBy_Except @pagesize INT, @pagenum INT AS SELECT TOP(@pagesize*@pagenum) orderid, custid, empid FROM dbo.Orders EXCEPT ( SELECT TOP((@pagenum-1)*@pagesize)
orderid, custid, empid FROM dbo.Orders ); GO --当读取第1万条附近的20条数据时花的时间为 EXEC usp_EvaluatePerformanceBy_Except @pagesize=20,@pagenum=500 所需的时间为：123.000
--当读取第20万条附近的20条数据时花的时间为 EXEC usp_EvaluatePerformanceBy_Except @pagesize=20,@pagenum=10000 所花时间为：174.600
--当读取第80万条附近的20条数据时花的时间为 EXEC usp_EvaluatePerformanceBy_Except @pagesize=20,@pagenum=40000 所花时间为：390.200
总结一下，当利用EXCEPT来分页，且要查找的列都在索引中时，
1万条数据附近，为123.000ms
20万数据条附近，为174.600ms
80万条数据附近，为390.200ms
第四种分页的方法是利用TOP和Max函数结合，请看如下的T-SQL代码
--清空可执行计划缓存 DBCC FREEPROCCACHE; DBCC FREESYSTEMCACHE ('ALL'); --第总方法通过top和max来求值 CREATE PROCEDURE usp_EvaluatePerformanceBy_Top_Max @pagesize INT, @pagenum INT AS SELECT TOP(@pagesize) orderid,custid,empid FROM dbo.Orders o1 WHERE o1.orderid>( SELECT max(d.orderid) as num FROM ( SELECT top((@pagenum-1)*@pagesize) orderid FROM dbo.orders o2 ORDER BY orderid )AS d ) GO --当读取第1万条附近的20条数据时花的时间为 EXEC usp_EvaluatePerformanceBy_Top_Max @pagesize=20,@pagenum=500 时间为：365.100
--当读取第20万条附近的20条数据时花的时间为 EXEC usp_EvaluatePerformanceBy_Top_Max @pagesize=20,@pagenum=10000 时间为：319.800
--当读取第80万条附近的20条数据时花的时间为 EXEC usp_EvaluatePerformanceBy_Top_Max @pagesize=20,@pagenum=40000 所花的时间为：137.000
总结一下，当利用TOP和MAX来分页，且要查找的列都在索引中时，
1万条数据附近，为365.100ms
20万数据条附近，为319.800ms
80万条数据附近，为137.000ms
看一看最后的结论：
|
|
TOP 与 NOT IN |
TOP 与 MAX |
ROW_NUMBER() |
EXCEPT |
|
1万条数据 |
257.400ms |
365.100ms |
21.500ms |
123.000ms |
|
20万条数据 |
152.700ms |
319.800ms |
44.900ms |
174.600ms |
|
80万条数据 |
240.200ms |
137.000ms |
118.400ms |
390.200ms |
在SQLSERVER2005中优先选择ROW_NUMBER()方法来分页，在SQLSERVER 2000中优先选择TOP和NOT IN 方法！！
