問題描述

我有一個表值的內聯 UDF.我想過濾該 UDF 的結果以獲得一個特定值.當我使用常量參數指定過濾器時，一切都很好，性能幾乎是瞬時的.當我使用可變參數指定過濾器時，它花費的時間要長得多，大約是 500 倍的邏輯讀取和 20 倍的持續時間.

I have a table-valued, inline UDF. I want to filter the results of that UDF to get one particular value. When I specify the filter using a constant parameter, everything is great and performance is almost instantaneous. When I specify the filter using a variable parameter, it takes a significantly larger chunk of time, on the order of 500x more logical reads and 20x greater duration.

執行計劃表明，在可變參數的情況下，過濾器直到進程的很晚才應用，導致多次索引掃描而不是在常量情況下執行的搜索.

The execution plan shows that in the variable parameter case the filter is not applied until very late in the process, causing multiple index scans rather than the seeks that are performed in the constant case.

我想我的問題是:為什么，因為我指定了一個對索引字段具有高度選擇性的過濾器參數，當該參數位于變量中時，我的性能是否會陷入困境?我能對此做些什么嗎?

I guess my questions are: Why, since I'm specifying a single filter parameter that is going to be highly selective against an indexed field, does my performance go into the weeds when that parameter is in a variable? Is there anything I can do about this?

和查詢中的解析函數有關系嗎?

Does it have something to do with the analytic function in the query?

這是我的疑問:

CREATE FUNCTION fn_test()
RETURNS TABLE
WITH SCHEMABINDING
AS
    RETURN
    SELECT DISTINCT GCN_SEQNO, Drug_package_version_ID
    FROM
    (
        SELECT COALESCE(ndctbla.GCN_SEQNO, ndctblb.GCN_SEQNO) AS GCN_SEQNO,
            dpv.Drug_package_version_ID, ROW_NUMBER() OVER (PARTITION BY dpv.Drug_package_version_id ORDER BY 
                ndctbla.GCN_SEQNO DESC) AS Predicate
        FROM dbo.Drug_Package_Version dpv
            LEFT JOIN dbo.NDC ndctbla ON ndctbla.NDC = dpv.Sp_package_code
            LEFT JOIN dbo.NDC ndctblb ON ndctblb.SPC_NDC = dpv.Sp_package_code
    ) iq
    WHERE Predicate = 1
GO

GRANT SELECT ON fn_test TO public
GO

-- very fast
SELECT GCN_SEQNO
FROM dbo.fn_test()
WHERE Drug_package_version_id = 10000

GO

-- comparatively slow
DECLARE @dpvid int
SET @dpvid = 10000
SELECT GCN_SEQNO
FROM dbo.fn_test()
WHERE Drug_package_version_id = @dpvid

推薦答案

我得到的答復很好，我從中學到了東西，但我想我已經找到了讓我滿意的答案.

The responses I got were good, and I learned from them, but I think I've found an answer that satisfies me.

我確實認為是使用 PARTITION BY 子句導致了這里的問題.我使用自連接習語的變體重新制定了 UDF:

I do think it's the use of the PARTITION BY clause that is causing the problem here. I reformulated the UDF using a variant of the self-join idiom:

SELECT t1.A, t1.B, t1.C
FROM T t1
    INNER JOIN
    (
        SELECT A, MAX(C) AS C
        FROM T
        GROUP BY A
    ) t2 ON t1.A = t2.A AND t1.C = t2.C

具有諷刺意味的是，這比使用特定于 SQL 2008 的查詢性能更高，而且優化器在使用變量而不是常量連接此版本的查詢時沒有問題.在這一點上，我得出的結論是優化器不能處理更新的 SQL 擴展以及舊的東西.作為獎勵，我現在可以在預升級的 SQL 2000 平臺中使用 UDF.

Ironically, this is more performant than using the SQL 2008-specific query, and also the optimizer doesn't have a problem with joining this version of the query using variables rather than constants. At this point, I'm concluding that the optimizer just doesn't handle the more recent SQL extensions as well as the older stuff. As a bonus, I can make use of the UDF now, in my pre-upgraded SQL 2000 platforms.

感謝大家的幫助！

這篇關于為什么當我使用可變參數而不是常量參數時，我的內聯表 UDF 會慢很多?的文章就介紹到這了，希望我們推薦的答案對大家有所幫助，也希望大家多多支持html5模板網！