ecpg - Poor fetch performance

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

Browse pgsql-novice by date

  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