网上有很多的分页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 方法!!