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

Hi,

Ludek Finstrle írta:

>>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)];
>
>Regards,
>
>Luf
>
>

At the moment I am at home, being sick, so I cannot tell which queries
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.

[zozo@host-81-17-177-202 psql]$ cat q1.txt
select * from v_invoice_browse;

[zozo@host-81-17-177-202 psql]$ cat q2.txt
begin;
declare mycur1 cursor for select * from v_invoice_browse;
FETCH FORWARD 84693 FROM mycur1;
commit;

[zozo@host-81-17-177-202 psql]$ cat q3.txt
prepare plan1 as select * from v_invoice_browse;
execute plan1;

DECLARE CURSOR complained about being outside of a transaction
block, so I put it between BEGIN and  COMMIT.

UNION ALL:

[zozo@host-81-17-177-202 psql]$ time psql -f q1.txt -U bolt bolt2 >r1.txt

real    0m5.663s
user    0m2.440s
sys     0m0.347s

[zozo@host-81-17-177-202 psql]$ time psql -f q2.txt -U bolt bolt2 >r2.txt

real    0m5.870s
user    0m2.480s
sys     0m0.354s

[zozo@host-81-17-177-202 psql]$ time psql -f q3.txt -U bolt bolt2 >r3.txt

real    0m5.981s
user    0m2.449s
sys     0m0.353s

UNION:

[zozo@host-81-17-177-202 psql]$ time psql -f q1.txt -U bolt bolt2 >r1.txt

real    0m10.237s
user    0m2.453s
sys     0m0.214s

[zozo@host-81-17-177-202 psql]$ time psql -f q2.txt -U bolt bolt2 >r2.txt

real    0m10.535s
user    0m2.480s
sys     0m0.241s
[zozo@host-81-17-177-202 psql]$ time psql -f q3.txt -U bolt bolt2 >r3.txt

real    0m10.488s
user    0m2.466s
sys     0m0.245s

The timings between UNION and UNION ALL comparing the same query
is about what I expected. But there isn't too much difference between
the different queries on the same view, so the PowerBuilder behaviour still
isn't clear to me.

BTW, the quoted timings in the previous post from psql and UNION
vs UNION ALL were taken on a 3GHz HT P4, running RedHat 9.
The client is Windows2000 with PsqlODBC 08.01.0106.
The above timings were on an Athlon 3200+, running FC3/x86-64.
Both servers are running PostgreSQL 8.1.1.

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.

Best regards,
Zoltán Böszörményi