SQL Server優化技巧——如何避免查詢條件OR引起的性能問題

之前寫過一篇博客“SQL SERVER中關於OR會導致索引掃描或全表掃描的淺析”，裡面介紹了OR可能會引起全表掃描或索引掃描的各種案例，以及如何優化查詢條件中含有OR的SQL語句的幾種方法，其實還有一些方法可以用來優化這種問題，這裡簡單介紹一下。

如下所示，下麵的SQL語句之所有出現這種寫法，是因為程式的查詢界面，可能有多個輸入性的查詢條件，往往用戶只填了一個或部分查詢條件（業務情況，應該不用詳細介紹，大家都能明白），但是程式裡面沒有通過判斷查詢條件生成不同的SQL語句，而是用一個SQL搞定，不管用戶沒有填寫JobNo這個查詢條件，下麵這種寫法：WHERE ISNULL(@JobNo, '') = ''  OR JobNo = @JobNo都能滿足條件，實現邏輯功能。

DECLARE @GenerateDateStart DATETIME ,

    @GenerateDateEnd DATETIME ,

    @JobNo NVARCHAR(200) ,

    @GkNo NVARCHAR(200);

SET @JobNo = 'PT19B030';

SET @GkNo = 'PV19-1-8050'; 

  SELECT    *

  FROM      [dbo].[GEW_UnitConsumption] AS A

            LEFT JOIN dbo.UnitConsumption_Relation AS B ON B.UsableFlag = 'Y'

                                                           AND A.GewUnitConsumptionId = B.RootUnitConsumptionID

  WHERE     ( ISNULL(@JobNo, '') = ''

              OR A.JobNo = @JobNo

            )

            AND ( ISNULL(@GkNo, '') = ''

                  OR A.GkNo = @GkNo

                );

其實，如果根據查詢條件動態生成SQL語句，的確能避免查詢條件中出現OR的情形，但是動態SQL語句沒有上面語句簡單和通熟易懂，尤其是查詢條件較多的情況下。只能說各有利弊。這裡暫且不討論那種策略的優劣。

下麵介紹一種技巧，如何避免OR引起的索引掃描或全表掃描問題。我們可以使用CASE WHEN改寫一下這個SQL語句，就能避免OR引起的執行計劃不走索引查找(Index Seek)的情況，如下所示：

DECLARE @GenerateDateStart DATETIME ,

    @GenerateDateEnd DATETIME ,

    @JobNo NVARCHAR(200) ,

    @GkNo NVARCHAR(200);

SET @JobNo = 'PT19B030';

SET @GkNo = 'PV19-1-8050'; 

SELECT  *

FROM    [dbo].[GEW_UnitConsumption] AS A

        LEFT JOIN dbo.UnitConsumption_Relation AS B ON B.UsableFlag = 'Y'

                                                       AND A.GewUnitConsumptionId = B.RootUnitConsumptionID

WHERE   CASE WHEN ISNULL(@JobNo, '') = '' THEN A.JobNo

             ELSE @JobNo

        END = JobNo

        AND CASE WHEN ISNULL(@GkNo, '') = '' THEN A.GkNo

                 ELSE GkNo

            END = @GkNo;

測試對比發現性能改善非常明顯，當然這種優化技巧也是有局限性的，並不能解決所有OR引起的性能問題（沒有銀彈！）。如下所示，對於下麵這種情況，這種技巧也是無能為力！

SELECT * FROM TEST1 WHERE A=12 OR B=500