SQL调优问题!
问题描述:
1.涉及表
请购单身表: PURTB
请购单头表: PURTA
采购单头表: PURTC
采购单身表: PURTD
以上为ERP系统自带表,以下为自己开发的排程系统涉及表
定单排程主表: KSPCSA
采购跟踪表: KSPCSE
交期回复表: KSPCSF
PURTD 记录40W以上, PURTB 记录50W以上
2.现状
入系统下单(维护请购单并生成采购单)采购人员> 25, 忙的时候下单及录入进货单都很缓慢.
3.问题:
SQL1:
select DISTINCT
cast(I.TA003 as datetime) AS RQ1,
cast(F.TC024 as datetime) AS RQ2,
C.TD021 AS F01,
J.SA08 AS F02,
C.TD002 AS F03,
C.TD004 AS F04,
C.TD005 AS F05,
C.TD006 AS F06,
C.TD009 AS F07,
C.TD008 AS F08,
(C.TD015-C.UDF51) AS F09,
case when J.SA27=0 and (D.SE16 is null or D.SE16= ' ') and (E.SF08 is null) then '未排 ' else
(case when (D.SE16 is null or D.SE16= ' ') and (E.SF08 is null) then '未复 '
when E.SF08 is null then
case when D.SE16= '待复 ' then '待复 '
when D.SE16= '待交 ' then '待交 '
end
else rtrim(cast(DATEPART (year,E.SF08) as char))+ '- '
+rtrim(cast(DATEPART (month,E.SF08) as char))+ '- '
+rtrim(cast(DATEPART (DAY,E.SF08) as char)) end) end AS F10,
isnull(E.SF09,(C.TD008-C.TD015-C.UDF51)) AS F11,
isnull(H.MV002, '无 ') AS F12,
F13=case when E.SF08 is null then C.TD008 else
(select sum(SF09) from KSPCSF where SF02=E.SF02 and SF03=E.SF03 and SF04=E.SF04 and SF08 <=E.SF08) end,
G.MA002 AS F14,C.TD008-C.TD015+C.UDF51 as F15,C.TD010 AS F16,rtrim(D.SE18)+rtrim(isnull(E.SF10, ' ')) AS F17,B.TB031 AS F18,
isnull(J.SA12, ' ') AS F19,isnull(J.SA32, ' ') AS F20,
isnull(Z.UDF56,0) AS F21,C.TD003 AS F22,
null AS F23,
J.SA04 AS F24,J.SA05 AS F25,TD001 AS F26,F.TC014 AS F27,isnull(SA15, ' ') AS F28,isnull(B.UDF06, ' ') AS F29,
cast(case when substring(SA32,1,3)= '200 ' then SA32 else null end as datetime) as F30,
isnull(J.SA34, ' ') AS F31,isnull(J.SA35, ' ') AS F32
from PURTD C
left join PURTB B on B.TB001=C.TD026 and B.TB002=C.TD027 and B.TB003=C.TD028
left join KSPCSE D on C.TD001=D.SE05 and C.TD002=D.SE06 and C.TD003=D.SE07
left join KSPCSF E on C.TD001=E.SF02 and C.TD002=E.SF03 and C.TD003=E.SF04
left join PURTC F on C.TD001=F.TC001 and C.TD002=F.TC002
left join PURMA G on F.TC004=G.MA001
left join CMSMV H on F.TC011=H.MV001
left join PURTA I on B.TB001=I.TA001 and B.TB002=I.TA002
left join KSPCSA J on C.TD021=J.SA02
left join INVMB Z on B.TB004=Z.MB001
where (C.TD016= 'N ' or C.UDF51> 0) and C.TD018 <> 'V ' and C.TD001 in ( '3301 ', '3302 ', '3303 ', '3304 ', '3305 ', '3306 ') and C.TD008 <> 0 and substring(C.TD004,1,4) <> '1501 ' and substring(C.TD004,1,1) <> '2 'and substring(C.TD004,1,4) <> '3400 '
如上SQL执行30秒以上,对如上SQL执行计划分析, PURTD为table scan,
不做任何关联,单独带条件查找PURTD耗时15秒:
SELECT * FROM PURTD C
where (C.TD016= 'N ' or C.UDF51> 0) and C.TD018 <> 'V ' and C.TD001 in ( '3301 ', '3302 ', '3303 ', '3304 ', '3305 ', '3306 ') and C.TD008 <> 0 and substring(C.TD004,1,4) <> '1501 ' and substring(C.TD004,1,1) <> '2 'and substring(C.TD004,1,4) <> '3400 '
上述SQL是否可优化?
SQL2:
select DISTINCT
A.SA02 AS A01,
case when B.UDF06= ' ' or B.UDF06 is null then '半成品 ' else B.UDF06 end AS A02,
rtrim(case when F.UDF06= ' ' or F.UDF06 is null then MB002 else F.UDF06 end) AS A03, '( '+
case when C.TD002 IS NULL then case when substring(F.MB003,1,2)= '待稿 ' then '待稿 ' else '未下单 ' end
when E.SF01 is null then
case when D.SE16 is null or rtrim(D.SE16)= ' ' then '未复 '
when D.SE16= '待复 ' then case when rtrim(isnull(D.SE18, ' '))= ' ' then '待复 ' else rtrim(isnull(D.SE18, ' ')) end
when D.SE16= '待交 ' then case when rtrim(isnull(D.SE18, ' '))= ' ' then '待交 ' else rtrim(isnull(D.SE18, ' ')) end
end
else isnull(rtrim(cast(month(E.SF08) AS CHAR(2)))+ '- '+rtrim(case when cast(day(E.SF08) AS CHAR) <10 then '0 '
+cast(day(E.SF08) AS CHAR) else cast(day(E.SF08) AS CHAR) end), '错误 ')+isnull(rtrim(SF10), ' ') end
+ ') ' as A04
into #t3
from KSPCSA A
left join PURTB B on A.SA02=B.TB030
left join PURTD C on B.TB001=C.TD026 and B.TB002=C.TD027 and B.TB003=C.TD028
left join KSPCSE D on C.TD001=D.SE05 and C.TD002=D.SE06 and C.TD003=D.SE07
left join KSPCSF E on C.TD001=E.SF02 and C.TD002=E.SF03 and C.TD003=E.SF04
left join INVMB F on B.TB004=F.MB001
where B.TB025= 'Y ' and B.TB009 <> 0 and B.TB039 <> 'y ' and (isnull(C.TD016, 'N ') not in ( 'Y ', 'y ') or isnull(C.UDF51,0)> 0) and isnull(C.TD018, ' ') <> 'V '
and isnull(C.TD018, 'N ') <> 'V ' and not(substring(B.TB004,1,1) in ( '1 ', '2 ', '9 ') and substring(B.TB004,1,4) not in ( '2225 ', '1300 ')) and substring(B.TB004,1,4) not in ( '3400 ', '3102 ')
上述SQL执行起伏很大,快的时候20秒内就可完成,慢的时候3-5分钟或经常死锁
请问如何优化?
[解决办法]
太长，接分再说。
[解决办法]
看看执行计划中，各个表之间的连接关系，根据连接关系在相应表增加索引以提高查询效率
[解决办法]
这种分~~不想要了...眼花
