Thread: 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
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 > >
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
But doesn't turning it off cause the entire recordset to come back at once?
Patrick Hatcher
David Gardner
<david.gardner@yu
caipaco.com> To
Patrick Hatcher
02/28/07 06:02 PM <PHatcher@macys.com>
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
>
>
> 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
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',user='',passwd='xxxxx'
[0.000]
onlyread='0',protocol='7.4',showoid='0',fakeoidindex='0',showsystable='0'
[0.000] conn_settings='',conn_encoding='(null)'
[0.000] translation_dll='',translation_option=''
Patrick Hatcher
Development Manager Analytics/MIO
Macys.com
Hiroshi Inoue
<inoue@tpf.co.jp>
To
02/28/07 08:10 PM Patrick Hatcher
<PHatcher@macys.com>
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
> 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',user='',passwd='xxxxx' > [0.000] > onlyread='0',protocol='7.4',showoid='0',fakeoidindex='0',showsystable='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
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