SQL幾種物理查詢邏輯

最近在查一個案例，使用者反應在查一個員工數比較少的請假資料的時候會出現錯誤畫面，查員工數較多的公司別的時候反而很快的就查詢出來了，當下一開始的直覺可能是資料面有問題，因此打開程式執行後發現該錯誤為ASP.NET畫面查詢SQL時等待過久timeout所導致，但這問題就來了，為何資料比較少查詢反而會timeout，資料較多的反而不會。

跟同事一起討論後大家一開始都覺的是Index的關係，因此就開始往錯誤的Index去查，結果查出了大學問(小弟我SQL方面的功力很破請見諒)。原來SQL Server在處理Join的時後，會跟據Join table的資料量、索引等…來決定使用不同的實體處理模式，SQL Server本身共有三種物理連接方式，【巢狀迴圈(Nested Loops)】、【合併聯結(Merge Join) 】、【雜湊比對(Hash Match)】。下面先簡單的說明三種連結的處理原則。

先說明一下範例的資料結構
1. 員工資料表bs_employee，欄位共有Company_ID，Emp_No
2. 請假資料表lv_leave，欄位共有Company_ID，Emp_No，Salary_YYMM
3. 公司別共有 A01、A99兩家代碼，A01的員工數為23033，A99的員工數為31筆，集團全員工數為27195。
4. 201401的請假資料筆數A01的筆數為2400，A99的筆數為36，全部請假資料共計564967筆。

巢狀迴圈(Nested Loops)
a. 邏輯 :
對每個頂端(外部)輸入中的資料列，掃描底端(內部)輸入，並輸出符合的資料列。
b. 選擇情境 :
巢狀迴圈是最常見的連接方式，此方式必須為內部有索引，且外部循環行數小於內部循環，否則SQL Server可能會選擇雜湊的模式進行，一般在資料量越來越大的情況下SQL Server可能會使用此方式來進行。

合併聯結(Merge Join)
a. 邏輯 :
跟據其排序次序，真對兩個經過排序之輸入資料表的資料進行比對。
b. 選擇情境 :
合併比對適用再join的兩個資料表都有建立索引的條件下進行，因此SQL Server在判斷Join 連接上若判斷到兩個索引條件相同時可能會選擇Merge Join，理論上此效率會較高，但若有些特殊條件下雜湊排序可能會有更佳的效率。
c. 實際操作 :

SET STATISTICS PROFILE ON
select a.Emp_No
from bs_employee a
left join lv_leave b on a.Emp_No=b.Emp_No

雜湊比對(Hash Match)
a. 邏輯 :
使用頂端輸入的每一個資料列來建立雜湊資料表，並使用底端輸入的每一個資料列來探查雜湊資料表，然後輸入所有符合的資料列。
b. 選擇情境 :
雜湊比對適用在大量數據，再無設定任何Index的情況下效能均優於以上兩種連接方式，因此SQL Server在選擇沒有任何索引的情況下可能會選擇Hash Match，Hash的索引建立方式會找較少的資料表建立索引值，在用該索引值去探查較多資料的資料表。
c. 實際操作 :
刪除任何索引，再無任何索引的情況下直接Join兩張資料表可看出，SQL Server預設果然選取了Hash Join的模式，但此模式耗用的CPU成本可能較高的。

SET STATISTICS PROFILE ON
select a.Emp_No
from bs_employee a
left join lv_leave b on a.Emp_No=b.Emp_No 

當然還有一種物理掃模式是遠端，但此這裡並不探討遠端的時候，故有興趣可自行去MSDN查詢。

以上三種物理筆對方式並沒有絕對哪個是較佳的，而是需視需求決定使用的方式，由這次的經驗中學到，SQL不是只要能跑就行了，Join的方向，索引的建立，適時的強制使用物理搜尋模式都是很重要的，隨意不考慮資料量的情況下Join語法等情況，很容易造成資料量龐大時，查詢效能的崩潰，且錯誤的索引建立可能導致SQL再尋找物理搜尋邏輯時的錯亂，不正確的資料型態查詢，如varchar型態使where nvarchar型態都可能造成不必要的轉型，這些都是我先前不曾深入理解的，實在需要檢討檢討。~”~


浅谈SQL Server中的三种物理连接操作
http://msdn.microsoft.com/zh-cn/library/dn144699.aspx

SQLServer查询性能问题调优案例
http://www.2cto.com/database/201401/272606.html

带您理解SQLSERVER是如何执行一个查询的
http://www.cnblogs.com/lyhabc/archive/2013/10/25/3367274.html

[查詢優化]影響執行計畫的因素1-Predicates(述詞)
http://adalf0722.blogspot.tw/2011/11/1-predicates.html