这个问题很诡异!

Question

如下为一表内字符串分拆SQL:
select 
     a.TA001,
     a.TA002,
     KPC02=substring(a.KPC02,b.id,charindex(',',a.KPC02+',',b.id)-b.id),
     a.TA015,''
from 
 (select  TA001,TA002,round(TA015/(datediff(day,KPC02,KPC03)+1),3) as TA015,
    dbo.split1(convert(char(8),KPC02,112),convert(char(8),KPC03,112)) as KPC02 
    from MOCTA where rtrim(TA026)+rtrim(TA027)+rtrim(TA028)='22011011060183C0003' and TA011='1' )  a, tep b
where substring(','+a.KPC02,id,len(','))=','
order by a.TA001,a.TA002,a.TA015运行括号内sql,得到124条记录, 如下:
5102 20060407935 887.11100000000000 20060310,20060313,20060314,20060315,20060316,20060317,20060318
5102 20060407936 887.11100000000000 20060310,20060313,20060314,20060315,20060316,20060317,20060318
………………………..
Tep表只有一个字段id, 数据为1至8000执行上述整段sql后半小时都不返回结果,估计S锁了
但如果在括号内sql 加上top 124, 5秒就执行完了, 如果把括号内结果集into到一个实表再进行关联,只需要2秒返回结果这是10么原因, 估计是查询结果集作为关联表导致死锁, 请高手指点.加top 124 后SQL如下:
select 
     a.TA001,
     a.TA002,
     KPC02=substring(a.KPC02,b.id,charindex(',',a.KPC02+',',b.id)-b.id),
     a.TA015,''
from 
 (select top 124  TA001,TA002,round(TA015/(datediff(day,KPC02,KPC03)+1),3) as TA015,
    dbo.split1(convert(char(8),KPC02,112),convert(char(8),KPC03,112)) as KPC02 
    from MOCTA where rtrim(TA026)+rtrim(TA027)+rtrim(TA028)='22011011060183C0003' and TA011='1' )  a, tep b
where substring(','+a.KPC02,id,len(','))=','
order by a.TA001,a.TA002,a.TA015

Answer 1

与dbo.split1() 有关系吗, 
create function split1(
@start char(8),
@end char(8)
) returns varchar(400)
with  ENCRYPTION 
as
begin
declare @result varchar(400)
declare @st datetime
declare @ed datetime
declare @i as int
declare @l as int
set @result=@start
set @st=cast(@start as datetime)
set @ed=cast(@end as datetime)
set @i=datediff(day,@st,@ed)
set @l=0
while @l<@i
begin
  if not exists(select 1 from CMSMP where MP004=CONVERT(CHAR(8),dateadd(day,@l+1,@st),112))
  set @result=@result+','+CONVERT(CHAR(8),dateadd(day,@l+1,@st),112)
  set @l=@l+1
end
return(@result)
end

Answer 2

split1 的目的是把两个日期 比如'20060601','20060604'变成'20060601,20060602,20060603,20060604' ,以供下一步骤分拆.

Answer 3

只所以说他诡异,是因为括号内SQL
select  
    TA001,TA002,round(TA015/(datediff(day,KPC02,KPC03)+1),3) as TA015,
    dbo.split1(convert(char(8),KPC02,112),convert(char(8),KPC03,112)) as KPC02 
    from MOCTA where rtrim(TA026)+rtrim(TA027)+rtrim(TA028)='22011011060183C0003' and TA011='1'
变成
select top 124 
    TA001,TA002,round(TA015/(datediff(day,KPC02,KPC03)+1),3) as TA015,
    dbo.split1(convert(char(8),KPC02,112),convert(char(8),KPC03,112)) as KPC02 
    from MOCTA where rtrim(TA026)+rtrim(TA027)+rtrim(TA028)='22011011060183C0003' and TA011='1'
就行了,需要注意的是,这段SQL返回记录数也就是124条,为什么加top 124 就行?

Answer 4

这一句SQL执行时间多长:
--------------------------------------------------------------------------------------------------------------------------------
select
    TA001,
    TA002,
    round(TA015/(datediff(day,KPC02,KPC03)+1),3) as TA015,
    dbo.split1(convert(char(8),KPC02,112),convert(char(8),KPC03,112)) as KPC02 
from
    MOCTA 
where 
    rtrim(TA026)+rtrim(TA027)+rtrim(TA028)='22011011060183C0003' 
    and 
    TA011='1'

Answer 5

dbo.split1开启锁并导致S锁,目前只有先into到一个临时表,然后再来关联奇怪的是为什么加top 124就行呢?

Answer 6

看看加top和不加top的执行计划的差异.估计是优化算法出了些问题, 导致采用了不恰当的处理方法.