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

>
> The SP below, slows down to several minutes when the tables become
> larger. How would I optimize the query. The query plan is below.
>
> BEGIN
> FOR SELECT ENTY_PATIENT.PATIENT_ID PRIMARYKEY,
> ENTY_PERSON.PERSON_LASTNAME || ', ' || ENTY_PERSON.PERSON_FIRSTNAME
> "SEARCH RESULT"
> FROM ENTY_PATIENT INNER JOIN
> ENTY_PERSON ON ENTY_PATIENT.ENTITY_ID = ENTY_PERSON.ENTITY_ID
> WHERE (ENTY_PERSON.ENTITY_ID
> IN(SELECT ENTITY_ID FROM ENTY_PATIENT
> WHERE (PATIENT_ID IN (SELECT PATIENT_ID FROM

LINK_MEDICALFACILITY_PATIENT

> WHERE (MEDICALFACILITY_ID = :MEDICALFACILITY_ID_IN)))))
> AND (lower(ENTY_PERSON.PERSON_LASTNAME) LIKE lower(:SEARCHSTRING_IN)
> || '%')
> INTO
> :PRIMARYKEY,
> :"SEARCH RESULT"
> DO
> SUSPEND;
> END
>

There is no difference optimising a stored procedure query from a
normal query. Get the query to behave how you like before wrapping it
in a stored procedure.

This one is difficult because the SQL itself seems to be
overcomplicated. You have used effectively a re-entrant join using
nested subselects, yet you don't need to unless I am reading your
original query wrong.

Firstly, I will rewrite your query as I understand it without the
nested subselects.

SELECT ENTY_PATIENT.PATIENT_ID, ENTY_PERSON.PERSON_LASTNAME || ', ' ||
ENTY_PERSON.PERSON_FIRSTNAME
FROM LINK_MEDICALFACILITY_PATIENT LMP
JOIN ENTY_PATIENT EPA1 ON (LMP.PATIENT_ID = EPA1.PATIENT_ID)
JOIN ENTY_PERSON EPE ON (EPA1.ENTITY_ID = EPE.ENTITY_ID)
WHERE LMP.MEDICALFACILITY_ID = :MEDICALFACILITY_ID_IN
AND UPPER(EPE.PERSON_LASTNAME) STARTING WITH UPPER(:SEARCHSTRING_IN)

I have switched around part of your where clause because:

UPPER is included in the engine of 1.5+ (maybe even earlier), whereas
lower is available only in a UDF library.
Starting with is easier to read than "Like lower(:searchstring_in) || '%'"

Now a couple of observations.

If you have a foreign key constraint on
LINK_MEDICALFACILITY_PATIENT.MEDICALFACILITY_ID, than the underlying
index will be used. If your database contains very few MEDICALFACILITY
records, this index may be worthless or even counter productive.

You are also doing a case insensitive search on
ENTY_PERSON.PERSON_LASTNAME, but you achieve this using an expression,
which means that no index can be used. (Note that in FB2+, you will be
able to add an index on such an expression). One possible solution is
to create a field in ENTY_PERSON called PERSON_LASTNAME_UPPER. Define
a before insert or update trigger that contains the line.

NEW.PERSON_LASTNAME_UPPER = UPPER(NEW.PERSON_LASTNAME);

You can create an index on this field and can use it in your case
insensitive searches.

SELECT ENTY_PATIENT.PATIENT_ID, ENTY_PERSON.PERSON_LASTNAME || ', ' ||
ENTY_PERSON.PERSON_FIRSTNAME
FROM LINK_MEDICALFACILITY_PATIENT LMP
JOIN ENTY_PATIENT EPA1 ON (LMP.PATIENT_ID = EPA1.PATIENT_ID)
JOIN ENTY_PERSON EPE ON (EPA1.ENTITY_ID = EPE.ENTITY_ID)
WHERE LMP.MEDICALFACILITY_ID = :MEDICALFACILITY_ID_IN
AND EPE.PERSON_LASTNAME_UPPER STARTING WITH UPPER(:SEARCHSTRING_IN)

I make the following assumptions.

MEDICALFACILITY -> relatively few records.
ENTY_PATIENT -> lots of records
ENTY_PERSON -> roughly same as enty_patient
LINK_MEDICALFACILITY_PATIENT -> only a few records per patient

If my assumptions are correct and if such a query were used an such an
indexed field were available, I would expect subsecond responses from
even a large database.

If the statistics are bad, it might try to use the index on
MEDICALFACILITY_ID which if my assumptions are right would be a bad
index to use. To avoid it, simply add 0 to the ID before comparisons
which will stop the optimiser from using the index.

SELECT ENTY_PATIENT.PATIENT_ID, ENTY_PERSON.PERSON_LASTNAME || ', ' ||
ENTY_PERSON.PERSON_FIRSTNAME
FROM LINK_MEDICALFACILITY_PATIENT LMP
JOIN ENTY_PATIENT EPA1 ON (LMP.PATIENT_ID = EPA1.PATIENT_ID)
JOIN ENTY_PERSON EPE ON (EPA1.ENTITY_ID = EPE.ENTITY_ID)
WHERE LMP.MEDICALFACILITY_ID+0 = :MEDICALFACILITY_ID_IN
AND EPE.PERSON_LASTNAME_UPPER STARTING WITH UPPER(:SEARCHSTRING_IN)


Adam