很多系統的查詢畫面之中，常常會有許多的非必填的查詢條件，
有輸入值就依那個資料查詢，沒有就怱略那個查詢條件。
針對這樣子的SQL，會造成 SQL Optimizer 無法正確判斷回傳的資料筆數。
很多系統的查詢畫面之中，常常會有許多的非必填的查詢條件，
有輸入值就依那個資料查詢，沒有就怱略那個查詢條件。
在「篩選條件多加 OR 來減少程式中 if 的判斷」這篇是針對程式不使用if。
然而針對這樣子的SQL，會造成 SQL Optimizer 無法正確判斷回傳的資料筆數，
而選擇到不好的執行計畫去執行。
針對這樣子的問題，有許多討論的文章，如下，
Dynamic Search Conditions in T-SQL (Version for SQL 2005 and Earlier)
Dynamic Search Conditions in T-SQL (Version for SQL 2008 (SP1 CU5 and later))
The Tipping Point Query Answers
To be, or not to be
解法就是用 if 或是用 OR + OPTION(RECOMPILE) ，測試如下，
-- drop table TBLA
-- GO
-- Step 1: Create Table
CREATE TABLE TBLA
(
c1 INT,
c2 VARCHAR(30)
);
GO
-- Step 2: Create NoCluster Index
CREATE NONCLUSTERED INDEX NIDX_TBLA_C2
ON dbo.TBLA
(
c2
);
GO
-- Step 3: insert data
DECLARE @I INT;
SET @I = 0;
WHILE @I < 100
BEGIN
SET @I = @I + 1;
INSERT dbo.TBLA( c1, c2 ) VALUES (@I, LTRIM(STR(@I)));
END
GO
-- Step 4: turn on include actual execution plan
-- Step 5: compare 3 query ]
-- Query 1: index seek
DECLARE @c2 VARCHAR(30);
SET @c2 = '66';
SELECT c2
FROM TBLA
WHERE (c2 =@c2);
-- Query 2: index scan
SELECT c2
FROM TBLA
WHERE (c2 = @c2 OR @c2 = '');
-- Query 3: index seek
SELECT c2
FROM TBLA
WHERE (c2 = @c2 OR '66' = '' );
雖然 Query 2 使用 index scan ，因為資料小，所以 I/O 是一樣的，如下，
(1 row(s) affected)
Table 'TBLA'. Scan count 1, logical reads 2, physical reads 0
(1 row(s) affected)
Table 'TBLA'. Scan count 1, logical reads 2, physical reads 0
(1 row(s) affected)
Table 'TBLA'. Scan count 1, logical reads 2, physical reads 0
再加一些資料，然後比較4個Query，如下，
-- Step 6: turn off include actual execution plan
-- Step 7: Insert more data
DECLARE @I INT;
SET @I = 10;
WHILE @I < 3000
BEGIN
SET @I = @I + 1;
INSERT dbo.TBLA( c1, c2 ) VALUES (@I, LTRIM(STR(@I)));
END
GO
-- Step 8: turn on include actual execution plan
-- Step 9: compare 4 query
DECLARE @c2 VARCHAR(30);
SET @c2 = '66';
SELECT c2
FROM TBLA
WHERE (c2 =@c2);
SELECT c2
FROM TBLA
WHERE (c2 = @c2 OR @c2 = '');
SELECT c2
FROM TBLA
WHERE (c2 = @c2 OR '66' = '' );
SELECT c2
FROM TBLA
WHERE (c2 = @c2 OR @c2 = '') OPTION(RECOMPILE);
-- Step 10: turn off include actual execution plan
-- Step 11: SET STATISTICS IO ON
SET STATISTICS IO ON;
-- Step 12: compare 4 query
DECLARE @c2 VARCHAR(30);
SET @c2 = '66';
SELECT c2
FROM TBLA
WHERE (c2 =@c2);
SELECT c2
FROM TBLA
WHERE (c2 = @c2 OR @c2 = '');
SELECT c2
FROM TBLA
WHERE (c2 = @c2 OR '66' = '' );
SELECT c2
FROM TBLA
WHERE (c2 = @c2 OR @c2 = '') OPTION(RECOMPILE);
資料量一大，I/O就有差別了哦! 如下，
(2 row(s) affected) WHERE (c2 =@c2);
Table 'TBLA'. Scan count 1, logical reads 2
(2 row(s) affected) c2 = @c2 OR @c2 = '');
Table 'TBLA'. Scan count 1, logical reads 14
(2 row(s) affected) (c2 = @c2 OR '66' = '' );
Table 'TBLA'. Scan count 1, logical reads 2
(2 row(s) affected) (c2 = @c2 OR @c2 = '') OPTION(RECOMPILE);
Table 'TBLA'. Scan count 1, logical reads 2
以上針對 變數 + OR 的狀況，記錄下來，謝謝大家。
*** 請注意: 在程式維護性與SQL的執行效能之間，就請大家依實際的需求進行選擇。 ***
Hi,
亂馬客Blog已移到了 「亂馬客 : Re:從零開始的軟體開發生活」
請大家繼續支持 ^_^
