Slowing other users/sessions - resource mgt

Hi everybody,

this is the scenario I'm testing:

One application writes, via a pool of jdbc connections, 40 records per second (one write=one commit), limited by the disk speed (I know because I'm asking to write the double, 80).

When I do a count on the table (select count(*) from myTable), the query runs for more than 15 minutes.

However, when the application is stopped, this count can be done in less than 6 seconds.

I'd like to prioratize the count, so response time is better. So far, I've tried:

• Locking the table before the count, counting, and releasing with a commit. It works ok, but this is not acceptable if the count starts taking longer, as the application is completely frozen in the meantime.
• Using the resource manager: the problem is that in this case, CPU is not limiting (less than 5% used), so the manager is not used. I've tried giving the pool users 1% CPU, and the user performing the count 99%: no improvement.
• I've also looked at the use of profiles, but the type of limitations they provide (logical_reads_per_session, logical_reads_per_call, etc) doesn't seem adapted to the connection pool/small transaction case.

Do you have any other ideas?

Thanks in advance. Received on Wed Mar 16 2005 - 10:58:21 CST