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:
- patientappdetails - 195k
- patients - 34k
- staffs - 200
- departmentalservices - 700
- insproviders - 2800
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:
- Run the query with the inner joins only, and test the time it takes
- Get a query plan for that and see if it can be optimized (by adding indexes mostly)
- Run the query of step 1 with the WHERE statement
- Query plan and optimization
- Add the left joins one at the time
- Query plan and optimization
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
- Add more hardware (it never hurts to have more memory and better disks)
- Based on the input of the optimization, split the query into smaller parts using temporal tables son the optimizer can speed up the good parts.
Generally in situations when you have a lot of joins and especially when joining a lot of records to few (each of the patients' appointments to doctor's/staff details and similar) it makes sense to fetch these records separately and join them outside SQL server - on application server.
I would suggest to fetch doctors/staffs/departmentalservices (and maybe insproviders) details once and store them in application cache on application server. Then your SQL query will only need to get patient's and appointments' details which will result in considerably smaller (shorter record) result set.
And considering the size of your selection (I assume it is some sort of reporting functionality) you should think about paging and/or batch processing here.
