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工具栏上的【包含客户端统计信息】按钮。

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

脚本如下：

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 方法！！