Performance tuning mystery
time to read 1 min | 197 words

I got a stored procedure that was taking an undue amount of time to run, in was about half an hour, and that was really bad. I tried looking at the execution plan and add an index to make it faster, but it wasn't that effective.

The query was an update from a nested select (which contained a rather complex condition and a couple of joins), and I couldn't really figure out how to make it better. I tried running the pieces individually, and they run reasonably fast, so it was the interactions between them that killed the performance. The execution plan showed that the joins were costing very much in terms of performance.

During my attempts to figure it out, I moved the select into a temporary table, and then updated from it, like this:

SELECT To, SUM(p.Amount) TotalAmount
INTO #TempPaymetsAmounts
FROM Payments p, Payments p2, Orders o
WHERE ComplexCondition
GROUP BY [To]

UPDATE PaymentsTotal
SET Total = TotalAmount
FROM #TempPaymetsAmounts
WHERE SimplerCondition

That query run in less than 10 seconds.

The only thing that I did was move the select into a temporary table, but I'm not sure why this should have such a profound effect on the performance of the query.

Tweet Share Share 0 comments
Tags: