加入兩個內聯函式,在過濾第二個函式時會顯著減慢
我有兩個單獨執行足夠快的內聯函式。
當我離開時,像這樣加入他們:
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)) 來解決此問題。這樣就不會考慮統計資訊,因為這將不再是執行計劃的參數。