Re: ECPG patch to use prepare for improved performance

This approach was used for several reasons--

1. No changes were required in the application source program. For
an application involving thousands of SQL statements in hundreds
of programs, this is important. This customer application has
been tuned extensively by the customer for DB2, and he is not
receptive to large changes.

2. The performance was improved by about 1 hour in the 3 hour
elapsed time of the application. This is important to the
customer in terms of accomplishing his work load in the
time that has been allotted, based on his experience with DB2.
Without this improvement, he is likely to consider it too slow.

I would like to emphasize that we aren't measuring an artificial
test program; this is a real customer's application. We loaded
7 million rows into 217 tables to run the application. I believe
it is representative of many real batch applications.

Is there reason not to prepare each statement?

Could it be predicated upon a user supplied option ?

Other comments ?

-----Original Message-----
From: Michael Meskes [mailto:meskes(at)postgresql(dot)org]
Sent: Wednesday, May 09, 2007 2:04 AM
To: William Lawrance
Cc: Pgsql-Patches
Subject: Re: [PATCHES] ECPG patch to use prepare for improved
performance

On Mon, May 07, 2007 at 02:46:29PM -0700, William Lawrance wrote:
> This patch for ECPG utilizes the "PQprepare" and "PQexecPrepared"
> functions to cause SQL statements from ECPG to be cached. It does
> this without requiring any changes in the user's source program.
> ...

I still do not understand why you prepare each statement. This might
help you with your test case, but I don't like to add this as a general
rule. If a user wants a prepared statement he/she should use the prepare
statement. I agree that the prepare logic has to be rewritten and this
is high on my agenda, but I will probably only do this for statements
issued with EXEC SQL PREPARE not for every single statement.

Michael