ecpg - Poor fetch performance - Mailing list pgsql-novice

From Steve McAllister
Subject ecpg - Poor fetch performance
Date
Msg-id 008f01c48317$5c159020$3b00a8c0@home
Whole thread Raw
List 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

pgsql-novice by date:

Previous
From:
Date:
Subject: Re: PGSQL 8-beta For WinXP Home Edition Instructions
Next
From: Amir Zicherman
Date:
Subject: LOCK TABLE and FUNCTIONS