pg.ddx.io  pgsql-performance@postgresql.org mailing list archive  
help / color / mirror / Atom feed
Select in FOR LOOP Performance
2+ messages / 2 participants
[nested] [flat]

* Select in FOR LOOP Performance
@ 2005-03-04 17:53 Charles Joseph <khocuihu@yahoo.com>
  2005-03-04 18:29 ` Re: Select in FOR LOOP Performance Tom Lane <tgl@sss.pgh.pa.us>
  0 siblings, 1 reply; 2+ messages in thread

From: Charles Joseph @ 2005-03-04 17:53 UTC (permalink / raw)
  To: pgsql-performance

    I face problem when running the following pgplsql
function. The problem is it takes more than 24hours to
complete
    the calculation. The EMP table has about 200,000
records. I execute the function through psql  "select
calculate()";
    (There is no cyclic link inside the data).
    
    Computer used: IBM xSeries 225, RAM 1GB, SCSI 36GB
    O/S : RedHat Linux Enterprise 3.0 AS
    PostgreSQL version 8.0.1
    fsync=false
    
    I would very appreciate if anyone can help to find
out what the problem is, or any others way to improve
the performance
    of the function. 
    
    Is there any difference between select in FOR LOOP
with CURSOR in term of performance ?

    EMP Table
        GEN  char(3),
        CODE varchar(20),
        PARENT varchar(20),
        POSITION INT4 DEFAULT 0,
        PG NUMERIC(15,2) DEFAULT 0,
        P  NUMERIC(15,2) DEFAULT 0,
        QUA CHAR(1) DEFAULT '0',
    .
    .
    .             
       create index EMP_GEN on EMP (GEN);
       create index EMP_CODE on EMP (CODE);
       create index EMP_PARENT on PARENT (PARENT);
    
    Sample EMP DATA:
  GEN   CODE   PARENT     POSITION    P    PG   QUA
  ===============================================
  000   A001   ****       3           100   0   '1'
  001   A002   A001       2            50   0   '1'
  001   A003   A001       1            50   0   '1'
  001   A004   A001       1            20   0   '1'
  002   A005   A003       2            20   0   '1'
  002   A006   A004       3            30   0   '1'
       ...
       ...
       
    
    for vTMP_ROW in select CODE,PARENT,POSITION from
EMP order by GEN desc loop    
        vCODE     := vTMP_ROW.CODE;
        vPARENT   := vTMP_ROW.PARENT;
        nPOSITION := vTMP_ROW.POSITION;

        update EMP set PG=PG+P where CODE = vCODE;

        select into vCURR_ROW PG,POSITION from EMP
where CODE = vCODE;
        
        nPG       := vCURR_ROW.PG;
        nPOSITION := vCURR_ROW.POSITION;

        vUPL := vPARENT;
        
        loop
           select into vUPL_ROW
CODE,PARENT,POSITION,P,QUA from EMP where CODE = vUPL;
           if found then
              if vUPL_ROW.POSITION > nPOSITION and
vUPL_ROW.QUA = ''1'' then
                 update EMP set PG=PG+nPG where CODE =
vUPL;
                 exit;
              end if;
           else 
              exit;   
           end if;
           vUPL := vUPL_ROW.PARENT;
        end loop;
    end loop;
 
    .
    .
    .

Thank You
  


	
		
__________________________________ 
Celebrate Yahoo!'s 10th Birthday! 
Yahoo! Netrospective: 100 Moments of the Web 
http://birthday.yahoo.com/netrospective/



^ permalink  raw  reply  [nested|flat] 2+ messages in thread

* Re: Select in FOR LOOP Performance
  2005-03-04 17:53 Select in FOR LOOP Performance Charles Joseph <khocuihu@yahoo.com>
@ 2005-03-04 18:29 ` Tom Lane <tgl@sss.pgh.pa.us>
  0 siblings, 0 replies; 2+ messages in thread

From: Tom Lane @ 2005-03-04 18:29 UTC (permalink / raw)
  To: Charles Joseph <khocuihu@yahoo.com>; +Cc: pgsql-performance

Charles Joseph <khocuihu@yahoo.com> writes:
>     I face problem when running the following pgplsql
> function. The problem is it takes more than 24hours to
> complete
>     the calculation. The EMP table has about 200,000
> records.

Sure there are no infinite loops of PARENT links in your table?

Also, if CODE is supposed to be unique, you should probably declare
its index that way.  Or at least make sure the planner knows it's
unique (have you ANALYZEd the table lately?)

			regards, tom lane



^ permalink  raw  reply  [nested|flat] 2+ messages in thread


end of thread, other threads:[~2005-03-04 18:29 UTC | newest]

Thread overview: 2+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2005-03-04 17:53 Select in FOR LOOP Performance Charles Joseph <khocuihu@yahoo.com>
2005-03-04 18:29 ` Tom Lane <tgl@sss.pgh.pa.us>

This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox