【茶包射手日記】不限條件選項之SQL組裝與資料庫OR運算特性驗證

在設計查詢介面時，下拉式選單是很常見的條件選取方式，而往往我們都會再加上一個"不限條件"選項，允許使用者不限制條件，列出所有項頁。舉例來說: 在設定查詢地區的下拉選單(例如: ddlArea)，除了列出基隆市、台北市、台北縣、桃園縣...等各縣市Option外，常會再多一個"所有地區"選項，將Option Value設定='*'，選取時表示不限定地區。在Server端，便要將這些篩選條件轉為SQL指令，如何將'*'視為不限地區? 我過去都用條件式動態組裝:

if (ddlArea.Text != "*") {
    cmd.CommandText += " AND AreaCol = @pArea";
    cmd.Parameters.Add("@pArea", SqlDbType.NVarChar).Value = ddlArea.Text;
}

後來有一天開了竅，想到更簡潔的寫法:

cmd.CommandText = @"
SELECT .... FROM ...
WHER ...其他查詢條件...
AND (@pArea = '*' OR AreaCol = @pArea)
....";
cmd.Parameters.Add("@pArea", SqlDbType.NVarChar).Value = ddlArea.Text;

如此，整個查詢條件可以直接寫成靜態字串，便於閱讀修改，不必視狀況決定是否要傳入@pArea，而它一樣可達到傳入"*"就不設地區比對條件的效果。

今天在噗浪上與噗友討論到類似議題，看到一種新寫法: where p1 = case @p1 when '*' then p1 else @p1 end，噗友提到曾經聽某資料庫老師說過，這種寫法比上述OR法效能更好，理由是在SQL指令中，OR 的兩邊條件都會做運算比較，跟一般程式的認知不同。(不過噗友對此理論也抱持懷疑態度就是了)

基本上，我無法接受SQL指令中OR兩邊都會執行的主張。當今各家DB在效能優化上不遺餘力，每一個環節錙銖必較，怎麼可能留下這麼個大洞害人摔跤? 不過要怎麼驗證它倒是挺傷腦筋的。

想到一個好辦法，用Block機制來突顯差別!! 故意在OR後方條件式中SELECT一個被Lock住的Table，接著故意讓OR前方條件為True，若SQL傻不楞登硬要執行OR後方條件式就會被擋下來，直到Lock被釋放為止(ROLLBACK TRAN/COMMIT TRAN)；若SQL如我想的冰雪聰明，就會直接忽略OR後方條件立刻送回結果。由結果出現時機，便可判定OR後方的條件式是否被執行。

實驗結果如下: (SQL 2008 @ Windows 2008 x64)

在最下方的SQL指令區故意INSERT卻不COMMIT讓T2被Lock住，此時OR前方條件為1=1的SQL指令不受影響，OR前方為1=0的SQL指令才會執行到第二條件SELECT COUNT(ID) FROM T2而被Block住。由此可證: SQL Sever並不會像VB6一樣傻傻地檢查全部的AND/OR條件式，後方條件式執行與否會視前面的結果而定，故前述的OR法大家可安心服用。(這裡有另一篇討論也持相同看法: The query engine will take care of this for you. All operators will "short circuit" if they can.)

MYTH BUSTED!!! [註]