From: | "Steve McAllister" <stevemca(at)optushome(dot)com(dot)au> |
---|---|
To: | <pgsql-novice(at)postgresql(dot)org> |
Subject: | ecpg - Poor fetch performance |
Date: | 2004-08-15 22:29:41 |
Message-ID: | 008f01c48317$5c159020$3b00a8c0@home |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-novice |
How can I improve the performance of my ecpg based program in version 7.4.2
of Postgresql.
The structure of my code looks like the following.
EXEC SQL Declare cursor A;
EXEC SQL open A; # Approximately 15,000 rows selected
Do {
EXEC SQL fetch from A into ....;
EXEC SQL Declare cursor B;
EXEC SQL open B; # Approximately 15 rows selected from a fairly
complex join
Do {
EXEC SQL fetch from B into ....; # Execute time
approx 200ms
} while (..);
EXEC SQL close cursor B;
} while (...);
EXEC SQL close cursor A;
Based on the above comments the total execution time is approx 12.5 hours.
My question is why does the inner fetch take 200ms to execute? I would
expect Open Cursor B to execute in approx 200ms. But I would expect the
fetch to be a read from some buffer area. Given that the fetch executes in
200ms this leads me to believe the fetch is re-executing the query each
time.
Can anyone help. Please.
Regards
Steve McAllister
From | Date | Subject | |
---|---|---|---|
Next Message | Amir Zicherman | 2004-08-16 05:47:52 | LOCK TABLE and FUNCTIONS |
Previous Message | operationsengineer1 | 2004-08-15 20:10:36 | Re: PGSQL 8-beta For WinXP Home Edition Instructions |