Nested Loops join時顯示no join predicate原因分析以及解決辦法

本位出處：http://www.cnblogs.com/wy123/p/6238844.html 最近遇到一個存儲過程在某些特殊的情況下，效率極其低效，至於底下到什麼程度我現在都沒有一個確切的數據，因為預期很快就可以查詢出來結果的SQL，實則半個小時都出不來，後面會有截圖觀察執行計劃的時候發現中 ...

本位出處：http://www.cnblogs.com/wy123/p/6238844.html

　　最近遇到一個存儲過程在某些特殊的情況下，效率極其低效，
　　至於底下到什麼程度我現在都沒有一個確切的數據，因為預期很快就可以查詢出來結果的SQL，實則半個小時都出不來，後面會有截圖
　　觀察執行計劃的時候發現中間有一步中出現一個類似如下非常規的連接提示警告，如下圖

　 no join predicate 意思就是沒有連接謂詞，表之間join的時候沒有指定連接謂詞可以導致no join predicate，
　但是反過來也是一定成立的嗎，明明寫了連接條件，仍舊提示no join predicate，為什呢？
　下麵先從no join predicate 入手開始，說明什麼時候會出現no join predicate ，以及原因和解決辦法。

1，未指定連接條件下導致的no join predicate

　　兩個表在沒有指定連接條件的情況下，做運算的結果是計算器笛卡爾積，當然是沒有連接謂詞的，提示no join predicate 也很容易理解
　　上一段簡單的代碼演示一下，如下創建兩張表，#t1，#t2,至於測試數據為什麼是這樣子，我下麵會繼續做解釋

create table #t1(id int,name varchar(100))
create table #t2(id int,name varchar(100))

insert into #t1 values (1,newid())
insert into #t1 values (1,newid())

insert into #t2 values (1,newid())
insert into #t2 values (1,newid())

首先看計算笛卡爾積的時候的執行計劃，Nested Loops 中的紅叉叉，就表明是沒有連接謂詞，當然這個查詢SQL中也確實沒有連接謂詞，這種情況下也很容易理解。

2，指定了連接條件下的no join predicate

　　這裡即便是指定了連接條件，仍然提示沒有連接謂詞，這個原因又是為什麼呢？
　　此時就需要看表中的數據特點了，從上面造的測試數據可以看出，#t1表id = 1 的是兩行，#t2 表的同樣，id = 1的數據也是兩行
　　此時兩張表的join，是多對多的關係，多對多的情況下就是計算笛卡爾積，這就是這種情況下提示沒有連接謂詞的原因。
　　詳細請參考：http://www.cnblogs.com/liwei225/p/5056460.html，大神早就有詳細的分析，感謝liwei225大神的分享

　　不過我這裡還有一個疑問，還是上述兩張表，指定連接條件，但是不指定查詢條件，也就是沒有where a.id = 1，此時就沒有提示no join predicate
　　這個原因我也沒弄懂，後面再想想為什麼，希望路過的大神幫忙解釋一下，謝謝。

3，指定了連接條件的情況下，某些查詢條件下會出現no join predicate

　　這是一個實際業務的SQL，從存儲過程中扣出來的代碼，因為有比較多的查詢條件，最後組裝的動態SQL也不完全一樣，絕大多數情況下是沒有問題的，
　　但是當在where 條件中添加某一個查詢條件之後，效率就開始嚴重下降，至於下降到什麼程度，截圖是運行了35分鐘之後取消的
　　在這個SQL運行期間，伺服器CPU直接飆升至100%，並且是持續性的

　　截圖一個對比測試的，僅僅在上面的SQL中加了一個OPTION(FORCE ORDER)查詢提示，強制按照書寫的表的順序驅動，結果2秒鐘就出來結果了
　　執行計劃跟上面是不一樣的，同時也沒有顯示no join predicate，不能說加了一個強制提示就有了連接謂詞，不加強制提示就沒有連接謂詞吧？
　　從對比情況看，可以說明，沒有非常嚴重的外界因素干擾，比如缺少索引，統計信息有問題等等
　　倘若如此，加了OPTION(FORCE ORDER)查詢提示的SQL與不加OPTION(FORCE ORDER)查詢提示的SQL差別不可能這麼大，一定是執行計劃的選擇出了問題。

　　那麼久繼續分析這個執行計劃。
　　通常情況下，我們會首先分析執行計劃，什麼索引使用（被抑制）了，索引碎片了，參數嗅探了，統計信息過期了（取樣不夠），都一一分析過，
　　這些額外因素只會在一定程度上拖慢SQL的效率，而不是拖慢到如此相差幾個數量級的程度
　　那麼來分析，沒有加OPTION(FORCE ORDER)為什麼會這麼慢？
　　實際上，這個SQL的執行計劃只能從預估執行計劃來看，因為實在等不到這個SQL運行完成而看實際執行計劃
　　如題，預估執行計劃顯式，中間有一步存在一個如上所述的沒有連接謂詞警告

　　我們看一下這個Nested Loops的詳細信息，確實提示沒有連接謂詞，並且顯式的預估行數為126469000行，超過了1億行了，
　　根據具體的數據分佈和查詢條件分析，如果不做笛卡爾積，這個中間結果是怎麼也達不到億級別的，這個妥妥的是笛卡爾積
　　如果真的要計算出來超過一億行這麼大一個結果集，代價可想而知。

　　實際上1億行的笛卡爾積，並需要太多的基數，select 10000*10000就可以達到了，也就是兩個過萬的結果集做笛卡爾積運算，就可以算出來一億行的結果
　　結果也證明，第一個SQL在做查詢的時候CPU飆升，而並沒有很高的物理IO，慢就慢在笛卡爾結果的運算上。

　那麼這裡的笛卡爾積是怎麼出現的？具體數據我不方便分析，這裡做一個簡單的推倒
　比如這麼一個SQL：
　　select * from TableA a
　　　　inner join TableB b on a.Identifier1 = b.Identifier1
　　　　inner join TableC c on b.Identifier2 = c.Identifier2
　　where a.Column_X = ***
　　　　and b.Column_Y = ***
　　　　and Other Filter Condition

　　連接條件都是有的，我們暫時簡化問題，忽略查詢條件，從邏輯上分析
　　正常邏輯是A表結果驅動B表（ a.Identifier1 = b.Identifier1 ），
　　用A表和B表join的結果，藉助B表的Identifier2 驅動C表（ b.Identifier2 = c.Identifier2 ），這裡的A表和C表示沒有直接關係的，
　　如果A表和C表結合起來，最後驅動B表，可以想象，因為A表和C表之間沒有直接的關係，強制連接的話，A表和C表計算出來的結果必然是笛卡爾積
　　這個笛卡爾積就類似於上面截圖Nested Loops中的預估的超過一億行數的結果集。

　　為什麼SQL Server會私自更改表之前的連接方式，從而導致笛卡爾積？
　　執行計劃的選擇是一個複雜的計算過程。執行計劃的生成是跟索引，統計信息，表中的數據分佈，系統資源等等多種因素一併計算出來的，
　　SQL Server可能是根據查詢條件，選擇了自己認為一種“高效”的單個表查詢方式，卻忽略了表之間驅動的驅動順序（個人猜測）。
　　因此才會造如上推理的類似於“A表和C表之間沒有直接的關係，強制連接”造成的笛卡爾積，
　　根據預估的執行計劃和實際表之間的關聯關係分析得到，這個執行計劃在處理表之間關聯的處理上，正是如此。
　　同時，在強制驅動順序之後，很快地查詢出來了結果，也能說明，用類似於A驅動B，A+B的結果驅動C這種方式的效率遠遠高於A+C計算笛卡爾積再驅動B的

　　Sometimes SQL Server can remove a join predicate from the original query.

　　那麼，如果避免這種情況的呢？
　　已知的是，上述SQL在執行的時候提示沒有連接謂詞，並不是真的沒有寫連接謂詞，
　　而是SQL Server改動了表之間驅動順序，造成了部分沒有直接關係的表放在一起生成笛卡爾積的結果

　　方案一：

　　　　　　OPTION(FORCE ORDER)是也驗證過了，通過強制驅動順序來讓查詢引擎按照順序來實現，

　　方案二：

　　還是上面的例子來說明：
　　比如原始的SQL類似如下：
　　select * from TableA a
　　　　inner join TableB b on a.Identifier1 = b.Identifier1
　　　　inner join TableC c on b.Identifier2 = c.Identifier2
　　where a.Column_X = ***
　　　　and b.Column_Y = ***
　　　　and Other Filter Condition

　　將這個SQL改寫一下
　　　select * from TableA a
　　　　inner join TableB b on a.Identifier1 = b.Identifier1
　　　　CROSS APPLY( select * TableC c where b.Identifier2 = c.Identifier2)
　　where a.Column_X = ***
　　　　and b.Column_Y = ***
　　　　and Other Filter Condition
　　用CROSS APPLY的方式，類似於強制用B表去驅動C表，就不會出現A表和C表結合從而出現笛卡爾積的情況
　　事實也證明瞭，在改寫實際SQL的過程中，這種方式也是切實可行的，效果相當於OPTION(FORCE ORDER)。

　　方案三：同樣是改寫SQL，實際上述的SQL並不是太複雜，但也不是那種很簡單的邏輯關聯，可以通過在一定接住臨時表，拆分出一個中間結果集

　　　　　　用中間結果集的方式去驅動另外的表，簡化每一步的連接邏輯，也可以避免中間產生笛卡爾積的情況
　　　　　　事實證明，這種方式也是可行的，效果稍微亞於前兩種方式，
　　　　　　關於藉助臨時表做邏輯拆分的，也需要一定的技巧，這裡有案例，http://www.cnblogs.com/wy123/p/5712001.html

總結：上述通過一個實際案例，分析了什麼情況下會造成no join predicate，
　　以及即便是寫了連接條件，仍然會出現no join predicate的原因，當面對這種情況的時候，又可以通過什麼辦法來解決。
　　　當從新手開始，不敢在SSMS查詢視窗中寫SELECT（怕超過三個表的就寫不好，被師傅罵），怕寫Update DELETE語句（怕誤操作），
　　　到寫完一個又一個的SQL，慢慢地掌握了一些基礎知識和技巧，再到後面瞭解了索引，執行計劃表，統計信息，會用幾個DMV，幾個系統表，會看幾個性能指標，伺服器資源使用等信息
　　　開始做性能分析，性能優化的時候，我覺得自己已經無所不能了，
　　　現實情況屢屢告訴我，你還有很多很多未知的問題，再一次感覺到自己如此的弱逼。
　　　我承諾，我以後再也不敢吹牛逼了。

參考：http://www.cnblogs.com/liwei225/p/5056460.html

　　 http://www.scarydba.com/2009/09/15/no-join-predicate/

　　 http://dba.stackexchange.com/questions/35082/what-exactly-does-no-join-predicate-mean-in-sql-server

　　 http://www.scarydba.com/2009/09/15/no-join-predicate/

2017，SQL Server中還有很多很多未知的知識等著去學習和挑戰。