StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

POOptimise long running SQL Server query
text
Body
copied!<p>I have the following query:</p> <pre><code>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 </code></pre> <ul> <li>In <code>#tempAmount</code> I have 220 millions rows.</li> <li>In <code>ResultTest</code> I have 150 rows.</li> </ul> <p>I have an index on <code>#tempAmount</code>:</p> <pre><code>CREATE NONCLUSTERED INDEX #tempAmount_process_id ON #tempAmount(scenario_id, facility_id, mp_surrogate_id, process_id ) </code></pre> <p>It takes around 1 hour to execute. Is it possible to optimize it?</p> <p>EDIT:</p> <p>I have created index on ResultTest column CostId, changed a bit other index and query</p> <pre><code> 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 </code></pre> <p>Execution Plan:</p> <p>41% insert into ADM_FactProfitTotalAmount_1</p> <p>51% Hash Match Aggregate</p> <p>2% Hash Match Inner Join</p>

Querying!

Guidance

An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload