Helen,

Thanks for the comprehensive reply.

>Not sure what you think is the problem about "consuming a
>transaction". Every conversation between the client and the server
>"consumes" a transaction - even a read-only select. But there is no box of
>transactions sitting over there that - one day - will all be used up. Your
>concern with OAT is to make sure that all started transactions will finish
>(commit or rollback) with the minimum reasonable duration for the task they
>are doing.

Why I felt that consuming a transaction was a problem is as follows.
After much sweeping through my code I have ensured that OAT is not getting "stuck" by making sure that a commit or rollback happens at the earliest possible time.

However I noticed the bigger the difference between OAT and Next transaction the degradation in performance becomes more acute.

I am using this select count every twenty seconds from the client. Sometimes twenty or thirty clients will be running so sometimes the difference was growing to more than 3000 or 4000 but within around 3 or 4 minutes it would come down to a hundred or so.

So I just thought if there was some way to do this select count without "consuming a transaction" the performance degradation will not be very bad.

Simplistic thinking <g> .

Hmm If this is so I have another question but I think I will break that of into another thread by posting a seprate question

>Just understand that select count is ALWAYS a costly operation - nothing to
>do with the transaction - the only way the server can give you a rowcount
>is to walk the entire table and COUNT the rows (like beans).

Hmm, Again this raises another question but again I will break it out into another thread.

>Helen