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

> finally here's the results. I hope you can find something useful in it.

Finally I see the problem in mylog output. Unfortunatelly I don't know
how to change the behaviour :-( without more investigation.

There is problem that you (I mean PowerBuilder) calling
SQLExecDirect select * from view (which fire the execution)
SQLDescribeCol (for each column in result)
SQLCancel (I don't know why the statement is canceled
           it seems to me that PB miss some information)
...
SQLTables (get additional informations)
...
SQLColumns
...
SQLPrimaryKeys
...
SQLStatistics
...
SQLExecDirect select * from view (which fire the execution again)

I have a lot of questions it will be the best if you could try
create some repro app. Some of the questions:
1) could you try similar view without text (char, varchar, ...) columns?
2) maybe minimalization of view to one column could be the best test
3) could you try enable trace ODBC (in ODBC manager) - independent on
   datasource and create two logs - againist psqlodbc and another DB
   - maybe we could find the difference there
4) could you say me what are the query for (it's fired bettwen two calls
   of select * from view):
   - select pbd_fhgt, pbd_fwgt, pbd_fitl, pbd_funl, pbd_fchr, pbd_fptc,
            pbd_ffce, pbh_fhgt, pbh_fwgt, pbh_fitl, pbh_funl, pbh_fchr,
            pbh_fptc, pbh_ffce, pbl_fhgt, pbl_fwgt, pbl_fitl, pbl_funl,
            pbl_fchr, pbl_fptc, pbl_ffce, pbt_cmnt
     from informix.pbcattbl
     where pbt_tnam = 'v_invoice_browse' and pbt_ownr = 'informix'
   - select pbv_name, pbv_vald, pbv_type, pbv_cntr, pbv_msg
     from informix.pbcatvld
   - select pbf_name, pbf_frmt, pbf_type, pbf_cntr
     from informix.pbcatfmt
   - select pbe_name, pbe_edit, pbe_type, pbe_cntr, pbe_work, pbe_seqn,
            pbe_flag
     from informix.pbcatedt
     order by pbe_name, pbe_seqn
   - select pbc_tnam, pbc_ownr, pbc_cnam, pbc_labl, pbc_lpos, pbc_hdr,
            pbc_hpos, pbc_jtfy, pbc_mask, pbc_case, pbc_hght, pbc_wdth,
            pbc_ptrn, pbc_bmap, pbc_init, pbc_cmnt, pbc_edit
     from informix.pbcatcol
     where (pbc_tnam = 'v_invoice_browse' and pbc_ownr = 'informix')
5) How long does it take only (without fetch):
   BEGIN; DECLARE CURSOR csr_test AS select * from v_invoice_browse; COMMIT;
6) How long does it take:
   BEGIN; DECLARE CURSOR csr_test AS select * from v_invoice_browse;
   FETCH BACKWARD in csr_test; COMMIT;

Maybe the PowerBuilder go this way all the time. It's our problem
that we get first data in SQLExecDirect. I don't know it right now.
We have to find the solution but the way could be hard.

Regards,

Luf