查询语句优化问题(急!!)
Select 0 as RowID,
FRes,
isNull(FComkeyColor, 'None') as FComkeyColor,
FBeginTime1 + '~' +FEndTime1 as FTimeTerm,
dbo.Fn_GetBTSINVData2(0,
'',
Fres,
FComkeyColor,
'2006/02/20' + ' ' + FBeginTime + ':00',
'2006/02/20' + ' ' + FEndTime + ':00') as FFactQty,
FDispachQty,
dbo.Fn_GetBTSINVData2(1,
'',
Fres,
FComkeyColor,
'2006/02/20' + ' ' +FBeginTime + ':00',
'2006/02/20' + ' ' +FEndTime + ':00') as FLastQty,
dbo.Fn_GetBTSINVData2(2,
'',
Fres,
FComkeyColor,
'2006/02/20' + ' ' + FBeginTime + ':00',
'2006/02/20' + ' ' + FEndTime + ':00') as FTotalQty,
Convert(varchar(50), '0.00%') as FBTSRate,
dbo.Fn_GetBTSINVData2(3,
'',
Fres,
FComkeyColor,
'2006/02/20' + ' ' + FBeginTime + ':00',
'2006/02/20' + ' ' + FEndTime + ':00') as FMemo,
dbo.Fn_GetBTSINVData2(4,
'',
Fres,
FComkeyColor,
'2006/02/20' + ' ' +FBeginTime + ':00',
'2006/02/20' + ' ' + FEndTime + ':00') as FType1,
dbo.Fn_GetBTSINVData2(5,
'',
Fres,
FComkeyColor,
'2006/02/20' + ' ' +FBeginTime + ':00',
'2006/02/20' + ' ' +FEndTime + ':00') as FType2,
FComkey
Into #TblBTSINVDaily
From (Select Distinct FRes,
FDate,
FBeginTime,
FEndTime,
FBeginTime1,
FEndTime1,
FDispachQty,
dbo.Fn_GetBTSINVComkeyColor(FRes,
FDate,
FBeginTime,
FEndTime,
FComkey) as FComkeyColor,
FComkey
from (Select Distinct A.FRes,
A.FDate,
A.FBeginTime,
A.FEndTime,
A.FBeginTime1,
A.FEndTime1,
A.FDispachQty,
B.FComkey
from (Select FRes,
FDate,
FBeginTime,
FEndTime,
FBeginTime1,
FEndTime1,
FTargetQty as FDispachQty
from v_TimeTerm
where ((Exists (Select FRes
From TblTimeTerm
where FDate = '2006/02/20') And
FDate = '2006/02/20' and FDefault = 0) Or
(Not Exists (Select FRes
From TblTimeTerm
where FDate = '2006/02/20') and
FDefault = 1))
And FRes in ('YM001', 'YM002', 'YM003', 'YM004',
'YM005', 'YM006', 'YM007')) A
left Join (Select Distinct a.FRes,
b.fcomkey,
SubString(A.FSize,
0,
CharIndex('-', A.FSize)) as FColor,
Substring(a.FCheckInTime, 12, 19) as FTime
from TblMesLotOPInSizeTimesElem as a,
tblLot as b
Where a.FCheckInTime >= '2006/02/20 0'
And a.FCheckInTime <= '2006/02/20 24'
And FRes in ('YM001', 'YM002', 'YM003', 'YM004',
'YM005', 'YM006', 'YM007')
and a.flot = b.fid
Union All
Select Distinct a.FRes,
b.fcomkey,
SubString(A.FSize,
0,
CharIndex('-', A.FSize)) as FColor,
Substring(a.FCheckoutTime, 12, 19) as FTime
from TblMesLotOPOutSizeTimesElem as a,
tblLot as b
Where a.FCheckoutTime >= '2006/02/20 0'
And a.FCheckoutTime <= '2006/02/20 24'
And FRes in ('YM001', 'YM002', 'YM003', 'YM004',
'YM005', 'YM006', 'YM007')
and a.flot = b.fid) B On A.FRes = B.FRes
and b.FTime between
a.FBeginTime and
a.FEndTime) y) x
当记录数为40条时,大概就需要3分30秒左右,记录数再多了就慢得不敢想象,单个执行select Fn_GetBTSINVData2(),select Fn_GetBTSINVComkeyColor()马上就可返回结果.请教各位要怎么优化
让执行速度快点啊?
问题点数：100、回复次数：8Top
1 楼gerrley（gerrley）回复于 2006-03-02 07:32:39 得分 0
怎么没人帮忙啊,急!Top
2 楼wudan8057（上善若水）回复于 2006-03-02 08:11:38 得分 20
如果没有特别需要，改为存储过程执行，速度会提高很多！Top
3 楼gerrley（gerrley）回复于 2006-03-02 08:29:21 得分 0
执行Into #TblBTSINVDaily From(
后面的Select语句需要6秒,但是对Select 的结果集每条记录进行6次dbo.Fn_GetBTSINVData2
耗时一下就上来了,但单一执行select Fn_GetBTSINVData2()是很快的；但是形如：
select dbo.Fn_GetBTSINVData2(0,
'',
Fres,
FComkeyColor,
'2006/02/20' + ' ' + FBeginTime + ':00',
'2006/02/20' + ' ' + FEndTime + ':00') as FFactQty from
tableName 这样执行记录数稍多一点就很慢很慢。
数据库也在本机,我试过把如上语句改为存储过程,但执行耗时是一样的.Top
4 楼Yang_（扬帆破浪）回复于 2006-03-02 08:34:05 得分 50
from 嵌套4层
Exists(not Exists)嵌套2层
多次调用自定义函数
left join
in
......
不如说说你要实现的，才看看怎么重新写
Top
5 楼gerrley（gerrley）回复于 2006-03-02 08:54:20 得分 0
得到一个日报表,这段是把相关数据加载到#TblBTSINVDaily中去,接下来再对#TblBTSINVDaily与其他表关联统计相关栏位的值.我分段测试过,后面的secect对#TblBTSINVDaily与其他表的关联,统计都是一执行就可以出来,就是如上这段向#TblBTSINVDaily加载数据的过程很耗时.
dbo.Fn_GetBTSINVData2的内容如下:
CREATE FUNCTION Fn_GetBTSINVData2
(
@iType int,
@FOP varchar(50),
@FRes varchar(50),
@FComkeyColor varchar(50),
@TermBegin varchar(50),
@TermEnd varchar(50)
)
RETURNS int
AS
BEGIN
Declare @iResult int,@FComkey varchar(50),@dtBeginTerm DateTime,@StrBeginTerm varchar(50),@StrEndTerm varchar(50)
Declare @iLastOPin int,@iLastOpOut int,@iTotalIn int,@iTotalOut int
Declare @TimeTermBegin varchar(50),@TimeTermEnd varchar(50)
Select @iResult=0,@iLastOPin=0,@iLastOpOut=0,@iTotalIn=0,@iTotalOut=0
Select @FComkey='-'+Left(@FComkeyColor,CharIndex('/',@FComkeyColor)-1)+'-'
if Not Exists(Select FID from TblOP Where FID=@FOP)
Select @FOP=''
If (@iType=0) --0 is FactQty
Begin
if (RTrim(LTrim(@FOP))='')
Select @iResult=isNull(Sum(FGoodQty),0) from TblMesLotOPOutSizeTimesElem
Where (FType=10 Or FType=11) And CharIndex(@FComkey,FLot)>0 and FRes=@FRes
And FCheckOutTime>=@TermBegin and FCheckOutTime<=@TermEnd
Else
Select @iResult=isNull(Sum(FGoodQty),0) from TblMesLotOPOutSizeTimesElem
Where (FType=10 Or FType=11) And CharIndex(@FComkey,FLot)>0 and FRes=@FRes And FOP=@FOP
And FCheckOutTime>=@TermBegin and FCheckOutTime<=@TermEnd
End
If (@iType=1) --1 is LastQty
Begin
if (RTrim(LTrim(@FOP))='')
begin
Select @iLastOPin=isNull(Sum(FOPQty),0) from TblMesLotOPInSizeTimesElem
Where CharIndex(@FComkey,FLot)>0 and FRes=@FRes And FCheckInTime<=@TermBegin
Select @iLastOpOut=isNull(Sum(FGoodQty)+Sum(FDefectiveGoodQty),0) from TblMesLotOPOutSizeTimesElem
Where CharIndex(@FComkey,FLot)>0 and FRes=@FRes And FCheckOutTime<=@TermBegin
end Else
begin
Select @iLastOPin=isNull(Sum(FOPQty),0) from TblMesLotOPInSizeTimesElem
Where CharIndex(@FComkey,FLot)>0 and FRes=@FRes And FCheckInTime<=@TermBegin And FOP=@FOP
Select @iLastOpOut=isNull(Sum(FGoodQty)+Sum(FDefectiveGoodQty),0) from TblMesLotOPOutSizeTimesElem
Where CharIndex(@FComkey,FLot)>0 and FRes=@FRes And FCheckOutTime<=@TermBegin And FOP=@FOP
end
Select @iResult=@iLastOPin-@iLastOpOut
End
If (@iType=2) --2 is Total
Begin
Select @dtBeginTerm=Convert(DateTime,DateAdd(second,10,@TermEnd),120) --Delay ten minutes
if (RTrim(LTrim(@FOP))='')
begin
Select @iTotalIn=isNull(Sum(FOPQty),0) from TblMesLotOPInSizeTimesElem
Where CharIndex(@FComkey,FLot)>0 and FRes=@FRes And Convert(DateTime,FCheckInTime,120)<=@dtBeginTerm
Select @iTotalOut=isNull(Sum(FGoodQty)+Sum(FDefectiveGoodQty),0) from TblMesLotOPOutSizeTimesElem
Where CharIndex(@FComkey,FLot)>0 and FRes=@FRes And Convert(DateTime,FCheckOutTime,120)<=@dtBeginTerm
end Else
begin
Select @iTotalIn=isNull(Sum(FOPQty),0) from TblMesLotOPInSizeTimesElem
Where CharIndex(@FComkey,FLot)>0 and FRes=@FRes And Convert(DateTime,FCheckInTime,120)<=@dtBeginTerm And FOP=@FOP
Select @iTotalOut=isNull(Sum(FGoodQty)+Sum(FDefectiveGoodQty),0) from TblMesLotOPOutSizeTimesElem
Where CharIndex(@FComkey,FLot)>0 and FRes=@FRes And Convert(DateTime,FCheckOutTime,120)<=@dtBeginTerm And FOP=@FOP
end
Select @iResult=@iTotalIn-@iTotalOut
End
If (@iType=3) --3 is Memo(Current day CheckIn Quantity)
Begin
Select @StrBeginTerm=@TermBegin
Select @StrEndTerm=@TermEnd
if (RTrim(LTrim(@FOP))='')
begin
Select @iTotalIn=isNull(Sum(FOPQty),0) from TblMesLotOPInSizeTimesElem
Where CharIndex(@FComkey,FLot)>0 and FRes=@FRes And FCheckInTime>=@StrBeginTerm and FCheckInTime<=@StrEndTerm
end Else
begin
Select @iTotalIn=isNull(Sum(FOPQty),0) from TblMesLotOPInSizeTimesElem
Where CharIndex(@FComkey,FLot)>0 and FRes=@FRes And FCheckInTime>=@StrBeginTerm and FCheckInTime<=@StrEndTerm And FOP=@FOP
end
Select @iResult=@iTotalIn
End
If (@iType=4) --4 is The term position
Begin
Select @iResult=0
Select top 1 @TimeTermBegin=FBeginTime,@TimeTermEnd=FEndTime from(
Select Distinct A.FRes,A.FDate,A.FBeginTime,A.FEndTime,B.FComKey from (
Select FRes, FDate,FBeginTime,FEndTime,FBeginTime1,FEndTime1, FTargetQty as FDispachQty from v_TimeTerm
where ((Exists(Select FRes From TblTimeTerm where FDate=Left(@TermBegin,10)) And FDate=Left(@TermBegin,10) and FDefault=0)
Or (Not Exists(Select FRes From TblTimeTerm where FDate=Left(@TermBegin,10)) and FDefault=1)) And FRes in (@FRes)
) A
left Join (
Select Distinct a.FRes, b.fcomkey ,SubString(A.FSize,0,CharIndex('-',A.FSize)) as FColor, SubString(a.FCheckInTime,12,19) as FTime from TblMesLotOPInSizeTimesElem as a, tblLot as b
Where a.FCheckInTime>=Left(@TermBegin,10)+' 0' And a.FCheckInTime<= Left(@TermBegin,10)+' 24' And a.FRes in (@FRes) and a.flot=b.fid
Union All
Select Distinct a.FRes,b.fcomkey,SubString(A.FSize,0,CharIndex('-',A.FSize)) as FColor,SubString( a.FCheckOutTime,12,19) as FTime from TblMesLotOPOutSizeTimesElem as a, tblLot as b
Where a.FCheckOutTime>=Left(@TermBegin,10)+' 0' And a.FCheckOutTime<=Left(@TermBegin,10)+' 24' And a.FRes in (@FRes) and a.flot=b.fid
) B
On A.FRes=B.FRes and b.FTime between a.FBeginTime and a.FEndTime
)x where FComKey=Left(@FComkeyColor,CharIndex('/',@FComkeyColor)-1) Order by FBeginTime ASC
if ((@TimeTermBegin=Substring(@TermBegin,12,5)) And (@TimeTermEnd=Substring(@TermEnd,12,5)))
Select @iResult=1
End
If (@iType=5) --5 is The term position
Begin
Select @iResult=0
Select top 1 @TimeTermBegin=FBeginTime,@TimeTermEnd=FEndTime from(
Select Distinct A.FRes,A.FDate,A.FBeginTime,A.FEndTime,B.FComKey from (
Select FRes, FDate,FBeginTime,FEndTime,FBeginTime1,FEndTime1, FTargetQty as FDispachQty from v_TimeTerm
where ((Exists(Select FRes From TblTimeTerm where FDate=Left(@TermBegin,10)) And FDate=Left(@TermBegin,10) and FDefault=0)
Or (Not Exists(Select FRes From TblTimeTerm where FDate=Left(@TermBegin,10)) and FDefault=1)) And FRes in (@FRes)
) A
left Join (
Select Distinct a.FRes, b.fcomkey ,SubString(A.FSize,0,CharIndex('-',A.FSize)) as FColor, SubString(a.FCheckInTime,12,19) as FTime from TblMesLotOPInSizeTimesElem as a, tblLot as b
Where a.FCheckInTime>=Left(@TermBegin,10)+' 0' And a.FCheckInTime<= Left(@TermBegin,10)+' 24' And a.FRes in (@FRes) and a.flot=b.fid
Union All
Select Distinct a.FRes,b.fcomkey,SubString(A.FSize,0,CharIndex('-',A.FSize)) as FColor,SubString( a.FCheckOutTime,12,19) as FTime from TblMesLotOPOutSizeTimesElem as a, tblLot as b
Where a.FCheckOutTime>=Left(@TermBegin,10)+' 0' And a.FCheckOutTime<=Left(@TermBegin,10)+' 24' And a.FRes in (@FRes) and a.flot=b.fid
) B
On A.FRes=B.FRes and b.FTime between a.FBeginTime and a.FEndTime
)x where FComKey=Left(@FComkeyColor,CharIndex('/',@FComkeyColor)-1) Order by FBeginTime DESC
if ((@TimeTermBegin=Substring(@TermBegin,12,5)) And (@TimeTermEnd=Substring(@TermEnd,12,5)))
Select @iResult=2
End
RETURN @iResult
ENDTop
6 楼Yang_（扬帆破浪）回复于 2006-03-02 09:13:09 得分 0
太复杂了，估计要了解你的表关系才能给出具体建议，
会不会设计上有些问题，引起经常要用子查询（uion 起来的）、经常要用CharIndex和自定义函数(加些冗余字段可以不必这么多字符处理)
自定义函数的效率永远都是问题，尽量避免，拿来玩除外
Top
7 楼gerrley（gerrley）回复于 2006-03-02 11:07:08 得分 0
表结构的问题,现在就没办法解决了,因为是多个人共同在做,表的设计比较混乱.Top
8 楼mljmh（）回复于 2006-03-02 14:55:06 得分 30
考慮改用其它算法。Top
相关问题
关键词
得分解答快速导航
相关链接
广告也精彩
反馈
