Subject Re: [ib-support] Utility to find slow queries?
Author Mark Patterson
Jason Frey wrote:
(And thanks for writing)
> Actually, a tool I've found quite useful for tuning/optimizing is IBConsole
> (I know, boo hiss), though I know IBExpert will also do what I use IBConsole
> for.

That comes with Interbase, IIRC. I'll have to get the D6 disk out to do that. Groan.

> Anyways, have it print out a statistical summary of your database. Check
> the index definitions to make sure you have a somewhat even distribution in
> your buckets... Or at least, don't have all of your data in one bucket.
> That's a bad thing. An example of an index that would have a bad
> distribution would be on an integer/varchar field which only holds a true or
> a false value. My rule of thumb is to not use indexes with bad
> distributions, because IB/FB will be tempted to use it.

On of the queries uses an index with an uneven distribute: status of a piece of
work throught the factory. By far the greatest number of them are in ARCHIVED
(4) status. So the query has to ask WHERE STATUS <= 3, which I imagine
efficently reduces the number of records to a fairly comstant number, with the
efficiency not affected by the large number of records that fail that criterion.
But there is also another condition.

Could it be that the part that runs slow involves 2 simultaneous threads? We are
thinking of cutting it back to one thread.

A solution that I am puttng off is to split the offending table into 2 with
identical formats, one for work in progress, one for archived work. Is that
likely to be any help?