最近这段时间,公司系统里的某张报表,速度突然变慢。原本查询速度不超过5秒的报表,现在查询速度居然要2分半钟,而且运气查差的话,会直接弹出死锁提示。
经过两天尝试,基本可以排除网络的其它原因的影响。于是在SQL语句上面做分析判断。以下是代码图
首先我将select后面的查询项全部注销掉,改为 select * from。。。 速度一下子就正常了。初步判断是,select里面的重量计算,以及when case else 这两项导致的。于是在原语句里面注销这两项,但是速度还是很慢。于是一项项分别注释掉,分别分析。最后如图所示,我注释掉的这7个字段是导致我查询突然变慢的原因。 但问题是,这7个字段是只是很普通的字段,非关键字字段,无索引。为什么是这七项呢? 而且相同表也有其它字段不注销也不受影响的。
后来我又查了一下,死锁的原因。根据其中一项原则“按同一顺序访问对象” , 我把最后一句order by WIP.CreatedTime取消掉,试了一下,速度也正常了。
暂时我是通过注销最后一句order by的方法来解决问题。但是以上出现的现象背后原因,还是不太清楚:
- 原语句里导致速度变慢的7个查询字段,只是很普通的字段。 我用select * from*来查询,其实也包括了这些信息,但是为什么用select * from速度就不慢呢?
- 为什么导致速度变慢的是这7个字段呢?与这7个字段同表的字段,也有,为什么不是他们?
- 为什么我取消掉最后一句order by以后, 就算恢复这7个字段,速度也还是正常了呢?
- 这条语句速度慢,主要也是在上班阶段。非上班时间又会好很多。
对于这些问题,小弟百撕不得骑姐。还望各位路过大神,答疑解惑一下。
以下是查询语句:
select
WIP.Guid,
Basic_Product.EPC as 图号,
--Basic_Product.Name as 物品描述,
--Dynamic_Basic_ProductDetail.Specification as 规格,
--Dynamic_Basic_ProductDetail.RawMaterialCategory as 材质,
--Dynamic_Basic_ProductDetail.SurfaceTreatment as 表面处理,
--Dynamic_Basic_ProductDetail.HardnessRequirement as 机械性能等级,
Basic_Equipment.Name as 车间,
GL4.Name as 设备,
Basic_Process.Name as 当前工艺,
Basic_Process_Group.cName as 当前工艺组,
(B.cGroupName +(case B.iCount when 1 then '' else CAST(B.iCount as varchar(2)) end)) as 当前进度,
C.ProcessSN as 当前进度,
E.Name as 下工序,
NNP.Name AS 下下序,
Basic_Person.Name as 人员,
WIP.Quantity as 数量,
ProductionTask.Unit as 单位,
--Dynamic_Basic_ProductDetail.UnitWeight as 单位重量,
round(isnull(WIP.Quantity,0)*cast(isnull(Dynamic_Basic_ProductDetail.UnitWeight,0) as float)/1000,0) as 重量,
--WIP.Status as 状态,
ProductionTask.Lot as 批号,
Basic_RFIDCardDictionary.SN,
WIP.CreatedTime as 创建时间,
Dynamic_Basic_ProductDetail.cPlanPerson as 计调员,
WIP.Remark as 备注,
WIP.ProductionTaskGuid,ProductionTask.iIndex as iTaskIndex
From WIP
LEFT JOIN Basic_Product ON Basic_Product.Guid = WIP.ProductGuid --图号
left join Basic_Equipment on Basic_Equipment.Guid=WIP.EquipmentGL_1Guid --车间
left join (select Guid,Name from Basic_Equipment where GroupLevel=4) GL4 ON GL4.Guid=WIP.EquipmentGuid
left join Dynamic_Basic_ProductDetail on Dynamic_Basic_ProductDetail.ProductIndex = Basic_Product.iIndex
left join Basic_Process on Basic_Process.Guid = WIP.ProcessGuid --工艺
left join Basic_Process_Group on Basic_Process_Group.ID=Basic_Process.iGroupID--工艺组
left join (select count(1) AS iCount,ProductionTaskHistory.iTaskIndex,Basic_Process.iGroupID,Basic_Process_Group.cName as cGroupName
From ProductionTaskHistory
inner join Basic_Process on Basic_Process.Guid=ProductionTaskHistory.ProcessGuid
inner join Basic_Process_Group on Basic_Process_Group.ID=Basic_Process.iGroupID
inner join (select WIP.iTaskIndex,Basic_Process.iGroupID
from WIP inner join Basic_Process on Basic_Process.Guid = WIP.ProcessGuid
) Z on Z.iTaskIndex=ProductionTaskHistory.iTaskIndex and Z.iGroupID = Basic_Process.iGroupID
group by ProductionTaskHistory.iTaskIndex,Basic_Process.iGroupID,Basic_Process_Group.cName
) B on B.iGroupID = Basic_Process.iGroupID and B.iTaskIndex=WIP.iTaskIndex --根据报工记录分析进度
left join (select ProcessSN,ProductIndex,iCount,iGroupID
From Basic_Relation_Product_Process
inner join Basic_Process on Basic_Process.iIndex = Basic_Relation_Product_Process.ProcessIndex
) C on C.ProductIndex = Basic_Product.iIndex and C.iCount = B.iCount and C.iGroupID=B.iGroupID --获取当前进度的序号
left join Basic_Relation_Product_Process D on D.ProductIndex = Basic_Product.iIndex and D.ProcessSN=C.ProcessSN+1 --获取下一工序
left join Basic_Process E on E.iIndex = D.ProcessIndex
left join Basic_Relation_Product_Process NN on NN.ProductIndex = Basic_Product.iIndex and NN.ProcessSN=C.ProcessSN+2 --获取下下工序
left join Basic_Process NNP on NNP.iIndex = NN.ProcessIndex
left join Basic_Person on Basic_Person.Guid=WIP.PersonGuid
left join ProductionTask on ProductionTask.Guid=WIP.ProductionTaskGuid --状态,批号,数量,单位
left join Relation_RFIDCard_ProductionTask on Relation_RFIDCard_ProductionTask.ProductionTaskGuid=WIP.ProductionTaskGuid
left join Basic_RFIDCardDictionary on Basic_RFIDCardDictionary.Guid=Relation_RFIDCard_ProductionTask.RFIDCardGuid
where 1=1
order by WIP.CreatedTime