帮助提高 SQL Server 2008 CTE 的性能

Question

我有一个 SQL Server 2008 CTE，它负责返回某个位置的热门评论。

CTE 包装在 UDF（表值）中，并连接到 LocationId 字段，因此我可以获得每个位置的最高评论。

基数：

1位置有0-多个帖子位置
1 帖子位置有 1 条帖子
1 帖子有 1 评论

这是 UDF：

CREATE FUNCTION [dbo].[Review_HighestRated_Aggregated_ByLocation]
(   

)
RETURNS TABLE 
AS
RETURN 
(
    WITH [RankedLocations] AS
    (
        SELECT      PL.LocationId, 
                    R.Rating, 
                    P.PostID, 
                    P.UniqueUri, 
                    P.Content, 
                    ROW_NUMBER() OVER (PARTITION BY PL.LocationId ORDER BY R.Rating DESC, P.LocationTypeId, P.CreatedOn DESC) As ScoreRank

        From        dbo.PostLocations As PL
        INNER JOIN  dbo.Posts As P
        ON          P.PostId = PL.PostId
        INNER JOIN  dbo.Reviews As R
        ON          R.PostId = P.PostId

        WHERE       R.ReviewTypeId <> 5
        AND         P.Content IS NOT NULL
    )

    SELECT  LocationId, Rating, PostID, UniqueUri, Content
    FROM    RankedLocations
    WHERE   ScoreRank = 1
)

这是我如何使用它的示例：

select l.LocationId, l.Name, l.UniqueUri, r.UniqueUri, r.Content
from @Locations l -- temp table containing around 18 location ids
inner join dbo.Review_HighestRated_Aggregated_ByLocation() r 
on l.LocationId = r.LocationId

上面的查询需要 15 秒 执行，这是不可接受的。如果没有加入 UDF，则需要 0 秒。

关于如何改进它有什么想法吗？

如果我查看执行计划，SORT 占据了执行成本的 98%。此操作的 IO/子树成本约为 300。

我希望执行计划能给我一个提示，告诉我可以创建一个索引来提高成本，但我什么也没得到。

有什么想法吗？

Answer 1

所以我发现了性能问题，这不是 CTE，而是我使用它的方式。

我有几个查找表，其中一个专门针对位置类型（街道 = 7、城市 = 5 等）。

因此，为了保持 SQL 流畅和一致（并避免硬编码的幻数），我创建了一个包装标量函数，它根据字符串返回等效值，例如：

DECLARE @Street_LocationType = [dbo].[ToLocationTypeId]('Street')

该函数非常简单，只是一系列 CASE 语句。

但是，我是这样使用我的 CTE 的：

SELECT      a.LocationId, b.Content, b.UniqueUri
FROM        [dbo].[Locations] a
INNER JOIN  dbo.Review_HighestRated_Aggregated_ByLocation()  b -- UDF with CTE
ON          a.LocationId = b.LocationId        
WHERE       a.LocationTypeId = @Street_LocationType

所以我什至没有在 CTE 本身上使用它，而是将它用作位置表上的过滤器。

如果我更改上述内容以硬编码该值（例如 7），则过程执行时间从 13 秒降至 2 秒。

我不明白，但它解决了问题。我注意到当过程执行得很差时，查询计划中的“SORT”操作的估计行数 = 32,000 - 这基本上是系统中的每个帖子。

在我的更改之后，估计行数为 1（理应如此）。

确实是奇怪的活动。

Answer 2

如果您的表值函数不需要参数，请考虑使用 VIEW 而不是 UDF。可能它解决了性能问题。

Answer 3

要将 CTE 和 UDF 转换为 VIEW：

DROP FUNCTION [dbo].[Review_HighestRated_Aggregated_ByLocation]
GO

CREATE VIEW Review_HighestRated_Aggregated_ByLocation
AS
SELECT  LocationId, Rating, PostID, UniqueUri, Content
FROM
(
    SELECT      PL.LocationId, 
                R.Rating, 
                P.PostID, 
                P.UniqueUri, 
                P.Content, 
                ROW_NUMBER() OVER (PARTITION BY PL.LocationId ORDER BY R.Rating DESC, P.LocationTypeId, P.CreatedOn DESC) As ScoreRank
    From        dbo.PostLocations As PL
    INNER JOIN  dbo.Posts As P
    ON          P.PostId = PL.PostId
    INNER JOIN  dbo.Reviews As R
    ON          R.PostId = P.PostId
    WHERE       R.ReviewTypeId <> 5
    AND         P.Content IS NOT NULL
) RankedLocations
WHERE   ScoreRank = 1

GO

修改 OP 的示例查询以使用新的 VIEW：

select l.LocationId, l.Name, l.UniqueUri, r.UniqueUri, r.Content
from @Locations l -- temp table containing around 18 location ids
inner join Review_HighestRated_Aggregated_ByLocation r 
on l.LocationId = r.LocationId