SQL SERVER 2014 下IF EXITS 居然引起執行計劃變更的案例分享

  這個問題是在SQL SERVER 2005 更新到SQL SERVER 2014的測試過程中一同僚發現的。我覺得有點意思，遂稍微修改一下腳本展示出來，本來想構造這樣的一個案例來示範，但是畏懼麻煩，遂直接貼上原表，希望Leader不要叼我（當然個人覺得真沒啥，兩張表名而已，真洩露不了啥資訊）。

    腳本如下所示，非常簡單的一段SQL語句，我将其分為SQL1、SQL2、SQL3.  其實SQL2、SQL3是差不多的，唯一的差別在于多了一個IF EXISTS

在SQL SERVER 2005的環境中，整個批處理的SQL執行隻需要不到1秒的樣子。我們也能看到執行計劃的COST對比值為0%，99%，1%。

<a href="http://images2015.cnblogs.com/blog/73542/201512/73542-20151217232624974-2003136990.png"></a>

在SQL SERVER 2014（SQL Server 2014 - 12.0.2000.8 Standard Edition ）中執行時間突然變成了4分41秒。 最奇怪的是查詢計劃的COST比值依然為0%，99%，1%。實際測試發現這個COST的比值是不準确的。因為單獨執行SQL1、SQL2隻需要一秒。但是執行SQL3就需要4分多鐘。（當然SQL SERVER 2005 與SQL SERVER 2014的資料，索引是一緻的，細心的人會注意下面提示缺少索引，加上這個索引依然慢的出奇,這個影響因素完全可以忽略）

<a href="http://images2015.cnblogs.com/blog/73542/201512/73542-20151217232628099-1738384390.png"></a>

SQL 2的實際執行計劃如下所示

<a href="http://images2015.cnblogs.com/blog/73542/201512/73542-20151217232632006-1661734369.png"></a>

SQL 3的實際執行計劃如下所示

<a href="http://images2015.cnblogs.com/blog/73542/201512/73542-20151217232637256-1607839652.png"></a>

另外，表dbo.fnRepairOperation的記錄數有332553，dbo.fnJobTraceHdr 的記錄數為110058。表變量@FNCardTable記錄數為1.對比執行計劃，我們可以看到兩者的Nested Loops的外部表變化了，從表變量@FNCardTable變成了dbo.fnRepairOperation

我們先來看看SQL2執行計劃裡面的一些詳細資訊，我們可以看到外邊循環表為@FNCardTable，循環次數為1（Actual Number of Rows 值為1），内部循環表為dbo.fnJobTraceHdr，循環次數為1（Number of Executions為1），符合條件的記錄集資料為1條（Actual Number of Rows 值為1）

<a href="http://images2015.cnblogs.com/blog/73542/201512/73542-20151217232639271-1363827700.png"></a>

<a href="http://images2015.cnblogs.com/blog/73542/201512/73542-20151217232640787-317744162.png"></a>

那麼再來看SQL3， 外部循環表變為dbo.fnRepairOperation，它走表掃描（Table Scan），循環次數為432（Actual Number of Rows）,内部循環表為dbo.fnJobTraceHdr， 走索引掃描，總共循環了47545056次，這個值怎麼來的呢？ 因為内部循環表中符合記錄數為110058（表dbo.fnJobTraceHdr的記錄數）， 110058*432 = 47545056，也就是說總共循環了四千七百多萬次。 偶的神啊。難怪如此之慢。起初，我以為是統計資訊不準确導緻資料庫優化器選擇了錯誤的執行計劃，于是我更新了這兩個表的統計資訊，甚至連索引也重建了。結果還是如此。看來的确是優化器沒有選擇最優的執行計劃。但是沒有IF EXITS它又是正常的， 加了IF EXITS後執行計劃就變成這個鳥樣。說不清是優化器的bug還是算法問題所導緻。

<a href="http://images2015.cnblogs.com/blog/73542/201512/73542-20151217232642662-798067188.png"></a>

<a href="http://images2015.cnblogs.com/blog/73542/201512/73542-20151217232644599-1278264966.png"></a>

那麼怎麼解決這個問題，可以用聯接提示(HASH JOIN HINT)指定SQL語句走HASH JOIN，此時批處理的SQL語句可以1秒出來。另外就是改寫該SQL語句的寫法。在此不做過多闡述

其實這個案例也間接驗證了嵌套循環連接配接，随着資料量的增長，這種方式對性能的消耗将呈現出指數級别的增長。