Thread: ecpg - Poor fetch performance

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