Re: Cursors performance (was: Re: [PERFORM] Terrible - Mailing list pgsql-jdbc

Ok, given that there are 5000 rows, the jdbc driver will actually fetch
all 5000 when you do the fetch, so is it the speed of the connection, or
the actual fetch that is taking the time, again, check the server logs
for duration.

Dave
On Fri, 2004-07-09 at 17:03, Bill Chandler wrote:
> 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
> > >
> > >
> > >
> > >
> > >
> > >
> > --
> > 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
>
>
>
> !DSPAM:40ef083f256273772718645!
>
>
--
Dave Cramer
519 939 0336
ICQ # 14675561