語句一：select count(*) from A where A.a not in (select a from B)

語句二：select count(*) from A left join B on A.a = B.a where B.a is null

語句三：select count(*) from A where not exists (select a from B where A.a = B.a)

知道以上三條語句的實際效果是相同的已經很久了，但是一直沒有深究其間的效率對比。一直感覺上語句二是最快的。
今天工作上因為要對一個數千萬行資料的庫進行資料清除，需要刪掉兩千多萬行資料。大量的用到了以上三條語句所要實現的功能。本來用的是語句一，但是結果是執行速度1個小時32分，日誌檔案佔用21GB。時間上雖然可以接受，但是對硬碟空間的佔用確是個問題。因此將所有的語句一都換成語句二。本以為會更快。沒想到執行40多分鐘後，第一批50000行都沒有刪掉，反而讓SQL SERVER崩潰掉了，結果令人詫異。試了試單獨執行這條語句，查詢近一千萬行的表，語句一用了4秒，語句二卻用了18秒，差距很大。語句三的效率與語句一接近。


第二種寫法是大忌，應該儘量避免。第一種和第三種寫法本質上幾乎一樣。 

假設buffer pool足夠大，寫法二相對於寫法一來說存在以下幾點不足： 
（1）left join本身更耗資源（需要更多資源來處理產生的中間結果集） 
（2）left join的中間結果集的規模不會比表A小 
（3）寫法二還需要對left join產生的中間結果做is null的條件篩選，而寫法一則在兩個集合join的同時完成了篩選，這部分開銷是額外的 

這三點綜合起來，在處理海量資料時就會產生比較明顯的區別（主要是記憶體和CPU上的開銷）。我懷疑樓主在測試時buffer pool可能已經處於飽和狀態，這樣的話，寫法二的那些額外開銷不得不借助磁碟上的虛擬記憶體，在SQL Server做換頁時，由於涉及到較慢的I/O操作因此這種差距會更加明顯。 

關於日誌檔案過大，這也是正常的，因為刪除的記錄多嘛。可以根據資料庫的用途考慮將恢復模型設為simple，或者在刪除結束後將日誌truncate掉並把檔案shrink下來。


因為以前曾經作過一個對這個庫進行無條件刪除的指令碼，就是要刪除資料量較大的表中的所有資料，但是因為客戶要求，不能使用truncate table，怕破壞已有的庫結構。所以只能用delete刪，當時也遇到了日誌檔案過大的問題，當時採用的方法是分批刪除，在SQL2K中用set rowcount @chunk，在SQL2K5中用delete top @chunk。這樣的操作不僅使刪除時間大大減少，而且讓日誌量大大減少，只增長了1G左右。 
但是這次清除資料的工作需要加上條件，就是delete A from A where ....後面有條件的。再次使用分批刪除的方法，卻已經沒效果了。 
不知您知不知道這是為什麼。