[SQL Server] 案例分析 : 誤用Local Variable造成效能問題

案例情境

我們的資料庫中有個資料表存放有關訂單的相關資訊，訂單的狀態分為三種：Confirmed, Pending, Canceled，其中Confirmed的訂單占了全部的百分之99以上，剩下的少數才是Pending跟Canceled的。
此時，我們另外有個Job會定期去檢視Pending的訂單有那些，客服會查看這些訂單並去處理。
但這個Job隨著訂單越來越多，查詢時間變得非常的久，其中原因是當初誤用了Local variable增加可讀性，反而造成了嚴重的效能問題。

我們先建立測試Table及Procedure資料來模擬問題，我們建立兩個Table : Orders 、OrderStatus，訂單的狀態共有三種 : Confirmed、Pending、Canceled。

IF (EXISTS (SELECT *

    FROM INFORMATION_SCHEMA.TABLES

    WHERE TABLE_SCHEMA = 'dbo'

    AND  TABLE_NAME = 'OrderStatus'))

BEGIN

    DROP TABLE dbo.OrderStatus

END

GO

CREATE TABLE dbo.OrderStatus

(

    OrderStatusID TINYINT NOT NULL,

    OrderStatusName VARCHAR(100) NOT NULL,

    CONSTRAINT  PK_OrderStatus PRIMARY KEY CLUSTERED (OrderStatusID)

)

GO

INSERT INTO dbo.OrderStatus

(

    OrderStatusID,

    OrderStatusName

)

VALUES(1, 'Confirmed'),

      (2, 'Pending'),

      (3, 'Canceled')

GO

IF (EXISTS (SELECT *

    FROM INFORMATION_SCHEMA.TABLES

    WHERE TABLE_SCHEMA = 'dbo'

    AND  TABLE_NAME = 'Orders'))

BEGIN

    DROP TABLE dbo.Orders

END

GO

CREATE TABLE dbo.Orders

(

    OrderID INT IDENTITY(1,1),

    Profit MONEY NOT NULL,

    OrderStatusID TINYINT NOT NULL,

    DateCreated DATETIME2(3) NOT NULL,

    CONSTRAINT PK_Orders PRIMARY KEY (OrderID),

    CONSTRAINT FK_Orders_OrderStatus FOREIGN KEY(OrderStatusID)

    REFERENCES dbo.OrderStatus(OrderStatusID)

)

GO

塞入測試資料，其中Confirmed的訂單佔了99%以上，只有少數的Pending及Canceled的訂單
 

DECLARE @coun INT = 0;

WHILE(@coun < 21)

BEGIN

    IF NOT EXISTS(

        SELECT * FROM dbo.Orders

    )

    BEGIN

        INSERT INTO dbo.Orders

        (

            Profit,

            OrderStatusID,

            DateCreated

        )

        VALUES(100, 1, SYSDATETIME())

    END

    ELSE

    BEGIN

        INSERT INTO dbo.Orders

        (

            Profit,

            OrderStatusID,

            DateCreated

        )

        SELECT Profit,

               OrderStatusID,

               DateCreated

        FROM dbo.Orders

    END

    SET @coun = @coun + 1;

END

SET @coun = 0;

WHILE(@coun < 100)

BEGIN

    INSERT INTO dbo.Orders

    (

        Profit,

        OrderStatusID,

        DateCreated

    )

    VALUES(100, 2, SYSDATETIME())

    SET @coun = @coun + 1;

END

SET @coun = 0;

WHILE(@coun < 100)

BEGIN

    INSERT INTO dbo.Orders

    (

        Profit,

        OrderStatusID,

        DateCreated

    )

    VALUES(100, 3, SYSDATETIME())

    SET @coun = @coun + 1;

END

GO

SELECT OrderStatusName, COUNT(*)

FROM dbo.Orders o

INNER JOIN dbo.OrderStatus os

    ON o.OrderStatusID = os.OrderStatusID

GROUP BY OrderStatusName

接下來我們建立索引在OrderStatusID 的欄位上

CREATE NONCLUSTERED INDEX IX_Orders_OrderStatusID ON dbo.Orders

(

    OrderStatusID

)

GO

建立Stored Procedure，內容中為了增加可讀性另外宣告了一個Local variable　@OrderStatus_Pending, 並用其來當做查詢條件。
 

if object_id('dbo.GetAllPendingOrders', 'p') is null

    exec ('create procedure dbo.GetAllPendingOrders as select 1')

go

ALTER PROCEDURE dbo.GetAllPendingOrders

AS

BEGIN

    SET NOCOUNT ON;

    DECLARE @OrderStatus_Pending tinyint = 2;

    SELECT o.Profit

    FROM dbo.Orders o

    WHERE o.OrderStatusID = @OrderStatus_Pending

END

我們實際執行可以發現，從一百多萬筆資料中找尋100筆資料的語法居然使用了Index Scan，而非Index Seek +Lookup。

SET STATISTICS TIME, IO ON;

EXEC dbo.GetAllPendingOrders

SET STATISTICS TIME, IO OFF;

其Logical Reads及耗用時間為:
 

分析問題

這樣不當的執行計劃，其實是當初為了增加可讀性採用的Local Variable所造成的，我們在[SQL Server] 如何解決不佳的Parameter sniffing所造成的效能問題　這篇文章的解決方案2中有提到，只要使用了Local Variable當做參數，Optimizer會將其視為未知的參數值，並採用中庸的估計方法，如果是等於(=)的運算子，其估計行數為　Total Rows / (Distinct 此 Column組合數)，以此範例來看，其估算查詢的行數就會是

1048776 / 3 = 349592 。

其預估行數與實際行數落差非常多，此時Optimizer就會選擇用Scan的方法來查找近乎整個Table的資料量，而非使用Index Seek + Lookup。

這除了會造成查詢效率差，且會造成大量的block。
 

解決方案
上述的案例有兩個比較大的問題，第一個是誤用了Local variable混淆了Optimizer，第二個是Index並非建立的那麼洽當，以下提供兩個建議。

1. 使用Hardcode並加上註解取代Local variable：

Local Variable適用於你的資料分佈沒有嚴重偏差的時後。

如果有嚴重偏差就會造成上述的問題，除此之外，我們也有在Production環境中遇到查找大資料量卻使用Index Seek的案例。

為了可讀性造成嚴重效能問題得不償失。

我們將Stored Procedure修改後並執行比較差異。 

ALTER PROCEDURE dbo.GetAllPendingOrders

AS

BEGIN

    SET NOCOUNT ON;

    SELECT o.Profit

    FROM dbo.Orders o

    WHERE o.OrderStatusID = 2 --Pending

END 

SET STATISTICS TIME, IO ON;

EXEC dbo.GetAllPendingOrders

SET STATISTICS TIME, IO OFF;

Optimizer編譯出了恰當的執行計畫，選擇了Seek + Lookup，Logical Reads 從3775降低到了318，執行時間也是相差甚遠。

2. 改用Filtered Index將選擇性非常低的值給濾掉：

如果對資料分布非常熟悉，應當建立Filtered Index將選擇性非常低的值給濾掉，避免將大部分的資料給放到Index，造成Index過度肥大，並可能造成誤用Index Seek的可能性，且可稍稍降低Index Seek的成本。

DROP INDEX IX_Orders_OrderStatusID ON dbo.Orders

CREATE NONCLUSTERED INDEX FIX_Orders_OrderStatusID ON dbo.Orders

(

    OrderStatusID

)

WHERE OrderStatusID <> 1

SET STATISTICS TIME, IO ON;

EXEC dbo.GetAllPendingOrders

SET STATISTICS TIME, IO OFF;