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

>
> Adam
> Thank you for the suggestions Your assumptions are correct. Medical
> Facility is < 50 and Patients > 80k.
> Using your example, the SP now runs in a few hundred milliseconds.

Is it logically equivalent to what you were trying to do? I am pretty
sure it is, but the nested subselects and the fact that you used the
same table twice in the query made the aim a bit difficult to determine.

> I
> have not yet defined any indexes.

Defining the additional upper lastname field and creating the index on
that would probably give you results under 10ms (on good hardware),
but would make the insert or modification of a record marginally more
expensive (although probably not measurable).

Depending on the expected load of the server and the number of times
the stored procedure will be run, you will need to determine whether
you need that additional performance.

Adam