>How can I make this SQL execute faster? 2.5 seconds is way too long.

>To solve the NATURAL (Slow) access of records via SELECT, I usually just
>create some appropriate indexes and retry, until FB is fast-as-lighting
>again. Some how, I'm tied up with this SQL which I cannot seem to get
>going fast, no matter what indexing I try.
>
>-----------------------------------------------
>Here is the SQL I need to execute quickly:
>
>SELECT DISTINCT
> a.FLD_NM, b.FLD_DSPLY_NM, d.PAR_GUID
>FROM
> JET_CHG_LOG a
> LEFT JOIN JET_FLD_NMS b ON a.TBL_NM = b.TBL_NM AND a.FLD_NM = b.FLD_NM
> LEFT JOIN JET_SAV_PNT d ON a.PAR_GUID = d.OBJ_GUID
>WHERE
> d.PAR_GUID = 'C8B3B24AC7214A8084849D427F631102'
>ORDER BY b.FLD_DSPLY_NM
>
>PLAN SORT (SORT (JOIN (JOIN (A NATURAL, B INDEX (UNQ_JET_FLD_NMS_2)),
>D INDEX (PK_JET_SAV_PNT))))

When I encounter a slow select, I normally start by looking at the query to see if it can be improved. I rarely create new indexes, since that may influence other queries as well.

In your particular case, Steve spotted the problem - you use LEFT JOIN to D, yet still refer to D in the WHERE clause - which - in your case - means that you're using an INNER JOIN, but hiding it (and restricting the optimizers choices) in a LEFT JOIN. Generally, when using LEFT JOIN, you force the optimizer to access the tables in a particular order, in your case A must be before B and also A must be before D (the Firebird versions I've used also enforce B before D, but I see no logical reason for this). So what parts of the query is available/appropriate when choosing an index for A? I'd say that's limited to

SELECT DISTINCT
a.FLD_NM, b.FLD_DSPLY_NM, d.PAR_GUID
FROM
JET_CHG_LOG a

I.e. there's no field at all to use an index for. You don't refer to A anywhere in your where clause and due to the LEFT JOIN, no fields referred to in the left join is of any use (it is useful for the indexes for B and D, but not for A). So change your query to

SELECT DISTINCT
a.FLD_NM, b.FLD_DSPLY_NM, d.PAR_GUID
FROM
JET_CHG_LOG a
JOIN JET_SAV_PNT d ON a.PAR_GUID = d.OBJ_GUID
(LEFT) JOIN JET_FLD_NMS b ON a.TBL_NM = b.TBL_NM AND a.FLD_NM = b.FLD_NM
WHERE
d.PAR_GUID = 'C8B3B24AC7214A8084849D427F631102'
ORDER BY b.FLD_DSPLY_NM

I also put parenthesis around the left join to B since inner joins are generally preferable to outer (left/right/full/cross) joins and your use of D indicates that you may be using LEFT JOIN exceedingly. If it is possible that there will be no matching record in B, but that you still want the row returned with values from A and D, then remove the parenthesis, if there will always be one or more matching rows in B, then remove (LEFT). LEFT (or RIGHT) JOIN can sometimes be useful for optimization, but only when you see that the optimizer gets it wrong and that LEFT would eliminate the possibility to use one index incorrectly used.

Using LEFT JOIN as it is intended to be used, (i.e. for tables that may or may not contain matching rows) is normally OK, but then you would typically put all restrictions regarding the table in the JOIN clause and rarely refer to the table in the WHERE clause.

HTH,
Set