SQL Server-聚焦WHERE Column=@Param OR @Param IS NULL有問題？

標籤：語句   blog   尋找   image   type   條件   執行   效能   img   

前言

上一篇我們講完SQL動態查詢，本節我們繼續來講解SQL動態查詢中存在的問題。

SQL動態查詢條件式篩選過濾

當我們建立預存程序調用預存程序時，若篩選條件有值則過濾，沒有值則返回所行記錄，類似如下查詢：

WHERE ([email protected] OR @col IS NULL)

這樣查詢會存在什麼問題呢？效能會不會有問題呢，這個是我們本節需要深入探討的問題。

接下來我們建立如下測試表並插入測試資料，如下：

CREATE TABLE Test    (      SomeCol1 INT NOT NULL ,      Somecol2 INT NOT NULL    ) INSERT  Test        SELECT  number ,                low        FROM    master..spt_values        WHERE   TYPE = 'p'  CREATE INDEX ix_col2 ON Test(Somecol2)GO

對於動態SQL條件式篩選過濾我們利用WHERE 1 = 1來拼接。接下來我們使用一般SQL語句和動態查詢並比較其IO，如下：

SET STATISTICS IO ONGO DECLARE @col INTSELECT @col = 1 SELECT SomeCol2 FROM TestWHERE 1 =1AND  ([email protected] OR @col IS NULL) GO DECLARE @col INTSELECT @col = 1 DECLARE @SQL NVARCHAR(4000)    SET @SQL = 'SELECT SomeCol2                 FROM Test                WHERE 1 =1' IF @col IS NOT NULL     SET @SQL = @SQL + ' AND [email protected] '    EXEC sp_executesql @SQL,N'@InnerParamcol INT',@col SET STATISTICS IO OFFGO

我們能夠看到動態SQL查詢邏輯讀取唯讀取2次，而另外一般SQL語句查詢邏輯讀取7次，同時我們看到SQL動態查詢計劃執行的是索引尋找，而一般SQL語句則是索引掃描。

看來執行一般SQL語句不會走索引尋找，將導致效能問題，在開頭我們就講過篩選條件有值則過濾，無值則查詢所有資料，那麼我們完全可以藉助ISNULL來查詢，下面我們用ISNULL來改變一般語句篩選條件，看看是否會走索引尋找呢？

SET STATISTICS IO ONGO DECLARE @col INTSELECT @col = 1 SELECT SomeCol2FROM dbo.TestWHERE 1 = 1AND  SomeCol2 = ISNULL(@col,SomeCol2)

我們看到結果依然是走索引掃描，沒有任何改變。是不是就沒有解決之道了呢？我們來改變一般SQL語句查詢方式，如下：

DECLARE @col INTSELECT @col = 1IF @Col IS NULL    SELECT  SomeCol2    FROM    Test    WHERE   1 = 1ELSE    SELECT  SomeCol2    FROM    dbo.Test    WHERE   1 = 1            AND SomeCol2 = @colGO

如上只能是勉勉強強解決了問題，因為只是針對一個參數，如果有多個參數要進行IF...ELSE..,那可就傻逼了。從本質上解決這個問題我們需要利用可選項重新編譯。如下：

SET STATISTICS IO ONGO DECLARE @col INTSELECT @col = 1 SELECT SomeCol2 FROM dbo.TestWHERE 1 =1AND  (SomeCol2 = @col OR @col IS NULL)OPTION(RECOMPILE)  GO DECLARE @col INTSELECT @col = 1 DECLARE @SQL NVARCHAR(4000)    SET @SQL = 'SELECT SomeCol2                 FROM dbo.Test                WHERE 1 =1' IF @col IS NOT NULL     SET @SQL = @SQL + ' AND SomeCol2 = @InnerParamcol '            EXEC sp_executesql @SQL,N'@InnerParamcol INT',@col SET STATISTICS IO OFFGO

總結

當利用條件式篩選過濾資料時，如果條件有值則過濾，否則返回所有行記錄。如果執行一般SQL語句和動態SQL，那麼動態SQL會走索引尋找，而一般SQL語句將導致索引掃描，此時需要加上OPTION(RECOMPILE)才走索引尋找。

SQL Server-聚焦WHERE [email protected] OR @Param IS NULL有問題？