>

>select *;
>  from person p
> inner join referral r on p.cl_ref = r.cl_ref and !empty(r.re_date);
>      where gomonth(p.cl_dob, 216) > (select min(rr.re_date) from referral rr;
>                                             where rr.cl_ref = p.cl_ref and !empty(rr.re_date))
>

>
>According to Fox none of this is optimised, both cl_ref fields on each table are indexed.
>
>Can anyone suggest an alternative that may be quicker - or is there anything else I could do to speed up the query?

Sys(3054) has to be interpreted carefully <g>.
You probabably would get rushmore optimized filtering if you had an index on
r.re_date and switched to r.re_date<>{..} as empty is not rushmore-optimizable.
***WARNING*** Having sys(3054) state that the the table is optimized can actually be working slower if none or very few records are cought by that filter, especially if the table sits on another machine. Check the selectivity of such an index FIRST!

INdexing on "gomonth(p.cl_dob, 216)" or "cl_dob *and* moving the calculation into the select" are probably better for speeding up your query. Experiment <g>.

the index on cl_ref should be used, but will probably shown in the sys(3054)-line as used by the join, not by rushmore.

HTH

thomas