优化长时间运行的 SQL Server 查询。

Question

我有以下查询：

SELECT fpa.scenario_id,
   fpa.facility_id,
   cge.CostGroupId result_total_id,
   mp_surrogate_id,
   CAST(SUM(fpa.raw_amount * cge.CostSign) AS DECIMAL(25, 13)) 
   result_total_amount         
INTO ADM_FactProfitTotalAmount_1
FROM #tempAmount fpa
JOIN ResultTest cge ON cge.CostId = fpa.process_id 
WHERE fpa.scenario_id = 1
GROUP BY fpa.scenario_id, fpa.facility_id, cge.CostGroupId, fpa.mp_surrogate_id

• #tempAmount中有2.2亿行。
• ResultTest中有150行。

我在#tempAmount上建立了一个索引：

CREATE NONCLUSTERED INDEX #tempAmount_process_id
ON  #tempAmount(scenario_id, facility_id, mp_surrogate_id, process_id )

执行需要约1小时。有没有可能优化它？

编辑：

我在CostId列上创建了索引，对其他索引和查询进行了一些修改。

    CREATE CLUSTERED INDEX #tempFactAmount_index 
    ON  #tempAmount (process_id ,facility_id, mp_surrogate_id )

    SELECT  ISNULL(CAST(1 as BIGINT), 0) scenario_id,
            fpa.facility_id,
            cge.CostGroupId result_total_id,
            fpa.mp_surrogate_id,
            CAST(SUM(fpa.raw_amount * cge.CostSign) AS DECIMAL(25, 13)) result_total_amount         
    INTO ADM_FactProfitTotalAmount_1
    FROM ResultTest cge
    JOIN #tempAmount fpa ON cge.CostId = fpa.process_id 
    GROUP BY fpa.facility_id, fpa.mp_surrogate_id, cge.CostGroupId

执行计划：

41% 插入到ADM_FactProfitTotalAmount_1

51% 哈希匹配聚合

2% 哈希匹配内连接

- Andriy Kuzmych

---

你的 process_id 字段上有索引吗？从 JOIN 条件来看，定义一个索引可能会有益。将 process_id 移动到第二个索引列也可能有所帮助。但是，没有查询计划很难确定瓶颈在哪里。 - Oded

优秀的网络直播，关于优化查询方面：http://www.brentozar.com/archive/2012/10/back-index-basics-how-make-select-statements-faster-video/ - Oded

尝试在 #tempAmount 上仅针对 scenario_id 列创建聚集索引。 - Daniel PP Cabral

fpa.schenario_id = 1 有多个条件限制？ - Gordon Linoff

我从查询中删除了fpa.schenario_id = 1，抱歉给您带来了误导，请查看下面的查询。 - Andriy Kuzmych

@AndriyKuzmych 每个 process_id 是否有多个 ResultTest 行？ - Steve Ford

Answer 1

在这种情况下，我发现在连接较小的表之前先将较大的表中的金额相加通常会有所帮助。因此，在这种情况下，我会使用以下方法：

;WITH SUMCTE
AS
(
SELECT      fpa.facility_id,
            fpa.mp_surrogate_id,
            fpa.process_id,
            SUM(fpa.raw_amount) AS total_amount         
    FROM #tempAmount fpa 
    GROUP BY fpa.facility_id, fpa.mp_surrogate_id, fpa.process_id
)
SELECT  CAST(1 as BIGINT) AS Scenario_id,
        facility_id,
        cge.CostGroupId result_total_id,
        mp_surrogate_id,
        CAST(SUM(SCT.total_amount * cge.CostSign) AS DECIMAL(25, 13)) result_total_amount         
    INTO ADM_FactProfitTotalAmount_1
    FROM ResultTest cge
    JOIN SUMCTE SCT ON cge.CostId = SCT.process_id 
    GROUP BY fpa.facility_id, fpa.mp_surrogate_id, cge.CostGroupId

如果每个process_id在ResulTest中只有一行，则可以通过删除外部group by来进一步简化：

;WITH SUMCTE
AS
(
SELECT      fpa.facility_id,
            fpa.mp_surrogate_id,
            fpa.process_id,
            SUM(fpa.raw_amount) AS total_amount         
    FROM #tempAmount fpa 
    GROUP BY fpa.facility_id, fpa.mp_surrogate_id, fpa.process_id
)
SELECT  CAST(1 as BIGINT) AS Scenario_id,
        facility_id,
        cge.CostGroupId result_total_id,
        mp_surrogate_id,
        CAST((SCT.total_amount * cge.CostSign) AS DECIMAL(25, 13)) result_total_amount         
    INTO ADM_FactProfitTotalAmount_1
    FROM ResultTest cge
    JOIN SUMCTE SCT ON cge.CostId = SCT.process_id 

- Steve Ford

Answer 2

• 我建议从检查预估执行计划开始。
  http://msdn.microsoft.com/en-us/library/ms191194.aspx

• 多列索引只能在左前缀的情况下使用。 http://dev.mysql.com/doc/refman/5.0/en/multiple-column-indexes.html

  所以我建议将 process_id 移到 scenario_id 旁边，因为它们在 where 和 join 中都被使用。

  CREATE NONCLUSTERED INDEX #tempAmount_process_id ON #tempAmount(scenario_id, process_id, facility_id, mp_surrogate_id)

• 最后一个：让操作系统尽可能地将磁盘块缓存到内存中。 在 Linux 上，在将某些性能关键的数据库投入生产之前， 执行 "cat your_database.store.file > /dev/null"。 这将从内存缓存中读取大量磁盘数据。

- whunmr

Answer 3

首先，我建议捕获实际的执行计划。如果您正在从SQL Server Management Studio（SSMS）运行查询，请打开“包括实际执行计划”选项。如果此查询来自另一个程序，请运行SQL Server Profiler并打开Showplan Statistics Profile和/或Showplan XML Statistics Profile。查看此配置文件并查看查询是否按预期运行。

您在ResultTest列CostId上有索引吗？对于仅有150行的表格，索引扫描不是什么大问题。如果您没有在此表上创建索引，则可以尝试创建一个。

我想知道执行计划是否执行了嵌套循环以加入ResultTest。如果是这样，那将是150 X 220,000,000 = 330亿次操作。如果是这种情况，哈希连接或合并连接会更好。您可以使用连接提示OPTION（HASH JOIN）或OPTION（MERGE JOIN）强制执行特定的连接。这本身就可以产生巨大的差异。

#tempAmount上的索引有很多不必要的列，这些列对SELECT查询没有影响。此外，它是一个非聚集索引。
如果没有聚集索引，您可以尝试将其转换为CLUSTERED并摆脱其他列。这将缩小索引的大小，并且应该更有效，因为特定scenario_id的所有行都是连续的。

- Paul Williams