【茶包射手日記】不限條件選項之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!!! [註]