内部有递归 CTE 的表值函数非常慢

发布于 2024-09-27 19:10:08 字数 1962 浏览 6 评论 0原文

我创建了一个 TVF，它从递归 CTE 返回一个包含父记录的表此处。效果非常好，并且可以直接获得结果。现在我想获取子记录（它们与当前记录的 PK 具有相同的 FK）。问题是获取给定 id 的 22 条子记录需要 1 分 10 分钟。 为什么与查找父记录的相反 TVF 相比如此慢？

这是 ITVF：

CREATE FUNCTION [dbo].[_nextClaimsByIdData] (
    @idData INT
)

RETURNS TABLE AS
RETURN(
    WITH NextClaims 
    AS(
        SELECT 1 AS relationLevel, child.*
        FROM tabData child
        WHERE child.fiData = @idData

        UNION ALL

        SELECT relationLevel+1, parent.*
        FROM NextClaims nextOne
        INNER JOIN  tabData parent ON parent.fiData = nextOne.idData
    )

    SELECT TOP 100 PERCENT * FROM NextClaims order by relationLevel
)

这是关系：

Datamodel

以下是示例性查询的（正确）结果：

select relationLevel,idData,fiData from dbo._nextClaimsByIdData(30755592);

rl    idData      fiData
1   30073279    30755592
2   30765260    30073279
3   31942491    30765260
4   30895945    31942491
5   48045119    30895945
6   48342321    48045119
7   48342320    48342321
8   48308966    48342320
9   48308965    48308966
10  47044261    48308965
11  47044260    47044261
12  47253273    47044260
13  47253272    47253273
14  47279292    47253272
15  47279293    47279292
15  47494589    47279292
16  47494588    47494589
17  46051999    47494588
18  46373053    46051999
19  46083426    46373053
20  46099567    46083426
21  46600314    46099567
22  46595167    46600314

性能损失的原因可能是在我的第一个 TVF（上面链接）中我正在寻找主键和在此 TVF 中，我正在搜索（自引用）外键？如果是，我如何优化我的表架构以加速查询？

UPDATE: I've found out that the cause for this performance issue was that there was no Index on fiData(the foreignkey-column on the table's primary key). After creating and reorganizing, the result came immediately.

谢谢。

原文

I've cretaed a TVF which returns a table with parent-records from a recursive CTE here.
That works excellent and the result is available directly. Now i wanted to get the child-records(they have the same FK as the current record's PK).
The problem is that it takes 1:10 minutes to get 22 child-records for a given id.
Why is this so slow compared to the opposite TVF which looks for parent-records?

This is the ITVF:

CREATE FUNCTION [dbo].[_nextClaimsByIdData] (
    @idData INT
)

RETURNS TABLE AS
RETURN(
    WITH NextClaims 
    AS(
        SELECT 1 AS relationLevel, child.*
        FROM tabData child
        WHERE child.fiData = @idData

        UNION ALL

        SELECT relationLevel+1, parent.*
        FROM NextClaims nextOne
        INNER JOIN  tabData parent ON parent.fiData = nextOne.idData
    )

    SELECT TOP 100 PERCENT * FROM NextClaims order by relationLevel
)

This is the relationship:

Datamodel

And the following the (correct) result for an exemplary query:

select relationLevel,idData,fiData from dbo._nextClaimsByIdData(30755592);

rl    idData      fiData
1   30073279    30755592
2   30765260    30073279
3   31942491    30765260
4   30895945    31942491
5   48045119    30895945
6   48342321    48045119
7   48342320    48342321
8   48308966    48342320
9   48308965    48308966
10  47044261    48308965
11  47044260    47044261
12  47253273    47044260
13  47253272    47253273
14  47279292    47253272
15  47279293    47279292
15  47494589    47279292
16  47494588    47494589
17  46051999    47494588
18  46373053    46051999
19  46083426    46373053
20  46099567    46083426
21  46600314    46099567
22  46595167    46600314

It it possible that the cause for the performace lost is that in my first TVF(linked above) i'm looking for primary keys and in this TVF i am searching for (self referencing) foreign keys? If yes, how can i optimize my table-schema to accelerate the query?

Thank you.

分享到QQ

分享到微博