--- In firebird-support@yahoogroups.com, "sasidhardoc"
<madhusasidhar@...> wrote:

>
> Adam
>
> > Is it logically equivalent to what you were trying to do?
> Yes. The actual query is a little different (exclude deleted patients,
> exlude expired patients and so on).
>
> > Defining the additional upper lastname field and creating the index on
> > that would probably give you results under 10ms
> On my Athlon 3200 with 1G ram (V 2 RC5 superserver), using the index I
> get "Prepared in 0.032 sec, processed in 0.281 sec, rows fetched =
> 1346". Using "LIKE (:SEARCHSTRING)||'%" which apparently does not use
> the index, the processing time is closer to 1 sec.

No, Firebird WILL use an index if the query can be changed to a
'STARTING WITH' type query. The reason your index is not used is
because you are looking for values of UPPER(EPE.PERSON_LASTNAME). You
do not have an index on that expression (and you can't add an
expression index until Firebird 2), so unless you use the additional
field approach I suggested, I am afraid you are limited to an
expensive table scan.

> The Db will
> eventually be on a dual Xeon server with 4 G ram. But on some of these
> SP, I will take all the performance I can get.
>
> So, generally, JOIN seems to be preferable to a SUBQUERY - if the
> SUBQUERY resultset is large(?).

Yes, subqueries tend to be executed for each potential record in the
outer select, where joins leave it to the optimiser to decide which
way to try it. I would stick to joins where possible for readability
if no other reason.

Adam