Hi,

> The following query runs fine. It returns in about 5 secs. However the
> moment I add a date field comparison it goes for a toss. I have
> especially found that using containing instead of like significantly
> improves the performance of this particular query. All fields in this
> query have been indexed and I keep dropping and recreating the indexes
> every week at the beginning of the week for safety sake.

<snip explanation part>

> Now the query takes about 20 secs to return. There are only a few
> hundred entries after 12/01/2004 (December 1, 2004). If the query is
> changed to compare dates before December 1, 2004 (B.SPOKEN_DATE <
> '12/01/2004 00:00:00') the performance degrade is worse. (About 480 secs
> and more). In both cases the total results thrown are the same Approx 34
> records. (i.e. 34 people have this kind of workflow entries before
> december 1, 2004 and nearly the same number have them after that date)

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

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.

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".

Regards,
Arno Brinkman
ABVisie

-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-
Firebird open source database (based on IB-OE) with many SQL-99 features :
http://www.firebirdsql.org
http://www.firebirdsql.info
http://www.fingerbird.de/
http://www.comunidade-firebird.org/

Support list for Interbase and Firebird users :
firebird-support@yahoogroups.com

Nederlandse firebird nieuwsgroep :
news://80.126.130.81