Subject Re: [ib-support] Performance Large Query / Procedure
Author Jason Chapman (JAC2)
> We have a client with about 10 users. Whenever one of the clients
> runs a complex query (a few joins and many records) the other clients
> all complain that the system virtually stops responding. They are
> running Windows NT.
> The same thing happens when they run some procedures that take a
> couple of hours to run. I have looked at the 'performance' of the
> procedures in Quickdesk and it shows that it is using all the
> appropriate indexes.
> Is this an Interbase problem, a design problem or a Windows problem?
Most probably it's IB's problem, which means it's moved to a design problem
/ solution. Typical problems occur when you over index tables combined with
too many outer joins cause the optimiser to make some very strange
decisions. The net effect is that while it is merging indices etc, it hogs
huge amounts of CPU thus depriving other users.

When we come across the same problems we utilise:
1) Forcing better plans either using plans or better stoping the optimser
from electing indices (e.g. where = b.fk_id+0).
2) Looking at the different elements of the operations and finding the
killer sections and re-engineering them.
3) Break huge stored procedures into smaller units of work.

I'm not taking a swipe at IB/FB for this, it's just the way it is until the
optimiser / engine is looked at by someone better than I. Without looking
at your code / db etc it is not possible to be more specific.

What I have found when QA'ing newbies to IB, they often index everything and
lots of fields indexed in singly and in combinations, this really confuses

Maybe a little more info...#