【茶包射手日記】不限條件選項之SQL組裝與資料庫OR運算特性驗證
17 | 21,605 |
在設計查詢介面時,下拉式選單是很常見的條件選取方式,而往往我們都會再加上一個"不限條件"選項,允許使用者不限制條件,列出所有項頁。舉例來說: 在設定查詢地區的下拉選單(例如: 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!!! [註]
Comments
# by drem
請教一下,怎麼實驗有沒有使用 index呢?記得使用 OR會造成 SQL Server不使用 index,做 table scan. 1.WHERE (@pArea = '*' OR AreaCol = @pArea) 2.WHERE (AreaCol = case @pArea when '*' then AreaCol else @pArea end) 1.過了 OR之後「好像」會造成 AreaCol欄位就算有建索引也不會使用;想不到怎麼驗證,但有遇過幾次調整 SQL效能從這著手確實有變快。
# by Jeffrey
to drem, 依我的理解,可能形成強迫Table Scan的情境是WHERE SomeFunc(AreaCol) = @pAreaCol這種寫法,理由是它會要求把每筆資料的AreaCol欄位都拿出做SomeFunc運算,以確定是否等於@pAreaCol,因此就不能直接用Index直接定位,效能差很多。上述的寫法多半可以改成WHERE AreaCol = SomeFun(@pAreaCol),效率較佳。關於驗證Table Scan/Index Seek的方法,這裡有一篇參考文章((http://www.dotblogs.com.tw/rainmaker/archive/2010/02/09/13546.aspx)
# by eric
看到流言終結者..我笑了XD
# by jain
我在寫報表時也可以用這查詢語法, 終於避開使用者啥都不選的「冏」境了! (以前還要先給預設值), 謝謝!
# by Ever
您好 我們是巨匠電腦,冒昧打擾還請您見諒。 我們即將再近日成立一個新型態的部落格聯播網站,對您的文章很有興趣,很希望能加入您的部落格,與我們一同建構一個全方位的資訊平台,如您有興趣,請與我聯絡,詳細的細節我們可以再談 謝謝 -------------------------------------------------------------- 巨匠電腦股份有限公司 數位學習事業群/IT 網站組 王植鍵 Ever Wang 100 台北市中正區公園路30號5樓 TEL (02)2388-1213 分機 188 FAX (02)2388-1275 E-Mail:ever.wang@pcschool.com.tw --------------------------------------------------------------
# by mis2000 lab
謝謝您的分享, 又長了見識. Thanks
# by i我們這囧家~
VB是無辜的, 其實VB也有類似C#先決判斷語法:andalso, orelse, 只是寫VB的少有人用而已... 囧rz
# by david
我最近想到另一種寫法... 原: (@pArea = '*' OR AreaCol = @pArea) 新: (AreaCol = isnull(nullif(@pArea,'*'),AreaCol)) 不知道多用了函數會不會造成反效果... 囧rz
# by LEE
黑暗大你好, 我發現使用參數查詢語法和不使用的速度似乎差異很大, 例如: declare @Status int selec 欄位 form 資料表 where Status = @Status GO selec 欄位 form 資料表 where Status = 1 用SET STATISTICS TIME ON和執行計畫顯示, 後者不使用參數的程式成相對應是0%, 且200萬筆資料前者花了3個IO 將近 900ms 後者也是3個IO總只花了9ms,差距非常驚人, 這到底是為什麼呢? 我聽人家說使用傳參數是為了避免SQL injection? 可是速度真的差太多了@@ 這時該怎麼辦呢? 請黑暗大大給我這新手一點指教
# by LEE
忘了說 上面的SELECT 並不是透過VB 或C# 去傳參數的 而且Procedure。 這邊就不知道該怎麼改善。
# by Jeffrey
to LEE, 是否為第一次900ms,第二次查詢9ms? 資料庫查詢有個特性,反覆查詢相同資料時,之後的查詢會因為資料已進入Cache中,不用直接由硬碟讀取而比第一次快。另一種可能是做測試時,DB受到同時執行的其他作業影響而變慢。建議你反覆多做幾次測試,試著排除這類因素。
# by LEE
黑暗大, 我多測試幾次後數字還是差不多的, 我也把執行順序調換過來, 也是依樣, 所以才覺得是因為用參數傳遞條件, 所以SP才會變慢。
# by LEE
另外我用MSSQL的實際執行成本, 也差距非常 使用傳參數的成本是84% 不使用的參數的成本是16%
# by Jeffrey
to LEE, 雖然好像是筆誤,但還是問一下: declare @Status int selec 欄位 form 資料表 where Status = @Status 少了設set @Status = 1,是筆誤嗎?
# by LEE
to 黑暗大, 對,忘了貼set @Status = 1 我測試過很多次 結果都差不多,多超差距很大 我用的是MSSQL2005
# by Jeffrey
to LEE, 依你描述的情境,讓人覺得挺吊詭的,@Status扮演的是常數的角色,理論不該產生效能上的差別,更何況900ms對9ms的差距。不知你有沒有辦法把情境簡化到一推操作步驟可在別台機器重現問題,我倒挺有興趣抓出其中的魔鬼來。
# by LEE
to 黑暗大大, 終於找到相關的文章了, 是Parameter sniffing問題。 http://www.sqlpointers.com/2006/11/parameter-sniffing-stored-procedures.html