提高现有更新性能

Question

下面的查询将永远运行。有人能告诉我如何提高它的性能吗？

    Query:  update fsa_ip_mth_end_fin_aggregate tbl2
    set (team_acip_gss_sls_cry_am,team_sr_gss_sls_cry_am)  =         
         (select acip_gss_sls_cry_am , sr_gss_sls_cry_am
         from (select agg.current_fsa_ip_id current_fsa_ip_id, 
                       sum(base1.sr_gss_sls_cry_am) sr_gss_sls_cry_am, 
                       sum(base1.acip_gss_sls_cry_am) acip_gss_sls_cry_am  
                from ip_dim base,
                     ip_dim member,
                     ip_team_hierarchy_brdg,
                     fsa_ip_mth_end_fin_aggregate base1,
                     fsa_ip_mth_end_fin_aggregate agg 
                where ip_team_hierarchy_brdg.base_ip_dim_id = base.ip_dim_id 
                and ip_team_hierarchy_brdg.member_ip_dim_id = member.ip_dim_id 
                and ip_team_hierarchy_brdg.active_in = 'Y' 
                and base.dim_active_in = 'Y'
                and member.dim_active_in = 'Y' 
                and base1.current_fsa_ip_id = base.current_fsa_ip_id 
                and agg.current_fsa_ip_id = member.current_fsa_ip_id 
                and ip_team_hierarchy_brdg.allocation_factor != 0 
                and agg.current_fsa_ip_id = base1.current_fsa_ip_id
                group by agg.current_fsa_ip_id) tbl1
            where tbl1.current_fsa_ip_id = tbl2.current_fsa_ip_id)               
where exists                  
(select sr_gss_sls_cry_am,acip_gss_sls_cry_am 
         from (select agg.current_fsa_ip_id current_fsa_ip_id, 
                       sum(base1.sr_gss_sls_cry_am) sr_gss_sls_cry_am, 
                       sum(base1.acip_gss_sls_cry_am) acip_gss_sls_cry_am  
                from ip_dim base,
                     ip_dim member,
                     ip_team_hierarchy_brdg,
                     fsa_ip_mth_end_fin_aggregate base1,
                     fsa_ip_mth_end_fin_aggregate agg 
                where ip_team_hierarchy_brdg.base_ip_dim_id = base.ip_dim_id 
                and ip_team_hierarchy_brdg.member_ip_dim_id = member.ip_dim_id 
                and ip_team_hierarchy_brdg.active_in = 'Y' 
                and base.dim_active_in = 'Y'
                and member.dim_active_in = 'Y' 
                and base1.current_fsa_ip_id = base.current_fsa_ip_id 
                and agg.current_fsa_ip_id = member.current_fsa_ip_id 
                and ip_team_hierarchy_brdg.allocation_factor != 0 
                and agg.current_fsa_ip_id = base1.current_fsa_ip_id
                group by agg.current_fsa_ip_id) tbl1
            where tbl1.current_fsa_ip_id = tbl2.current_fsa_ip_id);    

我尝试了以下语法：

    update (select query...) 
    set (team_acip_gss_sls_cry_am,team_sr_gss_sls_cry_am) = (select query...) 

但由于涉及group by/aggregate函数，这不起作用。

我还尝试了“IN”而不是EXISTS。两者的性能几乎相同。请在这方面给我指点。

Answer 1

我将不得不花更多的时间分析您的查询，但是看起来您的子查询中有未使用的表。查询优化器应该省略它们，但是如果没有省略它们，那么您就会得到一个交叉连接和大量的行数膨胀。

更新:缺少的连接条件太多了，我甚至不知道这个查询想要达到什么目的。"ip_dim成员“与任何其他表之间的关系在哪里？"ip_team_hierarchy_brdg“和"ip_dim base”与"fsa_ip_mth_end_fin_aggregate agg“和"fsa_ip_mth_end_fin_aggregate base1”有什么关系？fsa_ip_mth_end_fin_aggregate上的self join的用途是什么？如果记录之间存在层次关系，则不应该在两端的同一字段上进行连接。

更新2:现在你已经给了我们整个查询，至少看起来没有任何交叉连接。除了其他人提供的关于确保您有适当索引的建议之外，我对重复的子查询持怀疑态度。你的第二次尝试是在正确的轨道上。可以将子查询移动到UPDATE的表引用中，但您需要将正在更新的表与聚合分开。我重组了您的查询以使用ANSI连接，以便更容易地进行故障排除，并移动子查询：

update fsa_ip_mth_end_fin_aggregate tbl2
join (select agg.current_fsa_ip_id current_fsa_ip_id, 
             sum(base1.sr_gss_sls_cry_am) sr_gss_sls_cry_am, 
             sum(base1.acip_gss_sls_cry_am) acip_gss_sls_cry_am  
        from ip_dim base
        join ip_team_hierarchy_brdg on ip_team_hierarchy_brdg.base_ip_dim_id = base.ip_dim_id 
        join ip_dim member on ip_team_hierarchy_brdg.member_ip_dim_id = member.ip_dim_id 
        join fsa_ip_mth_end_fin_aggregate base1 on base1.current_fsa_ip_id = base.current_fsa_ip_id 
        join fsa_ip_mth_end_fin_aggregate agg on agg.current_fsa_ip_id = member.current_fsa_ip_id 
            and agg.current_fsa_ip_id = base1.current_fsa_ip_id
        where ip_team_hierarchy_brdg.active_in = 'Y' 
        and base.dim_active_in = 'Y'
        and member.dim_active_in = 'Y' 
        and ip_team_hierarchy_brdg.allocation_factor != 0 
        group by agg.current_fsa_ip_id
   ) tbl1
   on tbl1.current_fsa_ip_id = tbl2.current_fsa_ip_id
set tbl2.team_acip_gss_sls_cry_am = tbl1.acip_gss_sls_cry_am,
    tbl2.team_sr_gss_sls_cry_am = tbl1.sr_gss_sls_cry_am            

这是MySQL语法，我刚刚意识到你没有指定你的数据库管理系统。SQL Server对多表UPDATE的语法稍有不同，其他的我不太确定。

如果我错了，重复的子查询不是罪魁祸首，那么这个查询结构至少可以更容易地优化聚合子查询。