Hi Arno,

>Please show us also the PLAN that's returned by the query.
>
>
>

The plan for the first (fast query) is thus :

PLAN SORT ((P NATURAL))
PLAN (S INDEX (RDB$PRIMARY14))
PLAN (T INDEX (P_CNT_PRFCD_IDX))
PLAN (T INDEX (P_CNT_PRFCD_IDX))
PLAN (T INDEX (P_CNT_PRFCD_IDX))
PLAN (T INDEX (P_CNT_PRFCD_IDX))
PLAN (T INDEX (P_CNT_PRFCD_IDX))
PLAN (T INDEX (P_CNT_PRFCD_IDX))
PLAN (B INDEX (I_PRF_IDX))
PLAN (T INDEX (P_CNT_PRFCD_IDX))
PLAN (T INDEX (P_CNT_PRFCD_IDX,P_CNT_STATUS_IDX,P_CNT_STATUS_IDX))
PLAN (B INDEX (I_PRF_IDX))

The plan for the second (slow query) is thus :
(here I have "anded" the B.FPRFSPOKENDT field to the above query.)

PLAN SORT ((P NATURAL))
PLAN (S INDEX (RDB$PRIMARY14))
PLAN (T INDEX (P_CNT_PRFCD_IDX))
PLAN (T INDEX (P_CNT_PRFCD_IDX))
PLAN (T INDEX (P_CNT_PRFCD_IDX))
PLAN (T INDEX (P_CNT_PRFCD_IDX))
PLAN (T INDEX (P_CNT_PRFCD_IDX))
PLAN (T INDEX (P_CNT_PRFCD_IDX))
PLAN (B INDEX (I_PRF_IDX))
PLAN (T INDEX (P_CNT_PRFCD_IDX))
PLAN (T INDEX (P_CNT_PRFCD_IDX,P_CNT_STATUS_IDX,P_CNT_STATUS_IDX))
PLAN (B INDEX (I_PRF_IDX,I_SPOKENDATE_IDX))

>I assume the problem is that the index on "SPOKEN_DATE" and if there is on
>"FPRFCD" and "OFR_CD" are "AND"-ed together. First all recordnumbers are
>read (in the index) that meet your criteria on SPOKEN_DATE (that includes
>all people) and all recordsnumbers matching FPRFCD are read. Those are
>"AND"-ed together to get the final recordnumbers which are used to fetch the
>data. This happends for every record (because this happends in a EXISTS).
>
>To speed up the query in the EXISTS part you certainly benifit by creating a
>compound index on (FPRFCD, SPOKEN_DATE) or even (FPRFCD, OFR_CD,
>SPOKEN_DATE). Note that the order in the compound index is very importand.
>
>

I tried a compound index I_PRFSPOKEN_IDX on BDFLOW(FPRFCD, FPRFSPOKENDT)
and now the query seems to be coming under control (7-10 secs). The new
plan is now :

PLAN SORT ((P NATURAL))
PLAN (S INDEX (RDB$PRIMARY14))
PLAN (T INDEX (P_CNT_PRFCD_IDX))
PLAN (T INDEX (P_CNT_PRFCD_IDX))
PLAN (T INDEX (P_CNT_PRFCD_IDX))
PLAN (T INDEX (P_CNT_PRFCD_IDX))
PLAN (T INDEX (P_CNT_PRFCD_IDX))
PLAN (T INDEX (P_CNT_PRFCD_IDX))
PLAN (B INDEX (I_PRF_IDX))
PLAN (T INDEX (P_CNT_PRFCD_IDX))
PLAN (T INDEX (P_CNT_PRFCD_IDX,P_CNT_STATUS_IDX,P_CNT_STATUS_IDX))
PLAN (B INDEX (I_PRF_SPOKEN_IDX))

What I would like to know is that why is this such a problem with only
the date field. Is this a problem with the way FB stores date internally
? Now all queries are coming back in real time. Why is it that the
timestamp field queries in the detail table should behave in such a
peculiar fashion.

>Also if you've a lot of NULLs in SPOKEN_DATE it's interesting to change
>SPOKEN_DATE >= '2004-31-01' => SPOKEN_DATE BETWEEN '2004-31-01' and
>'2999-01-01'. (Currently NULLs are always stored at the end of the index and
>this avoids scanning NULLs).
>Try to use a BETWEEN whenever possible, because it's much more
>effective/selective then "greater than" or "less than".
>
>

Spoken_date is a not null field. However using between is definately
making this fast. If I drop the index created above "between" is as poor
as comparison operators earlier. However with the index, between is
returning results in < 5 secs.

>Regards,
>Arno Brinkman
>ABVisie
>
>

Thanks for the help.

Regards,


RP