mysql 存储过程执行速度_SQL存储过程执行速度慢

发表于 2015-3-10 11:07 | 来自

51CTO网页

引用:原帖由 UltraSQL 于 2015-3-9 23:48 发表

麻烦楼主把查询语句和执行计划贴出来！SELECT

row_number() over (order by t2.医生姓名) 序号

,t2.医生代码,t2.医生姓名,t2.总处方,t3.未点评,t4.已点评

,case when (t4.已点评>0 and t5.合格 IS null) then 0 else t5.合格 end 合格

,case when (t4.已点评>0 and t6.不合格 IS null) then 0 else t6.不合格 end 不合格

,cast(cast((case when (t4.已点评>0 and t5.合格 IS null) then 0 else t5.合格 end)*100.0/t4.已点评 as decimal(6,2)) as varchar(8))+'%' 合格率

,t7.抗菌药物处方,cast(cast(t7.抗菌药物处方*100.0/t2.总处方 as decimal(6,2)) as varchar(8))+'%' 抗菌药物使用率

,t8.抗菌药物静脉注射处方,cast(cast(t8.抗菌药物静脉注射处方*100.0/t2.总处方 as decimal(6,2)) as varchar(8))+'%' 抗菌药物静脉注射率

,t9.基药处方,cast(cast(t9.基药处方*100.0/t2.总处方 as decimal(6,2)) as varchar(8))+'%' 基药使用率

,t10.针剂处方,cast(cast(t10.针剂处方*100.0/t2.总处方 as decimal(6,2)) as varchar(8))+'%' 针剂使用率

,t11.总金额,cast(t11.总金额/t2.总处方 as decimal(8,2)) 平均处方金额

,t12.总用药种数,cast(t12.总用药种数*1.0/t2.总处方 as decimal(6,2)) 平均每人用药种数

FROM

(select YSDM 医生代码,YSNAME 医生姓名,COUNT(1) 总处方 from (select ysname,YSDM,blh from T_MZ_CFB A WHERE CFRQ between @Starttime and @endtime and exists( select * from T_SelectedPatient B where Back='门诊' and Delete_Bit=0 and time=@Starttime and A.BLH=B.Blh AND A.YSDM=B.ysdm) group by ysname,YSDM,BLH) t1 group by ysname,YSDM) t2

left join (select YSDM 医生代码,COUNT(1) 未点评 from (select ysname,YSDM,blh from T_MZ_CFB A WHERE CFRQ between @Starttime and @endtime and Flag=0 and exists( select * from T_SelectedPatient B where Back='门诊' and Delete_Bit=0  and time=@Starttime and A.BLH=B.Blh AND A.YSDM=B.ysdm) group by ysname,YSDM,BLH) t1 group by ysname,YSDM) t3

on t2.医生代码=t3.医生代码 left join (select YSDM 医生代码,COUNT(1) 已点评 from (select ysname,YSDM,blh from T_MZ_CFB A WHERE CFRQ between @Starttime and @endtime and Flag<>0 and exists( select * from T_SelectedPatient B where Back='门诊' and Delete_Bit=0 and time=@Starttime and A.BLH=B.Blh AND A.YSDM=B.ysdm) group by ysname,YSDM,BLH) t1 group by ysname,YSDM) t4

on t2.医生代码=t4.医生代码 left join (select YSDM 医生代码,COUNT(1) 合格 from (select ysname,YSDM,blh from T_MZ_CFB A WHERE CFRQ between @Starttime and @endtime and Flag=1 and exists( select * from T_SelectedPatient B where Back='门诊' and Delete_Bit=0 and time=@Starttime and A.BLH=B.Blh AND A.YSDM=B.ysdm) group by ysname,YSDM,BLH) t1 group by ysname,YSDM) t5

on t2.医生代码=t5.医生代码

left join (select YSDM 医生代码,COUNT(1) 不合格 from (select ysname,YSDM,blh from T_MZ_CFB A WHERE CFRQ between @Starttime and @endtime and Flag=2 and exists( select * from T_SelectedPatient B where Back='门诊' and Delete_Bit=0 and time=@Starttime and A.BLH=B.Blh AND A.YSDM=B.ysdm) group by ysname,YSDM,BLH) t1 group by ysname,YSDM) t6

on t2.医生代码=t6.医生代码

left join (select YSDM 医生代码,COUNT(1) 抗菌药物处方

from (select ysname,YSDM,blh

from T_MZ_CFB A WHERE CFRQ between @Starttime and @endtime and CFID IN (SELECT distinct CFID FROM T_MZ_CFBMX WHERE KSSDJID>0) and exists( select *from T_SelectedPatient B where Back='门诊' and Delete_Bit=0 and time=@Starttime and A.BLH=B.Blh AND A.YSDM=B.ysdm) group by ysname,YSDM,BLH) t1 group by ysname,YSDM) t7

on t2.医生代码=t7.医生代码

left join (select YSDM 医生代码,COUNT(1) 抗菌药物静脉注射处方

from (select ysname,YSDM,blh from T_MZ_CFB A WHERE CFRQ between @Starttime and @endtime and CFID IN (SELECT distinct CFID FROM T_MZ_CFBMX WHERE KSSDJID>0 and JMZS=1) and exists( select * from T_SelectedPatient B where Back='门诊' and Delete_Bit=0 and time=@Starttime and A.BLH=B.Blh AND A.YSDM=B.ysdm) group by ysname,YSDM,BLH) t1 group by ysname,YSDM) t8

on t2.医生代码=t8.医生代码

left join (select YSDM 医生代码,COUNT(1) 基药处方

from (select ysname,YSDM,blh from T_MZ_CFB A WHERE CFRQ between @Starttime and @endtime and CFID IN (SELECT distinct CFID FROM T_MZ_CFBMX WHERE JCYP=1) and exists( select * from T_SelectedPatient B where Back='门诊' and Delete_Bit=0 and time=@Starttime and A.BLH=B.Blh AND A.YSDM=B.ysdm) group by ysname,YSDM,BLH) t1 group by ysname,YSDM) t9

on t2.医生代码=t9.医生代码

left join (select YSDM 医生代码,COUNT(1) 针剂处方

from (select ysname,YSDM,blh from T_MZ_CFB A WHERE CFRQ between @Starttime and @endtime and CFID IN (SELECT distinct CFID FROM T_MZ_CFBMX WHERE ZSJYP=1) and exists( select * from T_SelectedPatient B where Back='门诊' and Delete_Bit=0 and time=@Starttime and A.BLH=B.Blh AND A.YSDM=B.ysdm) group by ysname,YSDM,BLH) t1 group by ysname,YSDM) t10

on t2.医生代码=t10.医生代码

left join (select YSDM 医生代码,ysname 医生姓名,SUM(zje) 总金额 from (select ysname,YSDM,blh,sum(ZJE) zje from T_MZ_CFB A WHERE CFRQ between @Starttime and @endtime and exists( select * from T_SelectedPatient B where Back='门诊' and Delete_Bit=0 and time=@Starttime and A.BLH=B.Blh AND A.YSDM=B.ysdm) group by ysname,YSDM,BLH) t1 group by YSDM,ysname) t11

on t2.医生代码=t11.医生代码

left join (select YSDM 医生代码 ,ysname 医生姓名,sum(num) 总用药种数 from (select A.YSDM,A.YSNAME,A.BLH,COUNT(DISTINCT B.XMID) num from T_MZ_CFB A INNER JOIN T_MZ_CFBMX B ON A.CFID=B.CFID where CFRQ between @Starttime and @endtime and exists( select * from T_SelectedPatient C where Back='门诊' and Delete_Bit=0 and time=@Starttime and A.BLH=C.Blh AND A.YSDM=C.ysdm) AND cfrq between @Starttime and @endtime  GROUP BY A.BLH,A.YSDM,A.YSNAME) t1 group by YSDM,ysname) t12

on t2.医生代码=t12.医生代码