Re: Cursors performance (was: Re: [PERFORM] Terrible

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