为什么CTE计算在查询计划中重复，并且如何在不重复代码的情况下优化它？ [英] Why CTE calculation is duplicated in query plan and how to optimize it without duplicating code?

问题描述

在此查询的查询计划中，grp_set的计算重复了4次（不同的排序每次花费23％，因此花费23 * 4 =所有资源的92％）：

Calculation of grp_set is duplicated 4 times in query plan of this query (distinct sort takes 23% each time, so it takes 23 * 4 = 92% of all resources):

with
     grp_set as (select distinct old_num,old_tbl,old_db,old_val_num from err_calc)
    ,grp as (select id = row_number() over (order by old_num),* from grp_set)

    ,leaf as (select grp.id ,c.* ,sort = convert(varchar(max),old_col) + " - " + severity + " - " + err
        from grp
        join err_calc c on
                            c.old_num   = grp.old_num
                        and c.old_tbl       = grp.old_tbl
                        and c.old_db        = grp.old_db
                        and c.old_val_num   = grp.old_val_num
    )

    select old_num,old_tbl,old_db,old_val_num,conc.*
        from (select sep=",") sep
        cross join grp
        cross apply (select
             old_col    = stuff((select sep + old_col   from leaf where leaf.id = grp.id order by leaf.sort FOR XML PATH("")),1,len(sep),"")
            ,old_val    = stuff((select sep + old_val   from leaf where leaf.id = grp.id order by leaf.sort FOR XML PATH("")),1,len(sep),"")
            ,severity   = stuff((select sep + severity  from leaf where leaf.id = grp.id order by leaf.sort FOR XML PATH("")),1,len(sep),"")
            ,err        = stuff((select sep + err       from leaf where leaf.id = grp.id order by leaf.sort FOR XML PATH("")),1,len(sep),"")
        ) conc

表err_calc包含约35万条记录，并且按old_db，old_tbl，new_tbl，severity，err，old_col，new_col，old_val_num，old_val，old_num，new_num仅具有一个索引

Table err_calc contains about 350K records and it has only one index by old_db,old_tbl,new_tbl,severity,err,old_col,new_col,old_val_num,old_val,old_num,new_num.

此查询的目的是由于SQL中缺少串联聚合，因此每组连接4个字符串字段。

The purpose of this query is to concatenate 4 string fields per group due to lack of concatenation aggregate in SQL.

等效查询和期望查询（如果存在串联聚合或已使用CLR实现） d如果order by可以应用于聚合来源，并且所有分组字段都可以由 grouping引用。* 为：

Equivalent and desired query if concatenation aggregate existed or was implemented with CLR and if order by could be applied to source of aggregation and if all grouping fields could be referenced by grouping.* would be:

select grouping.*
    ,severity   =conc(sep+severity)
    ,err        =conc(sep+err)
    ,old_col    =conc(sep+old_col)
    ,old_val    =conc(sep+old_val)
    from err_calc
    cross join (select sep=',') sep
    group by old_num,old_tbl,old_db,old_val_num
    order by old_col,severity,err

推荐答案

因为它像子查询一样使用，并且多次使用。 cf. 在同一查询中多次调用CTE

Because it is used like a subquery, and used multiple times. cf. Calling CTE multiple times in same query

您应该使用CTE而不是 JOIN 代替 CROSS APPLY 来重写查询，然后将字符串串联逻辑放在查询的 SELECT 部分中，则CTE将被调用一次。

You should rewrite your query with a JOIN with your CTE instead of a CROSS APPLY, and put the logic of string concatenation in the SELECT part of your query, then the CTE will be called once.

这篇关于为什么CTE计算在查询计划中重复，并且如何在不重复代码的情况下优化它？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！