469,579 Members | 1,214 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,579 developers. It's quick & easy.

update trigger performance

Hello list,

I'm having a performance issue with a trigger before update, a single
row update take this

explain analyze update detpp set dpe_estado='C' where dpe_pk=25541

Index Scan using ix_detpp_pk on detpp (cost=0.00..6.01 rows=2
width=323) (actual time=0.063..0.070 rows=1 loops=1)
Index Cond: (dpe_pk = 25541)
Total runtime: 271.038 ms
(3 rows)

The code for the trigger is this, dunno if something wrong or if is
there another "better" way to get it done

DECLARE
lnRows integer;
totdet numeric(12,4);
oldtot numeric(12,4);
foo numeric(12,4);
hped record;
rprod record;
FACTORCOSTO CONSTANT FLOAT := 0.04;
BEGIN
new.dpe_stamp = 'now';
select into hped ped_estado,ped_tipo, ped_pk,
ped_factorad, ped_mercadoneg, ped_refno from ped_pro
where ped_pk = new.dpe_pedfk for update;
if not found then
raise exception 'EXCEPTION: NO existe encabezado par este detalle
de pedido';
end if;
select into rprod pro_derecho/100 as derecho,
pro_decreto/100 as decreto, pro_costnw, pro_qtyonhand,
pro_costprom, pro_code from product
where pro_pk = new.dpe_productfk for update;
if not found then
raise exception 'EXCEPTION: NO existe articulo par este detalle de
pedido';
end if;
new.dpe_procode = rprod.pro_code;
totdet := 0;
oldtot := 0;

-- Si es pedido local o extranjenro
if old.dpe_pedtype = 1 then
oldtot := old.dpe_qty * old.dpe_costol;
else
oldtot := old.dpe_qty * old.dpe_costod;
end if;

if new.dpe_pedtype = 1 then
totdet := new.dpe_qty * new.dpe_costol;
else
totdet := new.dpe_qty * new.dpe_costod;
new.dpe_costol := hped.ped_mercadoneg +
hped.ped_factorad * (rprod.derecho
+ rprod.decreto + FACTORCOSTO ) ;
new.dpe_costol := round(new.dpe_costol * new.dpe_costod,4) ;
end if;
if old.dpe_estado<>new.dpe_estado then
if new.dpe_estado='F' then
UPDATE ped_pro
SET ped_qfaxed = coalesce(ped_qfaxed,0) + 1,
ped_dfaxed = coalesce(ped_dfaxed,0) + totdet
WHERE ped_pk = new.dpe_pedfk;
elsif
new.dpe_estado='C' then
UPDATE ped_pro
SET ped_itemsconf = coalesce(ped_itemsconf,0) + 1,
ped_mnconf = coalesce(ped_mnconf,0) + totdet
WHERE ped_pk = new.dpe_pedfk;
elsif
new.dpe_estado='U' then
UPDATE ped_pro
SET ped_qtfact = coalesce(ped_qtfact,0) + 1,
ped_mnfact = coalesce(ped_mnfact,0) + totdet
WHERE ped_pk = new.dpe_pedfk;
end if;
end if;
UPDATE ped_pro
SET ped_mntotal = ped_mntotal - oldtot,
ped_itemstotal = ped_itemstotal - 1
WHERE ped_pk = old.dpe_pedfk;

UPDATE ped_pro
SET ped_mntotal = ped_mntotal + totdet,
ped_itemstotal = ped_itemstotal + 1
WHERE ped_pk = new.dpe_pedfk;

update product
set pro_qtypro = pro_qtypro - old.dpe_qty
where pro_pk = old.dpe_productfk;

update product
set pro_costproc = new.dpe_costol,
pro_qtypro = coalesce(pro_qtypro,0) + new.dpe_qty,
pro_ultqtyproc = new.dpe_qty,
pro_costprocd = new.dpe_costod
where pro_pk = new.dpe_productfk;
RETURN new;
END;
Any idea or suggestion is welcome.

--
Sinceramente,
Josué Maldonado.

"Cuando mi marido se retrasa para la cena, se que o tiene una amante o
está tirado, muerto en plena calle. Siempre espero que sea lo de la
calle." Jessica Tandy. Actriz Inglesa.

---------------------------(end of broadcast)---------------------------
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddressHere" to ma*******@postgresql.org)

Nov 23 '05 #1
0 1458

This discussion thread is closed

Replies have been disabled for this discussion.

Similar topics

8 posts views Thread by Jason | last post: by
1 post views Thread by Matt | last post: by
3 posts views Thread by takilroy | last post: by
17 posts views Thread by kalamos | last post: by
18 posts views Thread by Bill Smith | last post: by
13 posts views Thread by dennis | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.