We are running a hospital system which is web based created in PHP. The system was initially fast due to small size of the database but now it has become slow.
The following is an example query
select pa.id, pa.date as date, pa.visitno, pa.receiptno, pa.debitnoteno, pad.id as padid,
pad.serviceid as serviceid, pad.waitno, pa.paytype, s.id as doctorid, s.fullname as
doctorname, p.id as patientid, p.name as patient, p.regno, p.age, p.gender, p.doc,
p.department, p.telno, p.address, pa.ins_prov_id, ip.name as provider,
pa.sicksheet_billcode as billcode, ds.id as serviceid, ds.name as servicename, ds.departid
as departid, ds.servicetype as servicetype, pad.charge, pad.status as status, ts.id as
timeslotid, ts.name as timeslot, pad.treatment, sd.anesthesiologist, sd.hospitalcharge,
sd.anesthcharge from patientappointments as pa
INNER JOIN patientappdetails as pad ON pa.id = pad.patappid
INNER JOIN patients as p ON pa.patid = p.id
INNER JOIN staffs as s ON pad.doctorid = s.id
LEFT JOIN departmentalservices as ds ON pad.serviceid = ds.id
LEFT JOIN insproviders as ip ON pa.ins_prov_id = ip.id
LEFT JOIN timeslots as ts ON pad.timeslotid = ts.id
LEFT JOIN surgerydetails as sd ON sd.appdetid = pad.id
where 1 = 1 and pa.date >= '01.Jul.2012' and ds.departgroupid = 16 and pad.charge != 0
As you can see the size of our queries (call them un-optimized) which shows the patient, doctor, service taken, what time and which ins company he came from. So now we created indexes that did help for a while but now again the speed has become slow. Running the system on localhost results in around 15 secs for the result to appear while on the live system, it times out.
Can you suggest any method to improve the speed and exactly how to implement them.
Just FYI, rows in each table are as follows:
Thank you
Well, lets start with the indexes. I assume you have created an index for patienappointments.date and that is of the clustered kind. I also assume you have an index on ds.departgroupid. You're missing the size of several tables, but i'm gonna guess that those left joins are the culprits. The query execution plan should yield some interesting results (if you can post it here it may help dispel some doubts). If you dont have an index on the date field you are facing a sequential table scan (reading the whole table) and that is really bad. Also check those left joins since one of them is probably messing the query plan.
As a rule of thumb i'd do this:
And so forth...
Even so maybe your query ends up taking a lot of time in that case there are two things you can do