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.

Difference between "Not in" and "Not Exist" In SQL

1.如果要查詢的列上沒有NULL值，NOT   IN   和   NOT   Exist效率是一樣的.

2.當資料量較少時,not   in執行速度比not   exists快，反之,not   exists較可取
3.同樣，處理   IN   和Exist   也有下面的規則:

1. 內查詢為小表，外查詢為大表時，使用IN的效率高
2. 內查詢為大表，外查詢為小表時，使用Exist的效率高.

例:

select   count(*)   from   bigtable   where   id   in(select   id   from   smalltable)  
效率高
select   count(*) from bigtable where exists ( select null from   smalltable where  smalltable.id=bigtable.id   )  
效率低

select   count(*)   from   smalltable  where   id   in   (select   id   from   bigtable)  
效率差一點
select   count(*)     from   smalltable where   exists   (   select   null   from   bigtable where   smalltable.id   =   bigtable.id   )  
效率好一點.