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