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:[EMAIL PROTECTED] 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 ---------------------------(end of broadcast)--------------------------- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
