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