ODBC Performance slow for large results

Hi,

I am not using "UseDeclareFetch" and using "UseServerSidePrepare=1", but
we see over 40 mins for fetching over million rows between couple
powerful machines. Debug/trace is not in use either. I am using Redhat
Linux machines for both server and client. I am using prepare
statements. I using a small perl utility for testing, but results are
same with isql too.

I have read though the ODBC code, couple things I observed was

1) All the tuples from the server are pre-fetched before a single row is
returned to user. So, million rows are fetched to driver first (I was
not sure whether these are all memory resident or written to temp disk
space). So can somebody comment on memory implications? I see this
operation is completed fairly quickly, noting the fact that PG server is
responsive in returning and no network delays seen. We are also ran
independent tests to make sure we are running in full duplex.

2) From the debug logs, we seen significant time spent on "copy and
convert" operation, where the text response from PG server is being
converted to respective data types. Is there any way I can configure PG
database and ODBC driver to return the data in "binary" format, to
minimize this step? I do see CPU utilization being pegged during
operation.

Any way to re-compile *may be* to make both 1 & 2 above occur in
parallel to minimize times? Or any other techniques any one can suggest,
I would really appreciate it.

Thank you in advance for any advise you can share.

Ramesh..


--
Sent via pgsql-odbc mailing list (pgsql-odbc@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-odbc

Re: ODBC Performance slow for large results

(2011/06/19 11:06), Ramesh Reddy wrote:
> Hi,
>
> I am not using "UseDeclareFetch" and using "UseServerSidePrepare=1", but
> we see over 40 mins for fetching over million rows between couple
> powerful machines. Debug/trace is not in use either. I am using Redhat
> Linux machines for both server and client. I am using prepare
> statements. I using a small perl utility for testing, but results are
> same with isql too.
>
> I have read though the ODBC code, couple things I observed was
>
> 1) All the tuples from the server are pre-fetched before a single row is
> returned to user. So, million rows are fetched to driver first (I was
> not sure whether these are all memory resident or written to temp disk
> space). So can somebody comment on memory implications? I see this
> operation is completed fairly quickly, noting the fact that PG server is
> responsive in returning and no network delays seen. We are also ran
> independent tests to make sure we are running in full duplex.
>
> 2) From the debug logs, we seen significant time spent on "copy and
> convert" operation, where the text response from PG server is being
> converted to respective data types.

Could you send me directly the part of Mylog output?

regards,
Hiroshi Inoue

> Is there any way I can configure PG
> database and ODBC driver to return the data in "binary" format, to
> minimize this step? I do see CPU utilization being pegged during
> operation.
>
> Any way to re-compile *may be* to make both 1& 2 above occur in
> parallel to minimize times? Or any other techniques any one can suggest,
> I would really appreciate it.
>
> Thank you in advance for any advise you can share.
>
> Ramesh..

--
Sent via pgsql-odbc mailing list (pgsql-odbc@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-odbc