时间戳

首页 > 解决方案 > 降低表值函数的成本 - 查询计划中的 XML 阅读器 - 如何？

sql - 降低表值函数的成本 - 查询计划中的 XML 阅读器 - 如何？

问题描述

我有以下 SQL Server 查询：

with cte as 
(SELECT DISTINCT refrecid
FROM         docuref
WHERE     ACTUALCOMPANYID = 'an' and REFTABLEID='78' and typeid='Note')
SELECT     docuref.REFRECID,  Notes = STUFF
                            ((SELECT     CHAR(13) + CHAR(10) + cast([NOTES] AS nvarchar(max))
                                FROM         DOCUREF
                                WHERE     REFRECID = Cte.refrecid AND ACTUALCOMPANYID = 'an' FOR XML PATH(''), TYPE ).value('.', 'nvarchar(max)'), 1, 2, '')
 FROM         Cte INNER JOIN
                        DOCUREF ON cte.REFRECID= docuref.REFRECID
 WHERE     DOCUREF.ACTUALCOMPANYID = 'an' and docuref.REFTABLEID='78' and docuref.typeid='Note'
 GROUP BY docuref.REFRECID,cte.refrecid

Docuref 表包含大约 40,000 行。我正在尝试将 Notes 列合并到一个 RefrecID 相同的记录中，

例如，如果我有以下内容：

Refrecid    Recid     Notes
1000        2000      Notes1
1000        2001      Notes2
1000        2002      Notes3

我最终会得到：

Refrecid    Notes
1000        Notes1
            Notes2
            Notes3

但是，这个查询运行大约需要 2 分钟，所以需要减少很多，所以只需要几秒钟。我查看了实际执行计划，成本最高的项目是“表值函数 - XML 阅读器”，成本为 91%。实际执行计划见下图：

https://www.brentozar.com/pastetheplan/?id=ByJMhcb7B

有没有更好的方法来做我正在做的事情？

编辑：因此，根据@Shnugo 的评论，我使用了一个临时表进行查询，查询运行时间从 2 分钟下降到 3 秒。我现在使用的查询是：

IF OBJECT_ID('tempdb..#TempTable') Is Not null
Drop Table #TempTable
;
WITH grouped AS
(
    SELECT  dr.refrecid AS REFRECID
    FROM    docuref dr
    WHERE   dr.ACTUALCOMPANYID = 'ansa' 
        and dr.REFTABLEID='78' 
        and dr.typeid='Note'
    GROUP BY dr.refrecid
)

select * into #TempTable from grouped

SELECT gr.REFRECID
    ,STUFF(
    (
        SELECT CHAR(13) + CHAR(10) + cast(dr2.[NOTES] AS nvarchar(max))
        FROM DOCUREF dr2
        WHERE dr2.REFRECID = gr.refrecid 
            AND dr2.ACTUALCOMPANYID = 'ansa' 
        FOR XML PATH(''), TYPE 
    ).value('.', 'nvarchar(max)'), 1, 2, '') AS Notes
FROM #TempTable gr;

标签： sqlsql-servertsqlsql-server-2012

解决方案

为节点路径指定一个单例文本节点( (./text())[1])。这将有助于优化查询计划中的 XML 表值函数，这可以为更大的结果提供显着的改进。

with cte as 
(SELECT DISTINCT refrecid
FROM         docuref
WHERE     ACTUALCOMPANYID = 'an' and REFTABLEID='78' and typeid='Note')
SELECT     docuref.REFRECID,  Notes = STUFF
                            ((SELECT     CHAR(13) + CHAR(10) + cast([NOTES] AS nvarchar(max))
                                FROM         DOCUREF
                                WHERE     REFRECID = Cte.refrecid AND ACTUALCOMPANYID = 'an' FOR XML PATH(''), TYPE ).value('(./text())[1]', 'nvarchar(max)'), 1, 2, '')
 FROM         Cte INNER JOIN
                        DOCUREF ON cte.REFRECID= docuref.REFRECID
 WHERE     DOCUREF.ACTUALCOMPANYID = 'an' and docuref.REFTABLEID='78' and docuref.typeid='Note'
 GROUP BY docuref.REFRECID,cte.refrecid

推荐阅读