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.
It was developed during the preparation for a benchmark for a
large customer. This benchmark consists of several hundred programs
containing several thousand embedded SQL statements. The benchmark
has been successfully executed using Oracle, DB2, and PostgreSQL.
In the benchmark, Postgres is shown to be slower, by far, than the
other DBMS systems. In a three hour execution, using this patch,
approximately 30% was saved.
The following approach is used:
Within the "execute.c" module, routines are added to manage a cache
of prepared statements. These routines are used to search, insert,
and delete entries in the cache. The key for these cache entries is
the text of the SQL statement as passed by ECPG from the application
program.
Within the same module, the "ECPGexecute" function was replaced.
This is the function that is called to execute a statement after
some preliminary housekeeping is done. The original "ECPGexecute"
function constructs an ASCII string by replacing each host variable
with its current value and then calling "PQexec". The new
"ECPGexecute" function does the following:
- build an array of the current values of the host variables.
- search the cache for an entry indicating that this statement
has already been prepare'd, via "PQprepare"
- If no entry was found in the previous step, call "PQprepare"
for the statement and then insert an entry for it into the
cache. If this requires an entry to be re-used, execute a
"DEALLOCATE PREPARE.." for the previous contents.
- At this point, the SQL statement has been prepare'd by PQlib,
either when the statement was executed in the past, or in
the previous step.
- call "PQexecPrepared", using the array of parameters built
in the first step above.