Re: [PERFORM] Cursors performance
От | Bill Chandler |
---|---|
Тема | Re: [PERFORM] Cursors performance |
Дата | |
Msg-id | 20040712180730.37055.qmail@web51407.mail.yahoo.com обсуждение исходный текст |
Ответ на | Re: Cursors performance (Mark Kirkwood <markir@coretech.co.nz>) |
Ответы |
Re: [PERFORM] Cursors performance
|
Список | pgsql-jdbc |
Thanks, Will try this test (I'm assuming you mean to say to set fetch size of 1 and rerun on both JDBC and psql). BTW, here is another clue: I only get the JDBC performance degradation when I include the "real_name like 'NEPOOL%REAL%'" clause. I've tried re-ordering too: i.e. putting this clause first in the statement, last in the statement, etc. Doesn't seem to make any difference. real_name is a varchar(64). There is a unique index on it. thanks, Bill --- Mark Kirkwood <markir@coretech.co.nz> wrote: > Might be worth doing a little test: > > i) modify your code to fetch 1 row at a time > ii) set log_duration=true in your postgresql.conf > (as the other posters > have suggested) > > Then compare with running the query in psql. > > regards > > Mark > > > > 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 8: explain analyze is your friend > > > > > > ---------------------------(end of > broadcast)--------------------------- > TIP 5: Have you checked our extensive FAQ? > > > http://www.postgresql.org/docs/faqs/FAQ.html > __________________________________ Do you Yahoo!? Yahoo! Mail - 50x more storage than other providers! http://promotions.yahoo.com/new_mail
В списке pgsql-jdbc по дате отправления: