>-----Original Message-----
>From: Albe Laurenz [mailto:laurenz.albe@wien.gv.at]
>Eric Haszlakiewicz wrote:
>> 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]
exec sql fetch c_1ab into :myvar;
>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.
That doesn't avoid an extra prepare entirely since the fetch statement
gets prepared, but it actually _is_ faster: 1360 usec to run the (real)
query my way, 910 usec your way (710usec w/ pg8.2.4). (wall clock time,
measured in the app)
The real queries are a little more complicated that the above example.
One might have a form a bit like this:
select varchar_col1, varchar_col2 from foo where colA = '12345' and
colB = 99 and colC = 'xyzabc' and colD like 'BLUE%';
The difference in wall clock time from the app point of view seems to
match up with the query stats from the db, (20 usec for the parsing the
fetch, 268 usec for the select) so it looks like re-writing things this
way would help somewhat.
oh, yuck. It looks like I can't get rid of the prepare entirely b/c I
can't declare a cursor using a sql string. i.e.:
exec sql declare c_1ab cursor for :SQL_text;
actually means something more like:
exec sql declare c_1ab cursor for :statement_name;
Also, I can't use execute immediate with host variables, so I guess I'm
stuck preparing stuff. :(
eric