Posted by Robert Klemme on 01/25/06 14:03

Mike Read wrote:
> Hi Robert
>
>> I don't know your env and its requirements but to me the difference
>> between sub 1 sec and several seconds doesn't sound worth the effort
>> changing anything - unless, of course, you have lots of these short
>> queries executed in sequence and the difference sums up dramatically.
>>
>
> Yes there could well be a lot of the small queries.
>
>>
>> - scheduling: make sure the long runners are done during the night or
>> other time when the DB is mostly idle.
>>
>
> I'm trying to write some sort of queue to help with this but the
> chances are there will always be a long running query executing at a
> given time.
>
>> - distribution of data: either via some form of replication or by
>> moving data from one DB to a complete different system
>>
>
> We're looking at getting another server to handle the long queries
> so this might utilmately be the answer
>
>> - optimizing SQL: additional indexes, different query conditions etc.
>>
>
> We've pretty much done what we can but some queries will always need a
> full table scan.
>
> As all queries run at the same priority I was kind of expecting a
> 0.1 sec query to take approx 0.2 sec (rather than 10 secs as is
> happening) if another (long) query is running.
>
> As this isn't the case I presume there's some sort of
> overhead/cache/swapping occuring that I might have been able to
> reduce showhow.

My guess would be that your DB is IO bound during these phases, i.e. the
long running table scans eat up all the IO bandwidth and that's slowing
you down. I'd do some measurements to verify that before you change
anything.

Kind regards

robert

[Back to original message]