Re: Cursors performance (was: Re: [PERFORM] Terrible performance after deleting/recreating indexes)

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(at)fastcrypt(dot)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(at)postgresql(dot)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