SQL使用OFFSET/FETCH NEXT实现分页对比ROW_NUMBER，性能差异

第一步，创建表

• CREATE TABLE Customers (  
•     CustomerID INT,  
•     CustomerNumber CHAR(4),  
•     CustomerName VARCHAR(50),  
•     CustomerCity VARCHAR(20) )  
• GO  

第二步，编写语句

插入语句

/*使用CTE递归循环插入 运用CTE递归插入，速度较快*/  
TRUNCATE table Customers  
GO 
DBCC DROPCLEANBUFFERS  
DBCC FREEPROCCACHE  
  
SET STATISTICS IO ON;  
SET STATISTICS TIME ON;  
GO  
  
DECLARE @d Datetime  
SET @d=getdate();  
  
WITH Seq (num,CustomerNumber, CustomerName, CustomerCity) AS  
(SELECT 1,'0000','Customer 0',cast('X-City' as NVARCHAR(20))  
UNION ALL  
SELECT num + 1,'0000','Customer 0',  
cast(CHAR(65 + (num % 26)) + '-City' AS NVARCHAR(20))  
FROM Seq  
WHERE num <=   10000/*CTE批量递归500万数据量 用时一般大概3分钟 因个人电脑而已5000000*/
)  
INSERT INTO Customers (CustomerNumber, CustomerName, CustomerCity)  
SELECT CustomerNumber, CustomerName, CustomerCity  
FROM Seq  
OPTION (MAXRECURSION 0)  
  
select [500万数据量插入完毕，共花费时间(毫秒)]=datediff(ms,@d,getdate())  
  
SET STATISTICS IO OFF ;  
SET STATISTICS TIME OFF;  
GO

查询语句row_number

SET STATISTICS IO ON;
SET STATISTICS TIME ON;

SELECT * FROM
(
    SELECT ROW_NUMBER() OVER (ORDER BY customerid desc) AS 'RowNumber', * FROM Customers
) AS UserInfo
WHERE UserInfo.customerid BETWEEN 5001 AND 5020

SET STATISTICS IO OFF;
SET STATISTICS TIME OFF;

查询语句fetchnext

SET STATISTICS IO ON;
SET STATISTICS TIME ON;
select * from Customers order by customerid desc offset 5000 rows fetch next 20 rows only
SET STATISTICS IO OFF;
SET STATISTICS TIME OFF;

第三步，查询对比

1万条数据量查询对比

逻辑读取次数和执行时间fetchnext 获得一分

另外分别测试了读取开头100条，和末尾20条的情况。末尾20条查询亮着性能详尽，fetchnext还是相对快一点。

50万条数据量查询对比

逻辑读取次数和执行时间fetchnext 获得一分

另外分别测试了读取开头100条，和末尾20条的情况。末尾20条查询亮着性能详尽，fetchnext还是相对快一点。

500万条数据量查询对比

逻辑读取次数和执行时间fetchnext 获得一分,fetchnext 逻辑读取和执行时间性能是rownumber的一倍

1亿条数据量查询对比（final battle）  

再多不测了，哇咔咔。

下面是废话，哈哈哈，人生么，务实也要务虚。

500万数据插入用了3分钟。

5000万数据，推测30分钟。

1亿额，难道用1个小时么刚刚过去了三分钟，还在查询，那就让子弹再飞一会。

 

刚刚去看了看风景，思考了下人生。回来一看，额，还有个sql在执行，还没完，数据量确实有点大了。我还是暂停了语句吧

打算顺便测试下 count（1） 和（*）到底有啥区别，目前来看，500万以下的数据量查询效果一样，没啥区别

5千万条数据量查询对比（final battle）

吃完午饭眯会，继续码字，1亿条太多了，电脑卡死

 

逻辑读取次数和执行时间fetchnext 获得一分,此时已经是10倍的效率差距了

同时验证了下count（*）和count（1）的区别

 

对比结果

fetch性能高

简单数据下：count（1）和count（*）性能么有什么大的差别。推测，复杂数据表1比*性能高