pg.ddx.io pgsql-performance@postgresql.org mailing list archive
help / color / mirror / Atom feedSelect 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>
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 18:29 Tom Lane <tgl@sss.pgh.pa.us>
parent: Charles Joseph <khocuihu@yahoo.com>
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