大型SQL Server数据库使PHP Web应用程序超时 [英] Large SQL Server database timing out PHP web application
问题描述
我们正在运行一个医院系统，该系统是使用PHP创建的基于Web的系统.该系统最初由于数据库较小而运行很快，但现在却变慢了.
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.
以下是查询示例
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
您可以看到我们查询的大小(未优化)，显示了患者，医生，接受的服务，他从什么时间到哪个ins公司.因此，现在我们创建的索引确实可以使用一段时间，但现在速度又变慢了.在本地主机上运行系统大约需要15秒，结果会在实时系统上显示，超时.
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:
- 耐心的细节-195k
- 患者-34k
- 员工-200
- 部门服务-700
- 提供者-2800
谢谢
推荐答案
好，让我们从索引开始. 我假设您已经为patienappointments.date创建了一个索引，并且该索引属于集群类型.我还假设您在ds.departgroupid上有一个索引. 您丢失了几张桌子的大小，但是我猜想那些左联接是罪魁祸首. 查询执行计划应该会产生一些有趣的结果(如果您可以在此处发布它，则可能有助于消除一些疑问).如果您在date字段上没有索引，那么您将面临顺序表扫描(读取整个表)，这确实很糟糕.还要检查那些左联接，因为其中一个可能会弄乱查询计划.
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:
- 仅使用内部联接运行查询，并测试所需的时间
- 为此获取查询计划，并查看是否可以优化(主要通过添加索引)
- 使用WHERE语句运行步骤1的查询
- 查询计划和优化
- 一次添加左联接一个
- 查询计划和优化
依此类推...
即使这样，在这种情况下，您的查询最终可能会花费很多时间
Even so maybe your query ends up taking a lot of time in that case there are two things you can do
- 添加更多的硬件(拥有更多的内存和更好的磁盘永远不会受伤)
- 基于优化的输入，使用时间表将查询分为更小的部分，优化器可以加快性能.
这篇关于大型SQL Server数据库使PHP Web应用程序超时的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
