SQL Server查询运行速度非常慢

Question

我通过SQL Server Management Studio执行的以下查询非常慢。

输入表tbl_sb12_bhs大约有40000条记录，一小时后只处理了40条记录。

这里可以进行哪些更改以使其运行得更快一些？

DECLARE @bsrange INT

SET @bsrange = 0

WHILE @bsrange <= (SELECT max([p_a_l_out])
                   FROM   [DB001].[FD\f7].[tbl_sb12_bhs])
  BEGIN
      INSERT INTO [FD\f7].tbl_sb13_b_lin1
                  (aId,
                   p_a_l_out,
                   bs_id,
                   bs_db,
                   bs_tbl,
                   bs_column,
                   Int1,
                   cd1,
                   Hop1,
                   Int2,
                   cd2,
                   Hop2,
                   Int3,
                   cd3,
                   Hop3,
                   Int4,
                   cd4,
                   Hop4,
                   Int5,
                   cd5,
                   Hop5,
                   Int6,
                   cd6,
                   Hop6,
                   Int7,
                   cd7,
                   Hop7,
                   Int8,
                   cd8,
                   Hop8,
                   Int9,
                   cd9,
                   Hop9,
                   Int10,
                   cd10,
                   Hop10,
                   Int11,
                   cd11,
                   Hop11,
                   Int12,
                   cd12,
                   Hop12,
                   Int13,
                   cd13,
                   Hop13,
                   Int14,
                   cd14,
                   Hop14,
                   Int15,
                   cd15,
                   Hop15,
                   Int16,
                   cd16,
                   Hop16)
      SELECT DISTINCT tbl_sb12_bhs.aId,
                      tbl_sb12_bhs.p_a_l_out,
                      tbl_sb12_bhs.bs_id,
                      tbl_sb12_bhs.bs_db,
                      tbl_sb12_bhs.bs_tbl,
                      tbl_sb12_bhs.bs_column,
                      tbl_rpt_val_pt_crl.pt_el_Int    AS Int1,
                      tbl_rpt_val_pt_crl.user_cd      AS cd1,
                      tbl_rpt_val_pt_crl.cfk_upel     AS Hop1,
                      tbl_rpt_val_pt_crl_1.pt_el_Int  AS Int2,
                      tbl_rpt_val_pt_crl_1.user_cd    AS cd2,
                      tbl_rpt_val_pt_crl_1.cfk_upel   AS Hop2,
                      tbl_rpt_val_pt_crl_2.pt_el_Int  AS Int3,
                      tbl_rpt_val_pt_crl_2.user_cd    AS cd3,
                      tbl_rpt_val_pt_crl_2.cfk_upel   AS Hop3,
                      tbl_rpt_val_pt_crl_3.pt_el_Int  AS Int4,
                      tbl_rpt_val_pt_crl_3.user_cd    AS cd4,
                      tbl_rpt_val_pt_crl_3.cfk_upel   AS Hop4,
                      tbl_rpt_val_pt_crl_4.pt_el_Int  AS Int5,
                      tbl_rpt_val_pt_crl_4.user_cd    AS cd5,
                      tbl_rpt_val_pt_crl_4.cfk_upel   AS Hop5,
                      tbl_rpt_val_pt_crl_5.pt_el_Int  AS Int6,
                      tbl_rpt_val_pt_crl_5.user_cd    AS cd6,
                      tbl_rpt_val_pt_crl_5.cfk_upel   AS Hop6,
                      tbl_rpt_val_pt_crl_6.pt_el_Int  AS Int7,
                      tbl_rpt_val_pt_crl_6.user_cd    AS cd7,
                      tbl_rpt_val_pt_crl_6.cfk_upel   AS Hop7,
                      tbl_rpt_val_pt_crl_7.pt_el_Int  AS Int8,
                      tbl_rpt_val_pt_crl_7.user_cd    AS cd8,
                      tbl_rpt_val_pt_crl_7.cfk_upel   AS Hop8,
                      tbl_rpt_val_pt_crl_8.pt_el_Int  AS Int9,
                      tbl_rpt_val_pt_crl_8.user_cd    AS cd9,
                      tbl_rpt_val_pt_crl_8.cfk_upel   AS Hop9,
                      tbl_rpt_val_pt_crl_9.pt_el_Int  AS Int10,
                      tbl_rpt_val_pt_crl_9.user_cd    AS cd10,
                      tbl_rpt_val_pt_crl_9.cfk_upel   AS Hop10,
                      tbl_rpt_val_pt_crl_10.pt_el_Int AS Int11,
                      tbl_rpt_val_pt_crl_10.user_cd   AS cd11,
                      tbl_rpt_val_pt_crl_10.cfk_upel  AS Hop11,
                      tbl_rpt_val_pt_crl_11.pt_el_Int AS Int12,
                      tbl_rpt_val_pt_crl_11.user_cd   AS cd12,
                      tbl_rpt_val_pt_crl_11.cfk_upel  AS Hop12,
                      tbl_rpt_val_pt_crl_12.pt_el_Int AS Int13,
                      tbl_rpt_val_pt_crl_12.user_cd   AS cd13,
                      tbl_rpt_val_pt_crl_12.cfk_upel  AS Hop13,
                      tbl_rpt_val_pt_crl_13.pt_el_Int AS Int14,
                      tbl_rpt_val_pt_crl_13.user_cd   AS cd14,
                      tbl_rpt_val_pt_crl_13.cfk_upel  AS Hop14,
                      tbl_rpt_val_pt_crl_14.pt_el_Int AS Int15,
                      tbl_rpt_val_pt_crl_14.user_cd   AS cd15,
                      tbl_rpt_val_pt_crl_14.cfk_upel  AS Hop15,
                      tbl_rpt_val_pt_crl_15.pt_el_Int AS Int16,
                      tbl_rpt_val_pt_crl_15.user_cd   AS cd16,
                      tbl_rpt_val_pt_crl_15.cfk_upel  AS Hop16
      FROM   (SELECT DISTINCT pk_a AS aId,
                              p_a_l_out,
                              bs_id,
                              bs_db,
                              bs_tbl,
                              bs_column,
                              hop_pt_id_1,
                              hop_pt_id_2,
                              hop_pt_id_3,
                              hop_pt_id_4,
                              hop_pt_id_5,
                              hop_pt_id_6,
                              hop_pt_id_7,
                              hop_pt_id_8,
                              hop_pt_id_9,
                              hop_pt_id_10,
                              hop_pt_id_11,
                              hop_pt_id_12,
                              hop_pt_id_13,
                              hop_pt_id_14,
                              hop_pt_id_15,
                              hop_pt_id_16
              FROM   [FD\f7].tbl_sb12_bhs
              WHERE  [p_a_l_out] >= @bsrange
                     AND [p_a_l_out] < ( @bsrange + 1 )) AS tbl_sb12_bhs
             LEFT JOIN tbl_rpt_val_pt_crl
               ON tbl_sb12_bhs.hop_pt_id_1 = tbl_rpt_val_pt_crl.sk_el_pt
             LEFT JOIN tbl_rpt_val_pt_crl AS tbl_rpt_val_pt_crl_1
               ON tbl_sb12_bhs.hop_pt_id_2 = tbl_rpt_val_pt_crl_1.sk_el_pt
             LEFT JOIN tbl_rpt_val_pt_crl AS tbl_rpt_val_pt_crl_2
               ON tbl_sb12_bhs.hop_pt_id_3 = tbl_rpt_val_pt_crl_2.sk_el_pt
             LEFT JOIN tbl_rpt_val_pt_crl AS tbl_rpt_val_pt_crl_3
               ON tbl_sb12_bhs.hop_pt_id_4 = tbl_rpt_val_pt_crl_3.sk_el_pt
             LEFT JOIN tbl_rpt_val_pt_crl AS tbl_rpt_val_pt_crl_4
               ON tbl_sb12_bhs.hop_pt_id_5 = tbl_rpt_val_pt_crl_4.sk_el_pt
             LEFT JOIN tbl_rpt_val_pt_crl AS tbl_rpt_val_pt_crl_5
               ON tbl_sb12_bhs.hop_pt_id_6 = tbl_rpt_val_pt_crl_5.sk_el_pt
             LEFT JOIN tbl_rpt_val_pt_crl AS tbl_rpt_val_pt_crl_6
               ON tbl_sb12_bhs.hop_pt_id_7 = tbl_rpt_val_pt_crl_6.sk_el_pt
             LEFT JOIN tbl_rpt_val_pt_crl AS tbl_rpt_val_pt_crl_7
               ON tbl_sb12_bhs.hop_pt_id_8 = tbl_rpt_val_pt_crl_7.sk_el_pt
             LEFT JOIN tbl_rpt_val_pt_crl AS tbl_rpt_val_pt_crl_8
               ON tbl_sb12_bhs.hop_pt_id_9 = tbl_rpt_val_pt_crl_8.sk_el_pt
             LEFT JOIN tbl_rpt_val_pt_crl AS tbl_rpt_val_pt_crl_9
               ON tbl_sb12_bhs.hop_pt_id_10 = tbl_rpt_val_pt_crl_9.sk_el_pt
             LEFT JOIN tbl_rpt_val_pt_crl AS tbl_rpt_val_pt_crl_10
               ON tbl_sb12_bhs.hop_pt_id_11 = tbl_rpt_val_pt_crl_10.sk_el_pt
             LEFT JOIN tbl_rpt_val_pt_crl AS tbl_rpt_val_pt_crl_11
               ON tbl_sb12_bhs.hop_pt_id_12 = tbl_rpt_val_pt_crl_11.sk_el_pt
             LEFT JOIN tbl_rpt_val_pt_crl AS tbl_rpt_val_pt_crl_12
               ON tbl_sb12_bhs.hop_pt_id_13 = tbl_rpt_val_pt_crl_12.sk_el_pt
             LEFT JOIN tbl_rpt_val_pt_crl AS tbl_rpt_val_pt_crl_13
               ON tbl_sb12_bhs.hop_pt_id_14 = tbl_rpt_val_pt_crl_13.sk_el_pt
             LEFT JOIN tbl_rpt_val_pt_crl AS tbl_rpt_val_pt_crl_14
               ON tbl_sb12_bhs.hop_pt_id_15 = tbl_rpt_val_pt_crl_14.sk_el_pt
             LEFT JOIN tbl_rpt_val_pt_crl AS tbl_rpt_val_pt_crl_15
               ON tbl_sb12_bhs.hop_pt_id_16 = tbl_rpt_val_pt_crl_15.sk_el_pt

      SET @bsrange = @bsrange + 1
  END 

Answer 1

我最好的猜测是它很慢，因为你一次做了很多密集的操作。没有任何样本数据，这是困难的，但我可以尝试提出一些建议。

从你所说的在一个小时后只处理40条记录的情况来看，是循环内部发生的事情减慢了你的速度。

SELECT DISTINCT并不便宜，因为它必须比较所有数据，而且您还需要比较相当多的列。如果可以，如果您将列数限制为不同选择所需的最小列数，而不是将其自连接到原始表，则运行速度可能会更快。它应该足够简单，独立于其他部分进行测试，以确保您得到相同的结果，以及是否更快。

此外，连接越多，总体性能越差……我们为正常化付出的代价。

无论如何，我会退后一步，尝试将其分解为最小的工作单元，然后您可以分别测试每个单元，直到找到罪魁祸首。在这样做的时候，您可能会想到一种更好的方法来实现这一点。同样，如果没有任何样本数据，这对我来说是一个很难帮助的问题。

Answer 2

如果您在目标上有一个或多个索引，那么SQL将对每一行重新创建索引。我会禁用目标表上的所有索引，然后在插入完成时重新启用它们。Id批处理插入(比如说) 5k记录的范围，这样任何阻塞都会减少，否则我会在结果中创建一个临时文件作为select和bcp的结果。因为您每次在插入一条记录之前都要执行那组可怕的左连接。SQL最多只能优化7到8个左右的左或右连接。我的猜测是，插入的表上几乎没有索引，这意味着对每个连接执行一次表扫描，或者对插入的每一行执行大约17次表扫描。抱歉，这种方法在每个阶段都是错误的。或者你可以让你的老板给你买个约会中心...

Answer 3

您可能想要做的另一件事是最初连接到一个临时表中，然后引用该表。您不必每次都进行distinct或joins操作。只需为bsrange添加where子句。

所以它应该是这样的：

Create temporary table with as much of the joins/distinct as you can.
while.....
   insert into [FD\f7].tbl_sb13_b_lin1
       select * from temptable where  [p_a_l_out] >= @bsrange
                     AND [p_a_l_out] < ( @bsrange + 1 )