[SQL SERVER]select语句加某些字段速度突然变慢

最近这段时间，公司系统里的某张报表，速度突然变慢。原本查询速度不超过5秒的报表，现在查询速度居然要2分半钟，而且运气查差的话，会直接弹出死锁提示。
[SQL SERVER]select语句加某些字段速度突然变慢
经过两天尝试，基本可以排除网络的其它原因的影响。于是在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

[SQL SERVER]select语句加某些字段速度突然变慢

相关推荐