Re: Slowing other users/sessions - resource mgt

"Joe Smith" <nospam_at_nospam.com> wrote in message news:423865ac$0$19348$8fcfb975_at_news.wanadoo.fr...
> 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.
>
>

Why do you need the count ? Does the select count use an index scan ? How big is the table ? Received on Wed Mar 16 2005 - 16:06:43 CST