Doug Chamberlin wrote:

> jeff_j_dunlap wrote:
> > "Doug Chamberlin" wrote in message
> >> Try changing your test to start a transaction and commit it each time.
> >> Should run quite fast.
> >
> > I would definitely do this if I were inserting, updating or deleting.
> > But is a new transaction and commit necessary for read-only access mode /
> > read committed isolation?
> > In my testing, creating a new transaction and
> > statement
> > was noticably slower. In both cases, I did not experience memory
> > leaks and
> > the data returned was accurate.
>
> I'm quite surprised your testing has shown it to make that much difference.
>
> > But if it is required to create a
> > transaction and commit it for read-only transactions, I will do it.
>
> No, it is not necessary. In general, it is not good practice to keep any
> transaction active for any longer than it needs to complete its work.
> However, you have a special case that you have researched and tested
> thoroughly, so stick with what you have found to work.
>
> You have already tapped into the best authorities on this subject, so
> nothing I say should dissuade you.

But perhaps it would be a good idea to commit and reopen that
transaction once in a while, perhaps once each night or once an hour?

I guess a timer event or scheduled job could handle that.

Kjell
--
--------------------------------------
Kjell Rilbe
DataDIA AB
E-post: kjell@...
Telefon: 08-761 06 55
Mobil: 0733-44 24 64