Skip site navigation (1) Skip section navigation (2)

update trigger performance

From: Josué Maldonado <josue(at)lamundial(dot)hn>
To: pgsql-general(at)postgresql(dot)org
Subject: update trigger performance
Date: 2004-08-30 21:02:56
Message-ID: 41339600.4090703@lamundial.hn (view raw or flat)
Thread:
Lists: pgsql-general
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.

pgsql-general by date

Next:From: Tim PenheyDate: 2004-08-30 21:12:12
Subject: Re: Single Row Table?
Previous:From: Tom LaneDate: 2004-08-30 20:54:46
Subject: Re: aggregates with complex type as state and init condition

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group