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

Ludek Finstrle írta:

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

... retrieve 1000+ rows and then _I_ pressed Cancel. Can you confirm that?
I am just curious.

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

Would the same view do but only seleting numeric (integer and decimal)
columns?

>2) maybe minimalization of view to one column could be the best test
>
>

OK.

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

OK.

>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')
>
>

I don't know what are these. I guess  PowerBuilder tries something
behind everyones' back.
I just opened the "Database Painter" and issued "select * from
v_invoice_browse;".
I will recheck my database but those table names aren't familiar at all.
Maybe PB created
them at first connect and collected something about the database in
those tables.

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

I will test these, too.

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

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