Tutorial :How do I improve performance of a SQL UPDATE statement whose SET involves an expensive aggregate subquery?



Question:

I have the following UPDATE scenario:

UPDATE destTable d  SET d.test_count = ( SELECT COUNT( employee_id )                       FROM sourceTable s                       WHERE d.matchCode1 = s.matchCode1 AND                             d.matchCode2 = s.matchCode2 AND                             d.matchCode3 = s.matchCode3                        GROUP BY matchCode1, matchCode2, matchCode3, employee_id )  

I have to execute this in a loop changing out the match codes for each iteration.

Between two large tables (~500k records each), this query takes an unacceptably long time to execute. If I just had to execute it once, I wouldn't care too much. Given it is being executed about 20 times, it takes way too long for my needs.

It requires two full table scans (one for the destTable and another for the subquery).

Questions:

  1. What techniques do you recommend to speed this up?

  2. Does the SQL-optimizer run the subquery for each row I'm updating in the destTable to satisfy the where-clause of the subquery or does it have some super intelligence to do this all at once?


Solution:1

In Oracle 9i and higher:

MERGE     INTO    destTable d  USING   (          SELECT  matchCode1, matchCode2, matchCode3, COUNT(employee_id) AS cnt          FROM    sourceTable s          GROUP BY                  matchCode1, matchCode2, matchCode3, employee_id          ) so  ON      d.matchCode1 = s.matchCode1 AND          d.matchCode2 = s.matchCode2 AND          d.matchCode3 = s.matchCode3   WHEN MATCHED THEN  UPDATE  SET     d.test_count = cnt  

To speed up your query, make sure you have a composite index on (matchCode1, matchCode2, matchCode3) in destTable, and a composite index on (matchCode1, matchCode2, matchCode3, employee_id) in sourceTable


Solution:2

I have to execute this in a loop

The first thing you do is build the loop into your sub query or where clause. You're updating data, and then immediately replacing some of the data you just updated. You should be able to either filter your update to only change records appropriate to the current iteration or make your query complex enough to update everything in one statement- probably both.


Solution:3

Have you considered an UPDATE FROM query?


Note:If u also have question or solution just comment us below or mail us on toontricks1994@gmail.com
Previous
Next Post »