疑难问题！急！！

超凡 2011-06-02 02:21:43 




这个怎存储过程么优化，执行速度超慢！数据量不大，执行效率低！



set ANSI_NULLS ON

set QUOTED_IDENTIFIER ON

go





-- =============================================

-- Author:      <Author,,lsl>

-- Create date: <Create Date,,>

-- Description:	<Description,,>

--测试语句--   exec [proce_Find_ATT] '2011-03-31' ,'MFI','Cartridge','L1'

-- =============================================

ALTER PROCEDURE [dbo].[proce_Find_ATT]

		@Testingdate varchar(50),

		@M00_Family varchar(20),

		@M01_Area varchar(50),

		@M02_Line varchar(50)		

AS

BEGIN

select 

	    Value_Time= Fixing_DT,

	    Testingdate,

		M00_Family,

		M01_Area,

	    Class,

		M06_Station,

        P01_HumanCount,

		M02_Line,

		型号=case when @M01_Area='Cartridge' then M07_ModelTypeCode

											 else P05_PartNumber

			 end,

		GROUPCode,	

		设备=case when @M01_Area='Cartridge' then  M02_Line+'_'+M06_Station

			    							 else M06_Station

			 end,

		AVGValue= (select avg(Fixing_DT) from T_ATT where Testingdate=b.Testingdate and M00_Family=b.M00_Family and M01_Area=b.M01_Area and M02_Line =b.M02_Line and M06_Station=b.M06_Station and M07_ModelTypeCode=b.M07_ModelTypeCode and Class=b.Class and GROUPCode=b.GROUPCode and P05_PartNumber=b.P05_PartNumber  group by M00_Family,M01_Area,M02_Line,M06_Station,Class,Testingdate,GROUPCode,M07_ModelTypeCode,P05_PartNumber) ,

        MaxValue= (select max(Fixing_DT) from T_ATT where Testingdate=b.Testingdate and M00_Family=b.M00_Family and M01_Area=b.M01_Area and M02_Line =b.M02_Line and M06_Station=b.M06_Station and M07_ModelTypeCode=b.M07_ModelTypeCode and Class=b.Class and GROUPCode=b.GROUPCode and P05_PartNumber=b.P05_PartNumber  group by M00_Family,M01_Area,M02_Line,M06_Station,Class,Testingdate,GROUPCode,M07_ModelTypeCode,P05_PartNumber) ,

		MinValue= (select min(Fixing_DT) from T_ATT where Testingdate=b.Testingdate and M00_Family=b.M00_Family and M01_Area=b.M01_Area and M02_Line =b.M02_Line and M06_Station=b.M06_Station and M07_ModelTypeCode=b.M07_ModelTypeCode and Class=b.Class and GROUPCode=b.GROUPCode and P05_PartNumber=b.P05_PartNumber  group by M00_Family,M01_Area,M02_Line,M06_Station,Class,Testingdate,GROUPCode,M07_ModelTypeCode,P05_PartNumber) ,

		Min_Cyc= (case   when  ((select max(t.bb)  from (select count(Fixing_DT) as bb  from T_ATT as d where Testingdate=b.Testingdate and M00_Family=b.M00_Family and M01_Area=b.M01_Area and M02_Line =b.M02_Line and M06_Station=b.M06_Station and P01_HumanCount=b.P01_HumanCount and M07_ModelTypeCode=b.M07_ModelTypeCode and Class=b.Class and GROUPCode=b.GROUPCode and P05_PartNumber=b.P05_PartNumber  and Fixing_DT<

							    (select avg(Fixing_DT) from T_ATT where Testingdate=d.Testingdate and M00_Family=d.M00_Family and M01_Area=d.M01_Area and M02_Line =d.M02_Line and M06_Station=d.M06_Station and P01_HumanCount=d.P01_HumanCount and M07_ModelTypeCode=d.M07_ModelTypeCode and Class=d.Class and GROUPCode=d.GROUPCode  and P05_PartNumber=d.P05_PartNumber 

										 group by M00_Family,M01_Area,M02_Line,M06_Station,Class,M07_ModelTypeCode,Testingdate,GROUPCode,P01_HumanCount,P05_PartNumber)

										 group by M00_Family,M01_Area,M02_Line,M06_Station,Class,M07_ModelTypeCode,Testingdate,GROUPCode,P01_HumanCount,P05_PartNumber,Fixing_DT ) t where t.bb>1))>1

					      and  ((select max(t.bb)  from (select count(Fixing_DT) as bb  from T_ATT as d where Testingdate=b.Testingdate and M00_Family=b.M00_Family and M01_Area=b.M01_Area and M02_Line =b.M02_Line and M06_Station=b.M06_Station and P01_HumanCount=b.P01_HumanCount and M07_ModelTypeCode=b.M07_ModelTypeCode and Class=b.Class and GROUPCode=b.GROUPCode and P05_PartNumber=b.P05_PartNumber  and Fixing_DT<

								(select avg(Fixing_DT) from T_ATT where Testingdate=d.Testingdate and M00_Family=d.M00_Family and M01_Area=d.M01_Area and M02_Line =d.M02_Line and M06_Station=d.M06_Station and M07_ModelTypeCode=d.M07_ModelTypeCode and Class=d.Class and P01_HumanCount=b.P01_HumanCount and GROUPCode=d.GROUPCode and P05_PartNumber=d.P05_PartNumber 

										 group by M00_Family,M01_Area,M02_Line,M06_Station,Class,M07_ModelTypeCode,Testingdate,GROUPCode,P01_HumanCount,P05_PartNumber)

										 group by M00_Family,M01_Area,M02_Line,M06_Station,Class,M07_ModelTypeCode,Testingdate,GROUPCode,P01_HumanCount,P05_PartNumber,Fixing_DT ) t where t.bb>1)) is not null

						  then  (select min(k.aa) from  (select Fixing_DT as aa  from T_ATT as f where Testingdate=b.Testingdate and M00_Family=b.M00_Family and M01_Area=b.M01_Area and M02_Line =b.M02_Line and M06_Station=b.M06_Station and M07_ModelTypeCode=b.M07_ModelTypeCode and Class=b.Class and P01_HumanCount=b.P01_HumanCount and GROUPCode=b.GROUPCode and P05_PartNumber=b.P05_PartNumber  and Fixing_DT<

								(select avg(Fixing_DT) from T_ATT where Testingdate=f.Testingdate and M00_Family=f.M00_Family and M01_Area=f.M01_Area and M02_Line =f.M02_Line and M06_Station=f.M06_Station and M07_ModelTypeCode=f.M07_ModelTypeCode and Class=f.Class and GROUPCode=f.GROUPCode and P01_HumanCount=b.P01_HumanCount and P05_PartNumber=f.P05_PartNumber 

										 group by M00_Family,M01_Area,M02_Line,M06_Station,Class,M07_ModelTypeCode,Testingdate,GROUPCode,P01_HumanCount,P05_PartNumber)

										 group by M00_Family,M01_Area,M02_Line,M06_Station,Class,M07_ModelTypeCode,Testingdate,GROUPCode,P01_HumanCount,P05_PartNumber,Fixing_DT having(count(Fixing_DT)=

								((select max(t.bb)  from (select count(Fixing_DT) as bb  from T_ATT as d where Testingdate=b.Testingdate and M00_Family=b.M00_Family and M01_Area=b.M01_Area and M02_Line =b.M02_Line and M06_Station=b.M06_Station and M07_ModelTypeCode=b.M07_ModelTypeCode and Class=b.Class and P01_HumanCount=b.P01_HumanCount and GROUPCode=b.GROUPCode and Fixing_DT<

								 (select avg(Fixing_DT) from T_ATT where Testingdate=d.Testingdate and M00_Family=d.M00_Family and M01_Area=d.M01_Area and M02_Line =d.M02_Line and M06_Station=d.M06_Station and M07_ModelTypeCode=d.M07_ModelTypeCode and Class=d.Class and GROUPCode=d.GROUPCode and P01_HumanCount=d.P01_HumanCount and P05_PartNumber=d.P05_PartNumber 

										 group by M00_Family,M01_Area,M02_Line,M06_Station,Class,M07_ModelTypeCode,Testingdate,P01_HumanCount,P05_PartNumber,GROUPCode)

										 group by M00_Family,M01_Area,M02_Line,M06_Station,Class,M07_ModelTypeCode,Testingdate,P01_HumanCount,P05_PartNumber,GROUPCode,Fixing_DT ) t where t.bb>1))

									)) k)

							else (select avg(t.aa) from  (select Fixing_DT as aa  from T_ATT as f where Testingdate=b.Testingdate and M00_Family=b.M00_Family and M01_Area=b.M01_Area and M02_Line =b.M02_Line and M06_Station=b.M06_Station and M07_ModelTypeCode=b.M07_ModelTypeCode and P01_HumanCount=b.P01_HumanCount and Class=b.Class and GROUPCode=b.GROUPCode and P05_PartNumber=b.P05_PartNumber  and Fixing_DT<

							     (select avg(Fixing_DT) from T_ATT where Testingdate=f.Testingdate and M00_Family=f.M00_Family and M01_Area=f.M01_Area and M02_Line =f.M02_Line and M06_Station=f.M06_Station  and M07_ModelTypeCode=f.M07_ModelTypeCode and Class=f.Class and GROUPCode=f.GROUPCode and P01_HumanCount=f.P01_HumanCount  and P05_PartNumber=f.P05_PartNumber 

										 group by M00_Family,M01_Area,M02_Line,M06_Station,Class,M07_ModelTypeCode,Testingdate,P05_PartNumber,P01_HumanCount,GROUPCode)

										 group by M00_Family,M01_Area,M02_Line,M06_Station,Class,M07_ModelTypeCode,Testingdate,P05_PartNumber,P01_HumanCount,GROUPCode,Fixing_DT ) t) 

						     end)

							,



	

		CT_Min_Cyc=  case   when  (select HandWork_DT from T_ATT_HW where M00_Family=b.M00_Family and M01_Area=b.M01_Area and M02_Line =b.M02_Line and M06_Station=b.M06_Station and M07_ModelTypeCode=b.M07_ModelTypeCode and Class=b.Class and GROUPCode=b.GROUPCode and P05_PartNumber=b.P05_PartNumber  )is not null  then

				   (case   when ((select max(t.bb)  from (select count(Fixing_DT) as bb  from T_ATT as d where Testingdate=b.Testingdate and M00_Family=b.M00_Family and M01_Area=b.M01_Area and M02_Line =b.M02_Line and M06_Station=b.M06_Station and M07_ModelTypeCode=b.M07_ModelTypeCode and Class=b.Class and GROUPCode=b.GROUPCode  and P05_PartNumber=b.P05_PartNumber and P01_HumanCount=b.P01_HumanCount  and Fixing_DT<

								 (select avg(Fixing_DT) from T_ATT where Testingdate=d.Testingdate and M00_Family=d.M00_Family and M01_Area=d.M01_Area and M02_Line =d.M02_Line and M06_Station=d.M06_Station and M07_ModelTypeCode=d.M07_ModelTypeCode and Class=b.Class and GROUPCode=b.GROUPCode  and P05_PartNumber=b.P05_PartNumber  and P01_HumanCount=b.P01_HumanCount

										 group by M00_Family,M01_Area,M02_Line,M06_Station,Class,M07_ModelTypeCode,Testingdate,P01_HumanCount,GROUPCode ,P05_PartNumber)

										 group by M00_Family,M01_Area,M02_Line,M06_Station,Class,M07_ModelTypeCode,Testingdate,P01_HumanCount,GROUPCode,P05_PartNumber,Fixing_DT ) t where t.bb>1))>1

							and ((select max(t.bb)  from (select count(Fixing_DT) as bb  from T_ATT as d where Testingdate=b.Testingdate and M00_Family=b.M00_Family and M01_Area=b.M01_Area and M02_Line =b.M02_Line and M06_Station=b.M06_Station and M07_ModelTypeCode=b.M07_ModelTypeCode and Class=b.Class and GROUPCode=b.GROUPCode and P05_PartNumber=b.P05_PartNumber and P01_HumanCount=b.P01_HumanCount and Fixing_DT<
...全文
134 23 打赏 收藏 转发到动态 举报
AI 作业
写回复
用AI写文章
23 条回复
切换为时间正序
请发表友善的回复…
发表回复
yibey 2011-06-03
  • 打赏
  • 举报
 回复
[Quote=引用 3 楼 qianjin036a 的回复:]

楼下继续.
[/Quote]

楼下继续吧，如果楼主真想人帮忙的话，请在自己分析后遇到的的问题列出来，别让大家去想你的业务逻辑
水向东流时光怎么偷 2011-06-03
  • 打赏
  • 举报
 回复
[Quote=引用 4 楼 ssp2009 的回复:]
哥，佩服你。
[/Quote]

帮顶,
唐诗三百首 2011-06-03
  • 打赏
  • 举报
 回复
看执行计划,看主要消耗(cost)在哪个环节上,
抓住瓶颈所在,优化后效果就很明显了.
gogodiy 2011-06-03
  • 打赏
  • 举报
 回复
我觉得解决这个问题最好的办法，还不如重新写一个。
要看懂这个存储过程都要花大半天时间。
Q315054403 2011-06-03
  • 打赏
  • 举报
 回复
够苦力的了
唐诗三百首 2011-06-03
  • 打赏
  • 举报
 回复
好神奇..
zc_0101 2011-06-03
  • 打赏
  • 举报
 回复
[Quote=引用 20 楼 hncelfhv 的回复:]
引用 19 楼 yibey 的回复:
引用 3 楼 qianjin036a 的回复:

楼下继续.


楼下继续吧，如果楼主真想人帮忙的话，请在自己分析后遇到的的问题列出来，别让大家去想你的业务逻辑


问题解决了!谢了!各位,这类型的优化跟索引拉不上关系,前面我知道问题所在,白天没解决,晚上做梦知道答案了,一早起来解决了!
[/Quote]



不是吧。。。。
超凡 2011-06-03
  • 打赏
  • 举报
 回复
[Quote=引用 19 楼 yibey 的回复:]
引用 3 楼 qianjin036a 的回复:

楼下继续.


楼下继续吧，如果楼主真想人帮忙的话，请在自己分析后遇到的的问题列出来，别让大家去想你的业务逻辑
[/Quote]

问题解决了!谢了!各位,这类型的优化跟索引拉不上关系,前面我知道问题所在,白天没解决,晚上做梦知道答案了,一早起来解决了!
超凡 2011-06-02
  • 打赏
  • 举报
 回复
顶顶顶顶顶起！
超凡 2011-06-02
  • 打赏
  • 举报
 回复
[Quote=引用 11 楼 duoxu1983 的回复:]
我觉得你给出表结构 给出示例数据 说下想要的结果和基本逻辑比较靠谱
[/Quote]

逻辑超复杂，判断比较多！
超凡 2011-06-02
  • 打赏
  • 举报
 回复
[Quote=引用 10 楼 orchidcat 的回复:]
LZ

子查询太多了。可以考虑将其设定为链接的方式来处理。利用inner join ,left join 等。

另外基于执行计划，LZ可以看看具体问题发生在什么地方。
[/Quote]





--另外基于执行计划，LZ可以看看具体问题发生在什么地方。

什么意思？



我要用索引优化有作用吗？
水向东流时光怎么偷 2011-06-02
  • 打赏
  • 举报
 回复
晕了
duoxu1983 2011-06-02
  • 打赏
  • 举报
 回复
我觉得你给出表结构 给出示例数据 说下想要的结果和基本逻辑比较靠谱
Mr_Nice 2011-06-02
  • 打赏
  • 举报
 回复
LZ

子查询太多了。可以考虑将其设定为链接的方式来处理。利用inner join ,left join 等。

另外基于执行计划，LZ可以看看具体问题发生在什么地方。

超凡 2011-06-02
  • 打赏
  • 举报
 回复
[Quote=引用 3 楼 qianjin036a 的回复:]
楼下继续.
[/Quote]
偶像！帮帮忙!
ForFumm 2011-06-02
  • 打赏
  • 举报
 回复
看着头晕，等楼下
超凡 2011-06-02
  • 打赏
  • 举报
 回复
谁愿帮帮忙！ 高手来！ 万分感谢！

Spade_J 2011-06-02
  • 打赏
  • 举报
 回复
超凡 2011-06-02
  • 打赏
  • 举报
 回复
这是一个存储过程
快溜 2011-06-02
  • 打赏
  • 举报
 回复
哥，佩服你。
加载更多回复（3）
Windows 7安全模式解决疑难问题.docx
Windows 7安全模式解决疑难问题.docx
FANUC系统的疑难故障分析报告与排除.doc
FANUC系统的疑难故障分析报告与排除.doc
发那科FANUC0系统的疑难故障分析及排除.doc
发那科FANUC0系统的疑难故障分析及排除.doc
sapho疑难病例讨论学习教案.ppt
sapho疑难病例讨论学习教案.ppt
高危孕产妇与急危重症新生儿管理制度汇编.doc
高危孕产妇与急危重症新生儿管理制度汇编.doc
疑难问题

22,302

社区成员

121,734

社区内容

发帖
与我相关
我的任务
社区描述
MS-SQL Server 疑难问题
社区管理员
  • 疑难问题社区
  • 尘觉
加入社区
  • 近7日
  • 近30日
  • 至今

加载中

查看更多榜单
社区公告
暂无公告

试试用AI创作助手写篇文章吧

+ 用AI写文章