四种sql server 数据库分页的测试

SET statistics io on
SET STATISTICS TIME ON
GO

 

----1
PRINT 'fist begin'
DECLARE @time DATETIME
DECLARE @ms int
SET @time =GETDATE()
SELECT * FROM Client.client AS c
INNER JOIN
(
SELECT RowNum, ClientID FROM
 (
  SELECT ROW_NUMBER()OVER(ORDER by ClientID ASC) AS RowNum ,ClientID FROM client.client
 ) AS cc
WHERE cc.RowNum between 10000 and 10500
)AS t
ON c.ClientID = t.ClientID
SET @ms = DATEDIFF(MS,@time,GETDATE())
PRINT @ms
PRINT 'fist end'
GO

 

----2
PRINT 'second begin'
DECLARE @time DATETIME
DECLARE @ms int
SET @time =GETDATE()
SELECT * FROM 
(
 SELECT ROW_NUMBER()OVER(ORDER by ClientID ASC) AS RowNum ,* FROM client.client
) 
AS c WHERE c.RowNum BETWEEN 10000 AND 10500
SET @ms = DATEDIFF(MS,@time,GETDATE())
PRINT @ms
PRINT 'second end'
GO

 

 

--3

PRINT 'third begin'
DECLARE @time DATETIME
DECLARE @ms int
SET @time =GETDATE()
SELECT TOP(500) * FROM Client.Client
WHERE ClientID NOT IN(SELECT TOP 9999 ClientID FROM Client.Client)

SET @ms = DATEDIFF(MS,@time,GETDATE())
PRINT @ms
PRINT 'third end'
GO

 

 

PRINT 'fourth begin'
DECLARE @time DATETIME
DECLARE @ms int
SET @time =GETDATE()
SELECT TOP(500) * FROM Client.Client
WHERE ClientID > (SELECT MAX(ClientID) FROM
(SELECT top 9999 ClientID FROM Client.Client ORDER BY ClientID) as c )

SET @ms = DATEDIFF(MS,@time,GETDATE())
PRINT @ms
PRINT 'fourth end'
GO

 

fist begin

(501 row(s) affected)
Table 'Client'. Scan count 1, logical reads 2658, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

(1 row(s) affected)

 SQL Server Execution Times:
   CPU time = 31 ms,  elapsed time = 218 ms.

 SQL Server Execution Times:
   CPU time = 0 ms,  elapsed time = 0 ms.
220

fist end

second begin

(501 row(s) affected)
Table 'Client'. Scan count 1, logical reads 1155, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

(1 row(s) affected)

 SQL Server Execution Times:
   CPU time = 16 ms,  elapsed time = 158 ms.
156

second end

third begin

(500 row(s) affected)
Table 'Worktable'. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'Client'. Scan count 2, logical reads 1166, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

(1 row(s) affected)

 SQL Server Execution Times:
   CPU time = 47 ms,  elapsed time = 140 ms.
140
third end

 

fourth begin

(500 row(s) affected)
Table 'Client'. Scan count 2, logical reads 1166, physical reads 0, read-ahead reads 6, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

(1 row(s) affected)

 SQL Server Execution Times:
   CPU time = 16 ms,  elapsed time = 230 ms.

230

fourth end

 

 

 

数据量 6w

个人测试结果

SCAN count:4=3>1=2       3为2，其他为1

logical reads ：2>3=4>1 (2658>1166>1155)

 实际 time:4〉1>2>3   和执行顺序有关

理论 time：2〉3>4〉1 ??????? 

大数据量没测试。

估计是数据量的问题， 大数据的时候应该差别挺大的。 有兴趣的可以测试一下。

请提个建议。。。