Re: [SQL ] 哪一種SQL文效率比較高呢??

看板Database (資料庫)作者flakchen (flak)時間18年前 (2006/11/20 18:51)推噓1(1推 0噓 0→)

留言1則, 1人參與討論串6/6 (看更多)

※ 引述《flakchen (flak)》之銘言： : : 就MS-SQL而言，IN 的效果並不差，差的是NOT IN : LEFT OUTER JOIN的效率也好不到哪裡，如果必須取得「不存在於...」 : 的資料，請用NOT EXISTS : : SELECT TB1.fd1 : FROM TB1 : WHERE NOT EXISTS (SELECT top 1 1 FROM TB2 WHERE TB1.fd1 = TB2.fd2 ) : : : -- : ※ 發信站: 批踢踢實業坊(ptt.cc) : ◆ From: 220.132.166.116 : → webberhan:你的根據是?可以分享一下推論過程嗎? 11/20 13:46 : 推 jerryen:因為那是outer join吧 11/20 17:27 請參考http://www.sql-server-performance.com/transact_sql.asp If you currently have a query that uses NOT IN, which offers poor performance because the SQL Server optimizer has to use a nested table scan to perform this activity, instead try to use one of the following options instead, all of which offer better performance: Use EXISTS or NOT EXISTS Use IN Perform a LEFT OUTER JOIN and check for a NULL condition [6.5, 7.0, 2000] Updated 10-30-2003 ***** When you have a choice of using the IN or the EXISTS clause in your Transact-SQL, you will generally want to use the EXISTS clause, as it is usually more efficient and performs faster. [6.5, 7.0, 2000] Updated 10-30-2003 不過再根據我我日常要處理500萬-5000萬筆的大型資料表，時常要 TRY AND ERROR來找出最佳效能解的累積經驗： LEFT JOIN會導致SQL要耗費更多硬碟I/O來儲存兩個資料表的連結資料，所以如果查詢結果不需要第二個資料表的資料，能不JOIN就不要JOIN，尤其是LEFT/RIGHT OUTER JOIN IN跟EXISTS比起來，沒有他說的那麼爛，通常我感覺不到兩者效能有何差別 -- ※ 發信站: 批踢踢實業坊(ptt.cc) ◆ From: 210.64.110.97