Re: Cursors performance (was: Re: [PERFORM] Terrible performance after deleting/recreating indexes) - Mailing list pgsql-jdbc
From | Bill Chandler |
---|---|
Subject | Re: Cursors performance (was: Re: [PERFORM] Terrible performance after deleting/recreating indexes) |
Date | |
Msg-id | 20040709210348.1813.qmail@web51406.mail.yahoo.com Whole thread Raw |
In response to | Re: Cursors performance (was: Re: [PERFORM] Terrible (Dave Cramer <pg@fastcrypt.com>) |
Responses |
Re: Cursors performance (was: Re: [PERFORM] Terrible
(Dave Cramer <pg@fastcrypt.com>)
|
List | pgsql-jdbc |
Using psql it peforms exactly as I'd expect. The rows get printed out to stdout, I hold down the space bar to keep everything scrolling and as every 5000 rows go by I see a new fetch statement logged in the server log. The results from this statement seem to come back instaneously and the output starts scrolling again immediately. Whole query takes a few minutes to complete. I seems like it has something to do w/ my JDBC app but I can't think for the life of me what I might have changed. Anyway, there's only the setFetchSize(5000) and the setAutoCommit(false) that are relevant to cursors, right? And those have been in there for weeks. Bill --- Dave Cramer <pg@fastcrypt.com> wrote: > Bill, > > What happens if you do this in psql, also you can > turn on duration > logging in the backend and log the queries. > > dave > On Fri, 2004-07-09 at 16:24, Bill Chandler wrote: > > Thanks to all who have responded. I now think my > > problem is not related to deleting/recreating > indexes. > > Somehow it is related to JDBC cursors. It appears > > that what is happening is that since I'm using > > a fetch size of 5000, the command: > > > > FETCH FORWARD 5000 FROM JDBC_CURS_1 > > > > is being repeatedly sent to the server as I > process > > the result set from my query. Each time this > command > > is sent it it takes about 5 minutes to return > which is > > about the amount of time the whole query took to > > complete before the performance degredation. So in > > other words it looks as if the full select is > being > > rerun on each fetch. > > > > Now the mystery is why is this happening all of > the > > sudden? I have been running w/ fetch size set to > 5000 > > for the last couple of weeks and it did not appear > to > > be doing this (i.e. re-running the entire select > > statement again). Is this what I should expect > when > > using cursors? I would have thought that the > server > > should "remember" where it left off in the query > since > > the last fetch and continue from there. > > > > Could I have inadvertently changed a parameter > > somewhere that would cause this behavior? > > > > thanks, > > > > Bill > > > > __________________________________________________ > > Do You Yahoo!? > > Tired of spam? Yahoo! Mail has the best spam > protection around > > http://mail.yahoo.com > > > > ---------------------------(end of > broadcast)--------------------------- > > TIP 3: if posting/reading through Usenet, please > send an appropriate > > subscribe-nomail command to > majordomo@postgresql.org so that your > > message can get through to the mailing list > cleanly > > > > > > > > !DSPAM:40eefff6170301475214189! > > > > > -- > Dave Cramer > 519 939 0336 > ICQ # 14675561 > > __________________________________________________ Do You Yahoo!? Tired of spam? Yahoo! Mail has the best spam protection around http://mail.yahoo.com
pgsql-jdbc by date: