Re: embedded sql regression from 8.2.4 to 8.3.7

>-----Original Message-----
>From: Albe Laurenz [mailto:laurenz(dot)albe(at)wien(dot)gv(dot)at]
>>
>> 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:

Yes, but it's still incurring the overhead of sending the message to the
server, isn't it?

>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?

No, the queries aren't complex, but we prepare and excute hundred of
queries, so it seems like the overhead of the extra message sent to the
server adds up.

>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.

There are a number of things I could optimize once I start changing the
code, such as just skipping the prepare entirely, but then I'd need to
go through another whole release cycle of my app and I'd prefer not to
do that right now. I was hoping there was a way to work around this by
having Postgres not send that prepare to the server, but given the
"major protocol rewrite" phrase on that commit log message you pointed
me at, I'm guessing that's not possible.

eric