Following is one of the update query and it's explain plan which takes about 6 mins to execute. I am trying to find a way to execute it faster. The functions used in the update statement are if then else test and then return one value or the other.
=====================================================
update mdc_upc
set total_curoh = ownedgoods(k.gmmid,k.divid, loc1_oh, loc2_oh,loc3_oh,loc120_oh, loc15_oh,chesh_curoh),
total_curoo =orderedgoods(k.gmmid,k.divid, loc1_oo, loc2_oo,loc3_oo,loc120_oo, loc15_oo,chesh_curoo),
total_oh = ownedgoods(k.gmmid,k.divid, 0, 0,loc3_oh,loc120_oh, loc15_oh,chesh_oh),
total_oo =orderedgoods(k.gmmid,k.divid, 0, 0,loc3_oo,loc120_oo, loc15_oo,chesh_oo)
from mdc_products p LEFT OUTER JOIN
kst k on p.dvm_d = k.dept
where p.keyp_products = mdc_upc.keyf_products;
---------------------------------------------------------------------------
Hash Join (cost=48602.07..137331.77 rows=695899 width=391)
Hash Cond: ("outer".keyf_products = "inner".keyp_products)
-> Seq Scan on mdc_upc (cost=0.00..59153.99 rows=695899 width=383)
-> Hash (cost=47274.60..47274.60 rows=530990 width=12)
-> Hash Left Join (cost=43.85..47274.60 rows=530990 width=12)
Hash Cond: ("outer".dvm_d = "inner".dept)
-> Seq Scan on mdc_products p (cost=0.00..39265.90 rows=530990 width=8)
-> Hash (cost=41.48..41.48 rows=948 width=12)
-> Seq Scan on kst k (cost=0.00..41.48 rows=948 width=12)
======================================================
I have seen that the updates are very slow on our system. What parameter should I test in order to find out why is it slow during update.
Thanks
Abu
It's here! Your new message!
Get
new email alerts with the free
Yahoo! Toolbar.