Sql-Server

加入兩個內聯函式,在過濾第二個函式時會顯著減慢

  • May 8, 2018

我有兩個單獨執行足夠快的內聯函式。

當我離開時,像這樣加入他們:

select *
from
Function1(-1) F1 
left join
Function2(-1) F2 on F1.key = F2.key

…我在大約 14 秒內得到我的結果集。非常適合它正在做的事情。

但是,如果我添加 where 條件:

Where 
F2.Boolean = 0

…然後需要 8 多分鐘才能完成。

有些結果沒有 F2 記錄,因此這些結果為空。

我做了一個測試isnull(f2.boolean,0) = 0,它在 10 秒內執行,如果一個地方 a 或 F2.boolean 為空,它執行得很快。

我使用的是 SQL Server 2008 R2,並在 SP2 和 SP3 上進行了測試。有沒有人對看什麼有想法或建議?

這些函式是連接多個表的簡單選擇語句。他們沒有什麼特別的。每個單獨的函式在 10 秒內返回整個表的結果。

當我添加“F2 中的日期欄位之一在兩個日期之間的位置”時,它執行得很快。我還剛剛在 where 子句中的varchar(2)欄位上進行了測試,這也導致它非常慢(但不是 datetime 欄位)。好的,為什麼第二個函式的欄位或varchar上的過濾器會導致速度下降,而日期時間之間呢?另一個觀察到的行為:如果我在 F1 上添加一個過濾器,首先指定實際上有 F2 記錄的過濾器,然後 F2 上的原始過濾器再次快速執行。

解決方法建議:

如果您可以更改函式,請嘗試使它們成為多語句表值函式而不是內聯函式。這樣做時,您在RETURNS零件中定義一個輸出表。在輸出表的 DDL 中,聲明一個好的聚群主鍵。

例子:

CREATE FUNCTION dbo.fn_test1(@i int)
RETURNS @out TABLE (
   a    char(5) NOT NULL,
   b    date NOT NULL,
   c    int NOT NULL,
   PRIMARY KEY CLUSTERED (a, b)
)
AS

BEGIN;
   INSERT INTO @out (a, b, c)
   SELECT x, y, z
   FROM dbo.someTable
   WHERE someCriteria>@i;

   RETURN;
END;

儘管將內聯函式替換為多語句函式有其自身的優點和缺點,但在您的情況下的一個重要區別可能是在連接發生之前,輸出儲存在具有聚集索引的臨時表中。如果您已正確對齊這兩個函式的聚集索引,則加入它們的輸出表應該是一件很快的事情。

這是您可以期待什麼樣的查詢計劃的一個想法(顯然不包括函式的內部工作原理)。

範例查詢計劃

大多數情況下,這種情況來自“布爾”欄位的統計數據。請記住,統計數據可能來自索引,可以自動或手動創建。確保此欄位的統計資訊已更新。在 2014 年以下的 SQL(基數估計器低於 120)中,統計資訊中的值估計為 0,並且該值可能超出統計資訊,因為未更新統計資訊或更新樣本未獲取查詢中過濾值的行。您可以通過使用變數作為該列的值並使用 OPTION (OPTIMIZE FOR(@ariable UNKNOWN)) 來解決此問題。這樣就不會考慮統計資訊,因為這將不再是執行計劃的參數。

引用自:https://dba.stackexchange.com/questions/95481