CODEWITHSUNDEEP
oracle-database
Andrew
Andrew

Reputation: 29

Slow update query in Oracle - what am I doing wrong?

Update queries have never been my strong point, I am hoping someone can help me make a more efficient query?

I am trying to update a table with the total sales for a given product, for a given customer.

The table I am looking to update is the sales column of the below 'Estimate' table:

ID    Customer Product    Estimate    Sales
--------------------------------------------
1     A        303        100         20
2     A        425        20          30
3     C        1145       500         250
4     F        801        25          0

The figure I am using to update is taken from the 'Sales' view:

Product     Customer   Actual
------------------------------
303         A          30
500         C          2
425         A          88
1145        C          700

The query I have written is:

UPDATE estimate e
SET e.sales = (SELECT s.actual FROM sales s 
               WHERE e.customer = s.customer and e.product = s.product)
WHERE EXISTS (SELECT 1 sales s 
              WHERE e.customer = s.customer and e.product = s.product)

An added complication is that 'estimates' exists between a range of dates and need to be updated for sales during that period only. My 'sales' view above take care of that, but I have left this out of the example for simplicity sake.

I initially ran the query using test data of only around 20 records and it ran in around 3 /4 seconds. My actual data is 7,000+ records and when I run the query here, my browser times out before I get any results.

I suspect that the query is updating the whole table for every record in the view or vice versa?

Any help much appreciated.

Cheers

Andrew

Upvotes: 0

Views: 1518

Answers (1)

Boneist
Boneist

Reputation: 23588

Try a merge instead:

merge into estimate tgt
using sales src
  on (tgt.customer = src.customer and tgt.product = src.product)
when matched then
  update tgt.sales = src.actual;

By doing the merge instead of an update, you negate the need to repeat the query in the set clause in the where clause, which ought to speed things up a bit.

Another thing to check is how many indexes do you have on the estimate table that has the src column in it? If you have several, it might be worth reducing the number of indexes. Each index that has to be updated is an overhead when you update the row(s) in the table.

Also, do you have triggers on the estimate table? Those might be slowing things down as well.

Or maybe you're missing an index on the sales table - an index on (customer, product and sales) ought to help, as the query should then be able to avoid going to the table at all since the data it needs from that table would all be in the index.

Another argument you could have is to not do the update at all. If the information is available in the Sales table, why do you need to bother updating the estimate table at all? You could do that as a join when querying the estimate table. Of course, it depends on how often you'd be querying for the estimate vs actual sales information vs how often they'd be updated. If you update frequently and read infrequently, then I'd skip the update and just query the two tables directly.

Upvotes: 1

Related Questions