問題描述

我有一個(gè)表值的內(nèi)聯(lián) UDF.我想過濾該 UDF 的結(jié)果以獲得一個(gè)特定值.當(dāng)我使用常量參數(shù)指定過濾器時(shí)，一切都很好，性能幾乎是瞬時(shí)的.當(dāng)我使用可變參數(shù)指定過濾器時(shí)，它花費(fèi)的時(shí)間要長(zhǎng)得多，大約是 500 倍的邏輯讀取和 20 倍的持續(xù)時(shí)間.

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.

執(zhí)行計(jì)劃表明，在可變參數(shù)的情況下，過濾器直到進(jìn)程的很晚才應(yīng)用，導(dǎo)致多次索引掃描而不是在常量情況下執(zhí)行的搜索.

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.

我想我的問題是:為什么，因?yàn)槲抑付艘粋€(gè)對(duì)索引字段具有高度選擇性的過濾器參數(shù)，當(dāng)該參數(shù)位于變量中時(shí)，我的性能是否會(huì)陷入困境?我能對(duì)此做些什么嗎?

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?

和查詢中的解析函數(shù)有關(guān)系嗎?

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

推薦答案

我得到的答復(fù)很好，我從中學(xué)到了東西，但我想我已經(jīng)找到了讓我滿意的答案.

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

我確實(shí)認(rèn)為是使用 PARTITION BY 子句導(dǎo)致了這里的問題.我使用自連接習(xí)語(yǔ)的變體重新制定了 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 的查詢性能更高，而且優(yōu)化器在使用變量而不是常量連接此版本的查詢時(shí)沒有問題.在這一點(diǎn)上，我得出的結(jié)論是優(yōu)化器不能處理更新的 SQL 擴(kuò)展以及舊的東西.作為獎(jiǎng)勵(lì)，我現(xiàn)在可以在預(yù)升級(jí)的 SQL 2000 平臺(tái)中使用 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.

感謝大家的幫助！

這篇關(guān)于為什么當(dāng)我使用可變參數(shù)而不是常量參數(shù)時(shí)，我的內(nèi)聯(lián)表 UDF 會(huì)慢很多?的文章就介紹到這了，希望我們推薦的答案對(duì)大家有所幫助，也希望大家多多支持html5模板網(wǎng)！