請問下面的sql 還可以在優化運行速度嗎? 我已經優化好久了,真找不到辦法,所以放上來請教個位
請問下面的sql 還可以在優化運行速度嗎? 我已經優化好久了,真找不到辦法,所以放上來請教個位 :)
注: 其中ProdBasic , lotinfo , stknwip 三個表的數據量最大. 如果吧prodbasic 表去掉運行速度為 3s 如果加上運行速度為 12s . 請問還有優化的餘地嗎. prodbasic 表的索引為 partnum,revision,layer . lotinfo 和 stknwip 表的索引為: lotnum, layer. prodbasic 和lotinfo,stknwip 的數據量比較大(prodbasic >5w, lotinfo>50w, stknwip >10w). prodbasic 的字段超過100個. 其中帶有變量的判斷不能去除,還有排序不能去除.
以下是運行代碼.
Select t1.LineName, t1.ProcName, LotNum = Left(t1.LotNum,10), t1.PartNum, t1.Revision,
t1.LayerName, t1.PnPName, t1.Qnty, t1.ITypeName, t1.StsName,
t1.InputDate, t1.ExpOutDate, t1.ExpStkTime, t1.PI, t1.IssLotNum,
t1.IsInSC, t1.SCPONum, t1.LotType, t1.Layer, t1.ProcCode,
t1.IsPrepassed,t1.PoNum, t1.POItem, t1.IsNowStop,
LLpiece= convert(varchar(3),t9.Lpiece) + ' * ' +convert(varchar(3),t9.LLPiece)
From ProdBasic t9 ,
(
select t6.LineName, t8.ProcName, t1.LotNum, t1.PartNum, t1.Revision,
t7.LayerName, t5.PnPName, t1.Qnty, t3.ITypeName, t4.StsName,
t2.InputDate, t2.ExpOutDate, t2.ExpStkTime, t2.PI, t2.IssLotNum,
t2.IsInSC, t2.SCPONum, t2.LotType, t1.Layer, t1.ProcCode,
t2.IsPrepassed,t2.PoNum, t2.POItem, t2.IsNowStop --, LLpiece='A'
from (select t1.LineId, t1.ProcCode, t1.LotNum, t1.PartNum, t1.Revision, t1.Layer, t1.POP, t1.Qnty
from stknwip t1 (NOLOCK) where t1.qnty<>0 and ((t1.LineId = @LineId) or (@LineId = ''))
and ((t1.ProcCode >= @BProcCode) OR (@BProcCode=''))
and ((t1.ProcCode <= @EProcCode) OR (@EProcCode=''))
and ((t1.LotNum >= @BLotNum) OR (@BLotNum=''))
and ((t1.LotNum <= @ELotNum) OR (@ELotNum=''))
and (((t1.PartNum >= @BPartNum) AND (t1.Revision >= @BRv)) OR ((@BPartNum='') AND (@BRv='')))
and (((t1.PartNum <= @EPartNum) AND (t1.Revision <= @ERv)) OR ((@EPartNum='') AND (@ERv='')))
and ((t1.Layer = @Layer) OR (@Layer = ''))
) t1 inner join (select t2.InputDate, t2.ExpOutDate, t2.ExpStkTime, t2.PI, t2.IssLotNum, t2.LotNum, t2.Layer, t2.IssType,
t2.IsInSC, t2.SCPONum, t2.LotType, t2.IsPrepassed,t2.PoNum, t2.POItem, t2.IsNowStop
from LotInfo t2(NOLOCK)
Where ((t2.LotType = @Status) OR (@Status= ''))
and ((t2.IssType = @IssType) OR (@IssType= ''))
and ((t2.IsNowStop = @IsNowStop) OR (@IsNowStop=2))
and ((t2.IssLotNum >= @BIssLotNum) OR (@BIssLotNum=''))
and ((t2.IssLotNum <= @EIssLotNum) OR (@EIssLotNum=''))
and (t2.PONum = @PONum OR @PONum = '')
and (convert(char(3),t2.POItem) = @POItem OR @POItem = '')
and t2.CurrProc <> '000'
and t2.CurrProc <> '900'
and (t2.ExpStkTime >= @BExpStk OR @BExpStk= '01/01/1950')
and (t2.ExpStkTime < dateadd(day,1,@EExpStk) OR @EExpStk= '12/31/2049')
) t2 on t1.LotNum = t2.LotNum and t1.Layer = t2.Layer
inner join ClassIssType t3 (NOLOCK)
on t2.IssType = t3.ITypeCode
inner join ClassStatus t4(NOLOCK)
on t2.LotType = t4.StsNum
inner join ClassPNP t5(NOLOCK)
on t1.POP = t5.PnPCode
inner join ClassLine t6(NOLOCK)
on t1.LineId = t6.LineId
inner join NumOfLayer t7(NOLOCK)
on t1.Layer = t7.Layer
inner join ProcBasic t8(NOLOCK)
on t1.ProcCode = t8.ProcCode
) t1
Where t1.partnum=t9.partnum
and t1.Revision=t9.Revision
and t9.layer= dbo.PDL_getlayer(t1.partnum, t1.revision, t1.layer)
ORDER BY t1.ProcCode, t1.PartNum, t1.Revision, t1.LotNum, t1.Layer