Hi there. I have a select query on a Firebird 1.5 Super Server database
that is running slower than I expected. The database has 60 tables, and I
am constructing the following query through a stored procedure:

SELECT
JOB.JOB_ID,
CLIENT.CLIENT_NAME,
CLIENT_SITE.SITE_DESC,
JOB.ENTERED_TS,
JOB_STATUS.JS_NAME,
CHARGE_TYPE.CT_NAME,
JOB.JOB_DESC_SHORT
FROM JOB
INNER JOIN CLIENT ON (JOB.FK_CLIENT_ID = CLIENT.CLIENT_ID)
LEFT OUTER JOIN CLIENT_SITE ON (JOB.FK_SITE_ID = CLIENT_SITE.SITE_ID)
INNER JOIN JOB_STATUS ON (JOB.FK_JS_ID = JOB_STATUS.JS_ID)
INNER JOIN JOB_PRIORITY ON (JOB.FK_JP_ID = JOB_PRIORITY.JP_ID)
LEFT OUTER JOIN JOB_TECH ON (JOB.JOB_ID = JOB_TECH.JOB_ID)
INNER JOIN CHARGE_TYPE ON (JOB.FK_CHARGETYPE_ID = CHARGE_TYPE.CT_ID)

The JOB table has about 140,000 rows in it, about 600 in the client table,
etc. I've reviewed the performance information given in IBExpert and it
reports the following:

Plan
PLAN JOIN (JOIN (JOIN (JOIN (JOIN (CLIENT NATURAL,JOB INDEX
(FK_JOB_2)),CLIENT_SITE INDEX (PK_CLIENT_SITE)),JOIN (JOB_STATUS INDEX
(RDB$PRIMARY25),JOB_PRIORITY INDEX (RDB$PRIMARY23))),JOB_TECH INDEX
(FK_JOB_TECH_2)),CHARGE_TYPE INDEX (PK_CHARGE_TYPE))

Adapted Plan
PLAN JOIN (JOIN (JOIN (JOIN (JOIN (CLIENT NATURAL,JOB INDEX
(FK_JOB_2)),CLIENT_SITE INDEX (PK_CLIENT_SITE)),JOIN (JOB_STATUS INDEX
(INTEG_104),JOB_PRIORITY INDEX (INTEG_100))),JOB_TECH INDEX
(FK_JOB_TECH_2)),CHARGE_TYPE INDEX (PK_CHARGE_TYPE))

------ Performance info ------
Prepare time = 0ms
Execute time = 5s 937ms
Avg fetch time = 197.90 ms
Current memory = 6,316,872
Max memory = 11,007,584
Memory buffers = 2,048
Reads from disk to cache = 25,358
Writes from cache to disk = 0

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.

Does anything in this look obviously wrong that I should address? Otherwise
what tricks & techniques do I have available to optimize this query?

Thanks in advance for any advice.

Myles

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