> On 29-Mar-2008 10:00:05, firebird-support@yahoogroups.com wrote:
> > The performance results are reporting that all queries are indexed
> > reads with the exception of the CLIENT join, which is showing as
> > 'natural' and reporting as a NON-INDEXED read. I suspect this is
> > where the performance problem is. I've checked the CLIENT table and
> > verified indexes, etc. on it, but for some reason the optimizer is
> > not using the indexes.
>
> This is exactly as expected.
>
> You don't have any criteria (i.e. WHERE) which would reduce the number
> of Client table rows. Accordingly, you are tell the engine that you
> want all Client rows, so the engine is reading the Client table front
> to back (storage order)...

I neglected to include the WHERE clause because that is dynamically added in
the stored procedure based on the different query criteria that I allow the
user to select from. However you are correct in that it is highly possible
that a user will select to find ALL jobs of a particular type regardless of
the client that it is for.

Is there a better way that I can construct the query to increase performance
here?

Myles




===============================
Myles Wakeham
Director of Engineering
Tech Solutions USA, Inc.
Scottsdale, Arizona USA
www.techsolusa.com
Phone +1-480-451-7440