昂贵的查询，如何优化

Question

我有三个表(Entered_Triplets、Entered_Quads、Values_Contained)，其中两个表保存历史值。在我的第三个表中，如果来自其他两个表的条目存在于第三个表中，我希望标记一个列。但是，当前的查询需要大约4个小时的时间来处理，我不知道如何优化它。有更好的方法吗？Values_Contained表有超过2200万行。而另外两个表大约有400 K行。

update vc
set vc.TripletsCounted = case when exists(select *
                                          from Entered_Triplets
                                          where TotalCount <> 0
                                            and Number_1 in(vc.Number_1, vc.Number_2, vc.Number_3, vc.Number_4, vc.Number_5)
                                            and Number_2 in(vc.Number_1, vc.Number_2, vc.Number_3, vc.Number_4, vc.Number_5)
                                            and Number_3 in(vc.Number_1, vc.Number_2, vc.Number_3, vc.Number_4, vc.Number_5)) then vc.TripletsCounted + 1
                              else vc.TripletsCounted
                         end
   ,vc.QuadsCounted = case when exists(select *
                                       from Entered_Quads
                                       where TotalCount <> 0
                                       and Number_1 in(vc.Number_1, vc.Number_2, vc.Number_3, vc.Number_4, vc.Number_5)
                                       and Number_2 in(vc.Number_1, vc.Number_2, vc.Number_3, vc.Number_4, vc.Number_5)
                                       and Number_3 in(vc.Number_1, vc.Number_2, vc.Number_3, vc.Number_4, vc.Number_5)
                                       and Number_4 in(vc.Number_1, vc.Number_2, vc.Number_3, vc.Number_4, vc.Number_5)) then vc.QuadsCounted + 1
                           else vc.QuadsCounted
                      end
from Values_Contained vc

Answer 1

这个答案应该可以帮助您缓解当前的性能瓶颈。正如注释中提到的，表结构中反映了一些显著的性能限制设计决策，但我们并不总是这样(从来没有？)有机会纠正我们被迫使用的每一个模式。

这种方法执行两个单独的UPDATES，但每一个操作的行集都比原始代码小得多。这是通过使用INNER JOIN和WHERE子句过滤出不需要更改的行来实现的。因此，您应该观察到查询持续时间的急剧减少。

顺便提一句，你会发现当你问问题的时候，回顾一下https://stackoverflow.com/help/minimal-reproducible-example并结合它的指导会大大提高你的经验。

UPDATE vc
   SET TripletsCounted = vc.TripletsCounted + 1
  FROM Values_Contained      AS vc
 INNER JOIN Entered_Triplets AS et
    ON et.Number_1 IN (vc.Number_1,
                       vc.Number_2,
                       vc.Number_3,
                       vc.Number_4,
                       vc.Number_5)
       AND Number_2 IN (vc.Number_1,
                        vc.Number_2,
                        vc.Number_3,
                        vc.Number_4,
                        vc.Number_5)
       AND Number_3 IN (vc.Number_1,
                        vc.Number_2,
                        vc.Number_3,
                        vc.Number_4,
                        vc.Number_5)
 WHERE et.TotalCount <> 0;

UPDATE vc
   SET TripletsCounted = vc.TripletsCounted + 1
  FROM Values_Contained  AS vc
 INNER JOIN QuadsCounted AS qc
    ON qc.Number_1 IN (vc.Number_1,
                       vc.Number_2,
                       vc.Number_3,
                       vc.Number_4,
                       vc.Number_5)
       AND Number_2 IN (vc.Number_1,
                        vc.Number_2,
                        vc.Number_3,
                        vc.Number_4,
                        vc.Number_5)
       AND Number_3 IN (vc.Number_1,
                        vc.Number_2,
                        vc.Number_3,
                        vc.Number_4,
                        vc.Number_5)
 WHERE qc.TotalCount <> 0;