优化长期运行的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 )

大约需要一个小时才能执行。有没有可能对其进行优化？

编辑：

我在ResultTest列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% Hash匹配聚合体

2%哈希匹配内连接

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

如果每个ResulTest中只有一行process_id，我将通过以下方式进一步简化，删除外部组：

;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 

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。 在#tempAmount上创建无约束索引#tempAmount_process_id (scenario_id、process_id、facility_id、mp_surrogate_id)
• 最后一个问题:让操作系统尽可能多地将磁盘块缓存到内存中。在linux中，在某些性能关键的数据库投入生产之前，请执行"cat your_database.store.file > /dev/null“。许多磁盘读取将从内存缓存中命中。

Answer 3

首先，我建议掌握实际的过剩计划。如果从(SSMS)运行查询，请打开“包含实际执行计划”选项。如果此查询来自另一个程序，则运行并打开显示计划统计配置文件和/或显示计划XML统计配置文件。检查此配置文件，并查看查询是否如预期的那样运行。

你有ResultTest colum CostId的索引吗？只有150行时，对此表进行索引扫描并不是什么大事。如果这个表上没有索引，您可以尝试它。

我想知道执行计划是否正在执行嵌套循环以连接到ResultTest。如果是这样的话，那将是150×220,000,000 =330亿次操作。如果是这样的话，散列连接或合并连接会执行得更好。您可以使用联接提示OPTION (HASH JOIN)或OPTION (MERGE JOIN)强制特定的联接。光是这一点就能产生巨大的变化。

# that上的索引有很多列，这些列对于SELECT查询来说是不必要的。同时，它也是一个NONCLUSTERED索引。是否也有聚集索引？如果不是，您可以尝试将其转换为CLUSTERED并去掉其他列。这将减少索引的大小，并且应该执行得更好，因为scenario_id的所有行都是连续的。