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;GOIF DB_ID('Performance') IS NOT NULLDROP DATABASE Performance;ELSECREATE DATABASE Performance; GOUSE Performance;GO--创建辅助表Nums，1百万行数据IF OBJECT_ID('dbo.Nums') IS NOT NULL  DROP TABLE dbo.Nums;GOCREATE 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 <= @maxBEGIN  INSERT INTO dbo.Nums SELECT n + @rc FROM dbo.Nums;  SET @rc = @rc * 2;ENDINSERT 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工具栏上的【包含客户端统计信息】按钮。

--把SQLSERVER执行计划缓存清空DBCC FREEPROCCACHE;DBCC FREESYSTEMCACHE ('ALL');

脚本如下：

CREATE PROCEDURE usp_EvaluatePerformanceBy_Top_In@pagesize INT,@pagenum INTASSELECT TOP(@pagesize) o1.orderid,o1.custid,o1.empid FROM dbo.Orders o1WHERE 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 INTASWITH Tmp AS (SELECTROW_NUMBER() OVER (ORDER BY orderid ASC) AS colnum,orderid,custid,empidFROM dbo.Orders o1)SELECT orderid,custid,empid FROM TmpWHERE 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 INTASSELECT TOP(@pagesize*@pagenum) orderid,custid,empid FROM dbo.OrdersEXCEPT (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 INTASSELECT TOP(@pagesize) orderid,custid,empidFROM dbo.Orders o1WHERE o1.orderid>(SELECT max(d.orderid) as num FROM (SELECT top((@pagenum-1)*@pagesize) orderidFROM 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 方法！！