自定义函数造成死锁,能帮着看看吗
张海霖 2006-05-20 10:39:43 这个一个老大帮着写的自定义函数,一直用的好好的。但是最近发现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个,也还是时间超长,造成死锁。怎么改一下呢。问题的关键在哪里?