SQL Paging 的效率

T-SQL:

SET STATISTICS io ON
SET STATISTICS time ON
go
Select * FROM   (Select   Top 40000   *   from   core_users ) A  where Not   Exists
( Select * From (Select   top 20000  userid   from   core_users ) B where A.userid=B.userid )
go
SET STATISTICS profile OFF
SET STATISTICS io OFF
SET STATISTICS time OFF
SET STATISTICS io ON
SET STATISTICS time ON
go
SELECT *
FROM (
SELECT *,ROW_NUMBER() OVER (ORDER BY UserId DESC) AS RowNumber
FROM Core_users ) T
WHERE T.RowNumber BETWEEN 20000 AND 40000
SET STATISTICS profile OFF
SET STATISTICS io OFF
SET STATISTICS time OFF

 

 

Result:

SQL Server parse and compile time:
   CPU time = 30 ms, elapsed time = 33 ms.

(10611 row(s) affected)
Table 'Core_Users'. Scan count 2, logical reads 2654, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

SQL Server Execution Times:
   CPU time = 681 ms,  elapsed time = 3537 ms.
SQL Server parse and compile time:
   CPU time = 0 ms, elapsed time = 1 ms.

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

SQL Server Execution Times:
   CPU time = 0 ms,  elapsed time = 1 ms.
SQL Server parse and compile time:
   CPU time = 30 ms, elapsed time = 32 ms.

 

(10612 row(s) affected)
Table 'Core_Users'. Scan count 1, logical reads 1574, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

SQL Server Execution Times:
   CPU time = 601 ms,  elapsed time = 4101 ms.

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

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