Re: ECPG patch to use prepare for improved performance

William Lawrance wrote:
> 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?

One reason is that prepared statements have the parameters passed out of
line after the planning is done, so in certain cases the optimizer makes
a different choice which leads to worse plans.

This used to be a problem with JDBC as well, until a workaround was
added so that the "unnamed" prepared statement is not planned until the
parameters are passed. If you don't do that, it may end up being a bad
choice for applications as well.

> Other comments ?

Codewise I noticed you wrote your own hashing function, which seemed odd
to me at first sight. We already have a hashing infrastructure, but I'm
not sure if it could be used in ECPG (mainly due to lack of ereport/elog
support).

--
Alvaro Herrera http://www.CommandPrompt.com/
PostgreSQL Replication, Consulting, Custom Development, 24x7 support