Thread: RE: [INTERFACES] fetching way too much or too often???
well, with use declare / fetch turned OFF on everyone's machine, the activity on the server per user action appears to be less than 1/5th! (i.e., greatly reduced!). we have been running nowfor an hour with no lockups -- that hasn't happened yet today! but i also made another change at the same time as removing declare/fetch -- i increased the buffers in the startup script on the server from 256 to 512 (and then restarted). earlier today i removed the -B option altogether,letting it use default 64 buffers -- and we started locking up like crazy! so i don't know which is making the difference -- the buffers or no declare/fetch. . . but in any case, for the benefit of all who read this, unchecking declare / fetch SIGNIFICANTLY reduces the activity between the client and a postgres server!!! jt -----Original Message----- From: Ken J. Wright [SMTP:ken@ori-ind.com] Sent: Wednesday, June 30, 1999 2:30 PM To: JT Kirkpatrick Subject: Re: [INTERFACES] fetching way too much or too often??? At 14:11 06/30/1999 -0400, you wrote: >we have about 20 folks linked to a postgres 6.4.2 database through >msaccess97 and the latest odbc driver. we have declare/fetch turned on. > we are having a problem with the database locking up often! i am trying >to eliminate the possibilities / theories, and i am on the declare/fetch >process now. > >when a user selects one record, as a watch on the debug window (started >postmaster w/ debug 3), it is processing many (25-50? who can count that >fast??) fetch 100's -- but they only selected one record from one table! > why so many fetch 100's?? should i turn off declare / fetch on everyone's >odbc?? YES! turn it off! I think that was before Byron had the cursor library included. It has little to no value at this point. Ken
JT Kirkpatrick wrote: > well, with use declare / fetch turned OFF on everyone's machine, the > activity on the server per user action appears to be less than 1/5th! > (i.e., greatly reduced!). we have been running now for an hour with no > lockups -- that hasn't happened yet today! but i also made another change > at the same time as removing declare/fetch -- i increased the buffers in > the startup script on the server from 256 to 512 (and then restarted). > earlier today i removed the -B option altogether, letting it use default > 64 buffers -- and we started locking up like crazy! so i don't know which > is making the difference -- the buffers or no declare/fetch. . . but in > any case, for the benefit of all who read this, unchecking declare / fetch > SIGNIFICANTLY reduces the activity between the client and a postgres > server!!! > The reason the Declare/Fetch option exists is for the kind of scenario where you need to fetch, oh let's say, 1,000,000 rows, which is not uncommon in Data Mining applications. Without the Declare/Fetch, your PC would very likely run out of memory, because the driver would try to cache the entire result set ( all 1,000,000 rows) in its own memory. With the Declare/Fetch on, the driver only caches 100 rows at a time. When the cache has been depleted, another 100 rows are fetched. The 100 rows is actually configurable on the driver options dialog (cache size). So, I'm glad you're problem is fixed. But I just wanted you to know why the Declare/Fetch option is still important. Byron