Hi Myles!

Consider changing to

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)
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)
INNER JOIN CHARGE_TYPE ON (JOB.FK_CHARGETYPE_ID = CHARGE_TYPE.CT_ID)
LEFT OUTER JOIN CLIENT_SITE ON (JOB.FK_SITE_ID = CLIENT_SITE.SITE_ID)
LEFT OUTER JOIN JOB_TECH ON (JOB.JOB_ID = JOB_TECH.JOB_ID)

The reason being that having LEFT JOIN higher up in the SELECT reduces
the options of the optimizer when deciding the plan (at least for Fb
1.5, and I haven't heard that this has changed with later versions, but
do not know).

I don't know whether this will improve the speed or not, to me it seems
like a good plan for your query. Generally, I find that NATURAL on the
first table in a plan isn't disastrous unless the table contains lots of
records (and if you only have 600 records in CLIENT, this is not a
problem). Hence, check for poor selectivity of other indexes in the plan
- or - if your indexes consists of several fields - the selectivity of
the part of the plan that Firebird is able to use.

Without any indexed field compared to a constant value, Firebird has to
go NATURAL for at least one table, but it might be that the optimizer
can choose a better table than CLIENT if you do the change I recommended
above.

Set

Myles Wakeham wrote:

> 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