星期五, 5月 10, 2013

[SQL] WHERE 中的動態篩選條件

T-SQL 撰寫中看見動態篩選的 WHERE 條件,是利用 COALESCE()、ISNULL()、IIF() 或 CASE WHEN 來作到,主要是為了減少 T-SQL 的撰寫或是要避免根據使用者輸入條件來串出 T-SQL,此作法可以獲得相同結果,但最大影響在於效能。
  • 利用 AdventureWorks2012 的 Sales.SalesOrderHeader 和 Sales.SalesOrderDetail 來說明看見的情況
CREATE PROCEDURE uspDemo
(
  @訂單時間 AS datetime , 
  @顧客編號 AS int ,
  @產品編號 AS int
)
AS
  BEGIN

    SELECT
      OH.SalesOrderID ,
      OH.CustomerID ,
      OD.ProductID
    FROM [Sales].[SalesOrderHeader] AS OH
      JOIN [Sales].[SalesOrderDetail] AS OD ON OH.SalesOrderID = OD.SalesOrderID
    WHERE OH.OrderDate = ISNULL(@訂單時間 , OH.OrderDate)
      AND OH.CustomerID = ISNULL(@顧客編號 , OH.CustomerID) -- 動態篩選條件
      AND OD.ProductID = ISNULL(@產品編號 , OD.ProductID)

  END
動態篩選條件是利用 ISNULL() 來判斷 @顧客編號 的值,值為 NULL,WHERE 條件就會變成 OH.CustomerID = OH.CustomerID 也就是 1 = 1 的條件,永遠成立,此篩選條件就沒有作用,利用此特性 Store Procedure 就可以根據使用者輸入的條件,來控制 WHERE 的篩選條件。

  • 利用 AdventureWorks2012 的 Person.Contact 來觀察效能差異
建立 uspNormal 和 uspDynamic 這兩個 SP,內容都是要找出 ContactID 是哪位,uspNormal 是正常 WHERE 條件、uspDynamic 則是動態 WHERE 條件
IF OBJECT_ID('uspNormal') IS NOT NULL
  DROP PROC uspNormal

IF OBJECT_ID('uspDynamic') IS NOT NULL
  DROP PROC uspDynamic
GO 

CREATE PROCEDURE uspNormal
(
  @ContacID int
)
AS
  BEGIN

    SELECT ContactID , FirstName , MiddleName , LastName
    FROM [Person].[Contact]
    WHERE ContactID = @ContacID

  END
GO

CREATE PROCEDURE uspDynamic
(
  @ContacID int
)
AS
  BEGIN

    SELECT ContactID , FirstName , MiddleName , LastName
    FROM [Person].[Contact]
    WHERE ContactID = ISNULL(@ContacID,ContactID) 
                                                    
  END
GO
執行 uspNormal 和 uspDynamic,要找到 ContacID 為 10 的資料
-- Ctrl + M 開啟執行計畫
SET NOCOUNT ON       -- 避免回傳影響資料筆數
SET STATISTICS IO ON -- 開啟觀察 Logical Read

EXEC uspNormal 10
EXEC uspDynamic 10

  • 一樣的結果
[SQL] WHERE 中的動態篩選條件-3

  • uspNormal Logical Read 為 2、uspDynamic Logical Read 為 569
[SQL] WHERE 中的動態篩選條件-2

  • 從執行計畫中可以看到 uspNormal 利用 Index Seek、uspDynamic 利用 Index Scan 來篩選資料。
[SQL] WHERE 中的動態篩選條件-1

上述的簡單範例,就可以看出 WHERE 中的動態篩選條件會導致 Query Optimizer 無法正確地利用 Index 來搜尋資料,建議不要在 WHERE 中使用動態篩選條件,以免導致效能低落。

3 則留言:

  1. 如果條件式裡面放太多的判斷,執行速度會變很慢唷

    回覆刪除
  2. To Sheng-Po Tseng ~~
    效能問題,還是要觀察執行計畫最容易找出癥結點喔。^_^ ~~

    回覆刪除
  3. 我在一百個欄位的 Table 搜尋過,大約有三十個的 Case When,最後直接先判斷是否有直在組出 SQL 快超多的,大約是十秒跟一秒的差別

    回覆刪除