Subject Re: Optimize SP
Author sasidhardoc

> 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. 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(?). Thanks for the advice and help.