[SQL SERVER]Avoid use not operator in where clause

大部分情況,where 中使用 not 操作都無法使用索引搜尋,

都將使用table / index 掃描取代資料存取方式

Code review時, Linq to EF使用 != 操作很常見,

但這樣的操作大部分情況都將讓索引無真正用武之地(當然不只這些EF查詢操作),

下面我來簡單還原一下

from a in TransactionHistoryTestAs
join b in Products  
on a.ProductID equals  b.ProductID
where  a.TransactionDate >=new DateTime(2008, 2, 27)
&& a.ProductID !=986 
&& a.ProductID !=532
select a

可以看到!=轉譯後的SQL都使用<>(not)操作,下面我使用SSMS來執行該SQL並看看執行計畫

DECLARE @p0 DateTime = '2008-02-27 00:00:00.000'
DECLARE @p1 Int = 986
DECLARE @p2 Int = 532
select a.ProductID,a.TransactionDate,a.Quantity  
from TransactionHistoryTestA a join Product b
on a.ProductID=b.ProductID
where a.TransactionDate>=@p0 
and 
a.ProductID <>@p1 
and
a.ProductID <>@p2
option(recompile)

可以看到都使用Index Scan,整體成本:0.865,現在,我們想要都使用索引搜尋是否可以呢?我改寫TSQL如下

DECLARE @p0 DateTime = '2008-02-27 00:00:00.000'
DECLARE @p1 Int = 986
DECLARE @p2 Int = 532

select a.ProductID,a.TransactionDate,a.Quantity  
from TransactionHistoryTestA a join Product b
on a.ProductID=b.ProductID
where a.TransactionDate>=@p0 
and ((a.ProductID >=0 and a.ProductID<@p2 )
or (a.ProductID >=@p2+1 and a.ProductID<@p1)
or (a.ProductID>=@p1+1))
option(recompile)

改寫後果然都使用索引搜尋,整體成本下降為0.784,但真實世界的查詢需求比我文章例子複雜多了,

而這也是我認為任何物件導向語言都無法取代TSQL,

這就好像F1賽道上,你不可能使用自排(ORM)賽車手來挑戰專業手排賽車手,

最後,祝大家使用EF達到使用者需求同時兼具查詢效能最佳化。

 

參考

Comparison Expressions

避免反向查詢

Not Equal in WHERE Clause