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.
Description: Binary data
---------------------------(end of broadcast)--------------------------- TIP 7: You can help support the PostgreSQL project by donating at http://www.postgresql.org/about/donate