四種sql server 資料庫分頁的測試

SET statistics io on
SET STATISTICS TIME ON
GO

 

View Code ----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----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 --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

 

 

方法4PRINT '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 ??????? 

大資料量沒測試。

估計是資料量的問題， 大資料的時候應該差別挺大的。 有興趣的可以測試一下。

請提個建議。。。