At 17:18 03.01.2003 +0530, you wrote:

>Hello,
> i am using ib_query for fetching this record.This is my Query.
>SELECT P.OPNUMBER, P.PATIENTNAME, P.PATIENTADDRESS1, P.PATIENTADDRESS2,
>P.PATIENTGUARDIAN, P.DOCTORCODE, P.AREACODE, P.REGISTRATIONDATE,
>P.PATIENTAGE, P.PATIENTAGETYPE, P.PATIENTSEX,
>P.CUSTOMERTYPE, P.PRIVILEGEDCUSTOMERCODE, I.IPNUMBER, I.IPSERIALNUMBER,
>I.DISCHARGED, I.ADMITTINGDATE, I.PATIENTCOMPLAINT, I.BEDNUMBER,
>I.CURRENTROOMCODE, I.ADMITTINGDOCTOR,
>D.DISCHARGEDATE, R.ROOMNUMBER, A.AREANAME, E.EMPLOYEENAME FROM
>(((((PATIENTMASTER P LEFT OUTER JOIN IPMASTER I ON P.OPNUMBER = I.OPNUMBER
>AND I.IPSERIALNUMBER = (SELECT MAX(M.IPSERIALNUMBER) FROM IPMASTER M WHERE
>M.OPNUMBER = P.OPNUMBER))
>LEFT OUTER JOIN DISCHARGEDETAILS D ON I.IPNUMBER = D.IPNUMBER AND
>I.IPSERIALNUMBER = D.IPSERIALNUMBER)
>LEFT OUTER JOIN ROOMMASTER R ON I.CURRENTROOMCODE = R.ROOMCODE ) LEFT OUTER
>JOIN AREAMASTER A ON P.AREACODE = A.AREACODE)
>INNER JOIN EMPLOYEEMASTER E ON P.DOCTORCODE = E.EMPLOYEECODE)
>
>This is the plan for this query:
>PLAN (M INDEX (RDB$FOREIGN237))
>PLAN MERGE (SORT (E NATURAL),SORT (JOIN (JOIN (JOIN (JOIN (P NATURAL,I
>INDEX (RDB$FOREIGN237)),D INDEX (RDB$PRIMARY71)),R INDEX (RDB$PRIMARY41)),A
>INDEX (RDB$PRIMARY34))))

I see two potential problems here. First:

AND I.IPSERIALNUMBER = (SELECT MAX(M.IPSERIALNUMBER) FROM IPMASTER M WHERE
M.OPNUMBER = P.OPNUMBER)

In general I would recommend changing this to

AND EXISTS(SELECT 1 FROM IPMASTER M WHERE M.OPNUMBER = P.OPNUMBER AND
I.IPSERIALNUMBER = M.IPSERIALNUMBER AND NOT EXISTS(SELECT 1 FROM IPMASTER
M2 WHERE M2.OPNUMBER = M.OPNUMBER AND M2.IPSERIALNUMBER > M.IPSERIALNUMBER))
(If you know that there always will be a matching IPSerialnumber, you can
skip the first part)

However, since this part is already using an index, I am uncertain as to
how much speed increase you will get. Please report back how much
improvement you get from this change.

The other potential problem is that both EmployeeMaster and PatientMaster
is using natural order for the join. Since you have no limiting where
clause, one of them will have to be natural, but both are exaggerating. So
unless we are talking of very few employees, I would recommend you to add
an index for either DoctorCode or EmployeeCode.

Another thing you may consider, is to use subselects rather than outer
joins for DischargeDetails, RoomMaster and AreaCode. Again, I am a bit
uncertain whether and how much faster it will be.

HTH,

Set

- I support Firebird, I am a FirebirdSQL Foundation member.
- Join today at http://www.FirebirdSQL.org/Foundation