--- In ib-support@yahoogroups.com, Sivaraman Krishnan
<sivaraman@s...>
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)

Holy nested joins Batman!

> 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

This means you have no matching index for P:
create index blah on PATIENTMASTER(OPNUMBER)
should fix that one.
(replace blah with something useful;)

Same for table E although a different field needs an Index. Your
joins are giving me a headache, you figure that one out. :)

> For fetching the records, this query takes 14 seconds.

Not only is that taking 14 seconds, but check the CPU and Hard Drive
usage durring that 14 seconds. Ouch! Do that on a live system and
watch the phone calls come in. =P

A very wise person once told me to check that I don't have any
natural sorts in my plans, since then I checked all my queries and
removed most of the unused indexes and replaced with good, useful
ones. My database is now infinitely faster than when I first took it
over. :)

Hope this is useful to you.

Scott.