这个一个老大帮着写的自定义函数,一直用的好好的。但是最近发现sql总是死锁,后来发现原来出在这个函数上面。能帮着改改吗,是因为执行时间太长所以死锁的吗?
/*--news统计相关文章数--*/
CREATE function f_news_linknum(@ArticleID int,@num int)
returns int
as
begin
declare @t1 table(nkey varchar(200))
declare @t2 table(ArticleID int,nkey varchar(200))
declare @nkey varchar(200),@cnt int
select @nkey=nkey from article where articleid=@ArticleID
if isnull(@nkey,'')=''
return 0
while (charindex(',',@nkey)>0)
begin
insert into @t1 select left(@nkey,charindex(',',@nkey)-1)
set @nkey = stuff(@nkey,1,charindex(',',@nkey),'')
end
insert into @t1 select @nkey
if (select count(*) from @t1)<@num
return 0
insert into @t2
select
a.ArticleID,b.nkey
from
article a,@t1 b
where
a.articleid!=@ArticleID
and
charindex(','+b.nkey+',',','+a.nkey+',')>0
select @cnt=count(*)
from (select ArticleID from @t2 group by ArticleID having count(*)>=@num) t
return @cnt
end
使用方法:
/*--更新文章的相关文章数--*/
update article
set
link1 =dbo.f_news_linknum(ArticleID, 1),
link2 =dbo.f_news_linknum(ArticleID, 2),
link3 =dbo.f_news_linknum(ArticleID, 3),
link4 =dbo.f_news_linknum(ArticleID, 4),
link5 =dbo.f_news_linknum(ArticleID, 5),
link6 =dbo.f_news_linknum(ArticleID, 6),
link7 =dbo.f_news_linknum(ArticleID, 7),
link8 =dbo.f_news_linknum(ArticleID, 8),
link9 =dbo.f_news_linknum(ArticleID, 9),
link10=dbo.f_news_linknum(ArticleID,10)
我即使把这个语句拆成10个,也还是时间超长,造成死锁。怎么改一下呢。问题的关键在哪里?
/*--news统计相关文章数--*/
CREATE function f_news_linknum(@ArticleID int,@num int)
returns int
as
begin
declare @t1 table(nkey varchar(200))
declare @t2 table(ArticleID int,nkey varchar(200))
declare @nkey varchar(200),@cnt int
select @nkey=nkey from article where articleid=@ArticleID
if isnull(@nkey,'')=''
return 0
while (charindex(',',@nkey)>0)
begin
insert into @t1 select left(@nkey,charindex(',',@nkey)-1)
set @nkey = stuff(@nkey,1,charindex(',',@nkey),'')
end
insert into @t1 select @nkey
if (select count(*) from @t1)<@num
return 0
insert into @t2
select
a.ArticleID,b.nkey
from
article a,@t1 b
where
a.articleid!=@ArticleID
and
charindex(','+b.nkey+',',','+a.nkey+',')>0
select @cnt=count(*)
from (select ArticleID from @t2 group by ArticleID having count(*)>=@num) t
return @cnt
end
使用方法:
/*--更新文章的相关文章数--*/
update article
set
link1 =dbo.f_news_linknum(ArticleID, 1),
link2 =dbo.f_news_linknum(ArticleID, 2),
link3 =dbo.f_news_linknum(ArticleID, 3),
link4 =dbo.f_news_linknum(ArticleID, 4),
link5 =dbo.f_news_linknum(ArticleID, 5),
link6 =dbo.f_news_linknum(ArticleID, 6),
link7 =dbo.f_news_linknum(ArticleID, 7),
link8 =dbo.f_news_linknum(ArticleID, 8),
link9 =dbo.f_news_linknum(ArticleID, 9),
link10=dbo.f_news_linknum(ArticleID,10)
我即使把这个语句拆成10个,也还是时间超长,造成死锁。怎么改一下呢。问题的关键在哪里?
即将 CREATE function f_news_linknum(@ArticleID int,@num int)
改成 CREATE function f_news_linknum(@ArticleID int,@num int,@nkey)
当然函数内容也要相应的修改。执行时
update article
set
link1 =dbo.f_news_linknum(ArticleID, 1,nkey),
link2 =dbo.f_news_linknum(ArticleID, 2,nkey),
link3 =dbo.f_news_linknum(ArticleID, 3,nkey),
link4 =dbo.f_news_linknum(ArticleID, 4,nkey),
link5 =dbo.f_news_linknum(ArticleID, 5,nkey,nkey),
link6 =dbo.f_news_linknum(ArticleID, 6,nkey),
link7 =dbo.f_news_linknum(ArticleID, 7,nkey),
link8 =dbo.f_news_linknum(ArticleID, 8,nkey),
link9 =dbo.f_news_linknum(ArticleID, 9,nkey),
link10=dbo.f_news_linknum(ArticleID,10,nkey)
sp_lock
from
article a,@t1 b
where
a.articleid!=@ArticleID
and
charindex(','+b.nkey+',',','+a.nkey+',')>0
数据量一大家产生超强的计算量。运算就可能超时。我重建了你的表和函数。