Re: PsqlODBC slow on UNION queries - Mailing list pgsql-odbc

> >>Well, as I said, doing the same test from PSQL, the performance
> >>difference is much less. I was really asking about the difference
> >>between psql and PsqlODBC:
> >>
> >>                          psql(output to file)         psqlodbc
> >>UNION               total time: < 12 sec        first row received after
> >>30 seconds
> >>UNION ALL      total time < 4 sec           first row received instantly
> >
> >I suppose you have used Declare/Fetch turned on. Please try the same
> >query in psql client. The exact query you can find in mylog output.
> >It could be something like
> >DECLARE CURSOR <CURSOR_NAME> FOR SELECT <your select>;
> >FETCH FORWARD <number of rows to fetch at first time> FROM <CURSOR_NAME>;
> >
> >If you have used Server side prepare then query is already different.
> >It use:
> >PREPARE <plan_name>[(paremters types)] AS <query>;
> >EXECUTE <plan_name> [(parameters)];
>
> At the moment I am at home, being sick, so I cannot tell which queries
> PowerBuilder use.

You don't understand me. When you turn on psqlODBC option Use Declare/Fetch
all SELECT statements are internally (by psqlODBC) changed to
DECLARE/FETCH commands. It's the same with Server side prepare.

So it doesn't matter what does PowerBuilder use.

> But here are the above queries and their timings from psql,
> this is on my home machine, so the timings are different from
> what I quoted. 84693 rows were produced by SELECT *,
> so that's the number I used in FETCH FORWARD.

psqlODBC driver use only Cache size (default is 100) in FETCH FORWARD.
But you can change it to get better performance.

> And the  timing differences happen differently if I attach the view
> in an Access database in Office2003 using PsqlODBC 8.01.0106.
> This is on WinXP running in VMWare on the same machine as the PostgreSQL
> server. E.g. the view opens in Access in about 7 seconds when I use
> UNION ALL,
> but it requires about 17 seconds if I use UNION. This was with both
> Declare/Fetch and Server side prepare being OFF. Also the same with
> only Server side prepare being ON.
>
> If I set only Declare/Fetch or both to ON, opening the VIEW in Access
> comes close to what I experinced in psql, e.g. it appears almost instantly
> when using UNION ALL, and just under 7 seconds when using UNION.
>
> I don't remember which ODBC settings I used for the Windows2000 client
> and PowerBuilder, I am not near that machine. I will recheck it when
> I get back to my workplace next monday.

There is another thing. psqlODBC driver simulate SQLColAttributes (I wrote
it on the fly so it could be different ODBC API) throught call
the query and then get the columns information before query is already
open. So it could be the time bottleneck.
I try describe it better:
SQLPrepare, SQLColAttributes (this call the statement for getting columns
information), SQLExecute (this call the statement for result).

If you want trace it down the good start point is turn mylog output on
and read the result in C:\mylog_XXXX.log (or txt extension?).

Regards,

Luf