Driver seems to hang fetching data

driver version 8.02.02

I have a query that returns around 30K records. I have set the cache to
1000 records. It appears that while pulling data, the driver hangs for
around 30 seconds or more and then continues to the next batch of records.
It does this same sort of behavior whether it caching is set to 100 or
1000. I am using DECLARE/FETCH.
Is there anywhere I can check for the bottle neck?

Thanks.

Patrick Hatcher

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

Re: Driver seems to hang fetching data

I seem to remember having problems with Declare/Fetch, after turning it
off I had a huge performance boost when running large reports.

Patrick Hatcher wrote:
> driver version 8.02.02
>
> I have a query that returns around 30K records. I have set the cache to
> 1000 records. It appears that while pulling data, the driver hangs for
> around 30 seconds or more and then continues to the next batch of records.
> It does this same sort of behavior whether it caching is set to 100 or
> 1000. I am using DECLARE/FETCH.
> Is there anywhere I can check for the bottle neck?
>
> Thanks.
>
> Patrick Hatcher
>
> ---------------------------(end of broadcast)---------------------------
> TIP 5: don't forget to increase your free space map settings
>
>


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

Re: Driver seems to hang fetching data

But doesn't turning it off cause the entire recordset to come back at once?

Patrick Hatcher




David Gardner
caipaco.com> To
Patrick Hatcher
02/28/07 06:02 PM
cc
pgsql-odbc@postgresql.org
Subject
Re: [ODBC] Driver seems to hang
fetching data










I seem to remember having problems with Declare/Fetch, after turning it
off I had a huge performance boost when running large reports.

Patrick Hatcher wrote:
> driver version 8.02.02
>
> I have a query that returns around 30K records. I have set the cache to
> 1000 records. It appears that while pulling data, the driver hangs for
> around 30 seconds or more and then continues to the next batch of
records.
> It does this same sort of behavior whether it caching is set to 100 or
> 1000. I am using DECLARE/FETCH.
> Is there anywhere I can check for the bottle neck?
>
> Thanks.
>
> Patrick Hatcher
>
> ---------------------------(end of broadcast)---------------------------
> TIP 5: don't forget to increase your free space map settings
>
>

---------------------------(end of broadcast)---------------------------
TIP 2: Don't 'kill -9' the postmaster

Re: Driver seems to hang fetching data

Patrick Hatcher wrote:
> driver version 8.02.02
>
> I have a query that returns around 30K records. I have set the cache to
> 1000 records. It appears that while pulling data, the driver hangs for
> around 30 seconds or more and then continues to the next batch of records.
> It does this same sort of behavior whether it caching is set to 100 or
> 1000. I am using DECLARE/FETCH.
> Is there anywhere I can check for the bottle neck?
>

Could send me the CommLog(psqlodbc_xxxx.log) output using the snapshot
dll at
http://www.geocities.jp/inocchichichi/psqlodbc/index.html
?

regards,
Hiroshi Inoue



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

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

Re: Driver seems to hang fetching data

> But doesn't turning it off cause the entire recordset to come back at once?

Yes, it does.

> I seem to remember having problems with Declare/Fetch, after turning it
> off I had a huge performance boost when running large reports.

I have a huge performance reduce with turning off Declare/Fetch.

It depends what you want and how is the app written.

Regards,

Luf

---------------------------(end of broadcast)---------------------------
TIP 7: You can help support the PostgreSQL project by donating at

http://www.postgresql.org/about/donate

Re: Driver seems to hang fetching data

Here you go Hiroshi. This was all that was in the Log file

[0.000]DSN info:
DSN='macys',server=XX.XXXXX.XX',port='5432',dbase='mdc_oz',u ser='',passwd='xxxxx'
[0.000]
onlyread='0',protocol='7.4',showoid='0',fakeoidindex='0',sho wsystable='0'
[0.000] conn_settings='',conn_encoding='(null)'
[0.000] translation_dll='',translation_option=''

Patrick Hatcher
Development Manager Analytics/MIO
Macys.com




Hiroshi Inoue

To
02/28/07 08:10 PM Patrick Hatcher

cc
pgsql-odbc@postgresql.org
Subject
Re: [ODBC] Driver seems to hang
fetching data










Patrick Hatcher wrote:
> driver version 8.02.02
>
> I have a query that returns around 30K records. I have set the cache to
> 1000 records. It appears that while pulling data, the driver hangs for
> around 30 seconds or more and then continues to the next batch of
records.
> It does this same sort of behavior whether it caching is set to 100 or
> 1000. I am using DECLARE/FETCH.
> Is there anywhere I can check for the bottle neck?
>

Could send me the CommLog(psqlodbc_xxxx.log) output using the snapshot
dll at
http://www.geocities.jp/inocchichichi/psqlodbc/index.html
?

regards,
Hiroshi Inoue

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

Re: Driver seems to hang fetching data

> Here you go Hiroshi. This was all that was in the Log file
>
> [0.000]DSN info:
> DSN='macys',server=XX.XXXXX.XX',port='5432',dbase='mdc_oz',u ser='',passwd='xxxxx'
> [0.000]
> onlyread='0',protocol='7.4',showoid='0',fakeoidindex='0',sho wsystable='0'
> [0.000] conn_settings='',conn_encoding='(null)'
> [0.000] translation_dll='',translation_option=''

Patrick,

please turn on the common log also in Datasource dialog.
You turned it on only in Global dialog. It's not enough. You have to
see the select statement in psqlodbc_XXXX.log.

Regards,

Luf

---------------------------(end of broadcast)---------------------------
TIP 7: You can help support the PostgreSQL project by donating at

http://www.postgresql.org/about/donate

Re: Driver seems to hang fetching data

Patrick Hatcher wrote:
> Sorry. I didn't realize there was a setting besides the Global one. Here
> you go:
> (See attached file: psqlodbc_5360.log)

Thanks.

AFAIC the merits to use DECLARE/FETCH mode are
1. You can suppress the memory consumption at some level.
2. you can get first rows fast in some cases.

Looking at the log you sent me, probably the query in question is
declare "SQL_CUR02D9FB78" cursor for select amc_Week_id,
lpad(zl_divn_nbr::text,2,0) as zl_divn_nbr,
lpad(dept::text,4,0) as dept, .............
As for the merit 1), though the query returns 156673 records , you can
get them successfully even when
your machine has pretty small size of memory.

As for the merit 2), you don't get first rows first for the query
unfortunately.
According to the Commlog
declare "SQL_ ... 0.282sec
fetch the 1st 1000 rows 431.453sec
fetch the rest of the rows 29.89sec

Total throughput 461.625sec may be longer than that of the same query
without
using DECLARE/FETCH mode.

regards,
Hiroshi Inoue



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

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