SEO

> 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

---------------------------(end of broadcast)---------------------------
TIP 5: don't forget to increase your free space map settings

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


---------------------------(end of broadcast)---------------------------
TIP 3: Have you checked our extensive FAQ?

http://www.postgresql.org/docs/faq

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

Yes. I see > 1000 < 1100 rows. You could go to the bottom of mylog (I see
union one) and search backward "select * from v_invoice_browse".
Then you search forward fetch <case size> (<case size> = 100 in your
configuration so you search "fetch 100"). I count 11 (I hope I don't
create a mistake) "fetch 100" => > 1000 < 1100 :-)

There is SQLCancel after last "fetch 100" ;-)

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

Yes. It's enough. I don't know if the problem isn't char column
handling.

> >4) could you say me what are the query for (it's fired bettwen two calls
> > of select * from view):

....

> I don't know what are these. I guess PowerBuilder tries something
> behind everyones' back.

I think so. Could you try these selects in psql or PgAdmin tool?
I'm curios what you see.

Regards,

Luf

---------------------------(end of broadcast)---------------------------
TIP 9: In versions below 8.0, the planner will ignore your desire to
choose an index scan if your joining column's datatypes do not
match