Re: embedded sql regression from 8.2.4 to 8.3.7 - Mailing list pgsql-performance

Eric Haszlakiewicz wrote:
> I noticed a bit of a performance regression in embedded sql queries when
> moving from the client libraries in verison 8.2.4 to 8.3.7.  My
> application does a whole lot of queries, many of which don't return any
> data.  When we moved to the new libraries the time of running a query
> (from the application point of view) went from about 550 usec to 800
> usec.  In both cases this was against a server running 8.3.7.
> I turned on log_statement_stats and noticed that the behaviour is
> slightly different, and the 8.3.7 version sends the statement to the
> server twice, while 8.2.4 only sends it once.
>
>     const char *SQL_text = "select * from foo";  (not always the same query)
>     exec sql prepare s_1ab from :SQL_text;   <---- [*1]
>     exec sql declare c_1ab cursor for s_1ab;
>     exec sql open c_1ab;                     <---- [*2]
>
> At [*1], with the 8.3.7 libraries, I see in the server log:
> STATEMENT:  select * from foo
>
> With 8.2.4, nothing is logged.  Both versions send the statement to
> declare the cursor:
> STATEMENT:  declare c_1ab  cursor  for select * from foo

The log is misleading; the first statement is not really executed,
it is only prepared (parsed). If you set the log level to DEBUG2, it
will look like:

  DEBUG:  parse s_1ab: select * from empsalary
  STATEMENT:  select * from empsalary
  LOG:  statement: begin transaction
  LOG:  statement: declare c_1ab  cursor  for select * from empsalary

The difference to 8.2 is that since 8.3, EXEC SQL PREPARE will result
in a PREPARE statement on the server. In 8.2, no named prepared
statement was created on the server, so nothing is logged in 8.2.

The change in the source was here:
http://archives.postgresql.org/pgsql-committers/2007-08/msg00185.php

Maybe it is the additional PREPARE that slows your program.
Are your queries complex enough that the PREPARE consumes
significant time?

Maybe you could use something like this to avoid the
extra PREPARE:

    EXEC SQL BEGIN DECLARE SECTION;
    const char *SQL_text = "declare c_1ab cursor for select * from foo";
    const char *fetch = "fetch from c_1ab";
    int i;
    EXEC SQL END DECLARE SECTION;

    ....
    exec sql execute immediate :SQL_text;
    exec sql prepare fetch from :fetch;
    exec sql execute fetch into :i;

It avoids the extra PREPARE, but looks pretty ugly.

Yours,
Laurenz Albe