I have the following query:
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
- In
#tempAmountI have 220 millions rows. - In
ResultTestI have 150 rows.
I have an index on #tempAmount:
CREATE NONCLUSTERED INDEX #tempAmount_process_id
ON #tempAmount(scenario_id, facility_id, mp_surrogate_id, process_id )
It takes around 1 hour to execute. Is it possible to optimize it?
EDIT:
I have created index on ResultTest column CostId, changed a bit other index and query
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
Execution Plan:
41% insert into ADM_FactProfitTotalAmount_1
51% Hash Match Aggregate
2% Hash Match Inner Join
process_idas well? From the JOIN condition, it would appear to be beneficial to have one defined. And moving theprocess_idto be the second index column may also help. Though, without a query plan it is difficult to tell where the bottleneck is. - Oded