Skip site navigation (1) Skip section navigation (2)

ECPG patch to use prepare for improved performance

From: "William Lawrance" <bill(dot)lawrance(at)bull(dot)com>
To: "Pgsql-Patches" <pgsql-patches(at)postgresql(dot)org>
Subject: ECPG patch to use prepare for improved performance
Date: 2007-05-07 21:46:29
Message-ID: BNEIKJMOJGCEDBNCBHEGAEGECFAA.bill.lawrance@bull.com (view raw or flat)
Thread:
Lists: pgsql-patches
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.








Attachment: ecpg.patch
Description: application/octet-stream (26.4 KB)

Responses

pgsql-patches by date

Next:From: Neil ConwayDate: 2007-05-07 22:25:19
Subject: Re: Implemented current_query
Previous:From: Heikki LinnakangasDate: 2007-05-07 21:31:55
Subject: Re: Diagnostic functions

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group