Re: PsqlODBC slow on UNION queries

From: Zoltan Boszormenyi <zboszor(at)dunaweb(dot)hu>
To: Ludek Finstrle <luf(at)pzkagis(dot)cz>
Cc: pgsql-odbc(at)postgresql(dot)org
Subject: Re: PsqlODBC slow on UNION queries
Date: 2006-01-27 17:31:58
Message-ID: 43DA590E.4070403@dunaweb.hu
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: 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

In response to

Responses

Browse pgsql-odbc by date

  From Date Subject
Next Message web de Konto 2006-01-27 18:06:27
Previous Message Ludek Finstrle 2006-01-27 17:13:02 Re: PsqlODBC slow on UNION queries