注：本文仅针对 DiscuzNT3.0, sqlserver 2000版本，其他版本请勿对号入座.
在前两篇已经对看帖，发帖回帖做了优化，做过优化之后论坛访问起来果然顺畅多了。随着论坛帖子数的不断增加，论坛出现了一个新的问题，管理员在删帖的时候经常报错，超时，删除失败。
由于有了之前的两次经验，现在找起问题来比之前要快多了，现在先找到删帖的存储过程 dnt_deletetopicbytidlist3，脚本如下：
@tidlist AS VARCHAR(2000),
@chanageposts AS BIT
AS
DECLARE @postcount int
DECLARE @topiccount int
DECLARE @todaycount int
DECLARE @sqlstr nvarchar(4000)
DECLARE @fid varchar(2000)
DECLARE @posterid varchar(200)
DECLARE @tempFid int
DECLARE @tempPosterid int
DECLARE @tempLayer int
DECLARE @temppostdatetime datetime
DECLARE @tempfidlist AS VARCHAR(1000)
SET @fid = ''
SET @posterid = ''
SET @postcount=0
SET @topiccount=0
SET @todaycount=0
SET @tempfidlist = '';
IF @tidlist<>''
BEGIN
-- 问题：列运算
DECLARE cu_dnt_posts CURSOR FOR SELECT [fid],[posterid],[layer],[postdatetime]
FROM [dnt_posts3]
WHERE CHARINDEX(','+RTRIM([dnt_posts3].[tid])+',', ','+@tidlist+',') > 0
OPEN cu_dnt_posts
FETCH NEXT FROM cu_dnt_posts into @tempFid,@tempPosterid,@tempLayer,@temppostdatetime
WHILE @@FETCH_STATUS = 0
BEGIN
SET @postcount = @postcount + 1
IF @tempLayer = 0
BEGIN
SET @topiccount = @topiccount + 1
END
IF DATEDIFF(d,@temppostdatetime,GETDATE()) = 0
BEGIN
SET @todaycount = @todaycount + 1
END
IF CHARINDEX(',' + LTRIM(STR(@tempFid)) + ',',@fid + ',') = 0
BEGIN
-- 把所有关联的论坛id保存到变量 @tempfidlist，后面会用到
SELECT @tempfidlist = ISNULL([parentidlist],'')
FROM [dnt_forums] WHERE [fid] = @tempFid
IF RTRIM(@tempfidlist)<>''
BEGIN
SET @fid = RTRIM(@fid) + ',' + RTRIM(@tempfidlist) + ',' + CAST(@tempFid AS VARCHAR(10))
END
ELSE
BEGIN
SET @fid =RTRIM(@fid) + ',' + CAST(@tempFid AS VARCHAR(10))
END
END
IF @chanageposts = 1
BEGIN
-- 更新用户发帖数量
UPDATE [dnt_users] SET [posts] = [posts] - 1 WHERE [uid] = @tempPosterid
END
FETCH NEXT FROM cu_dnt_posts into @tempFid,@tempPosterid,@tempLayer,@temppostdatetime
END
CLOSE cu_dnt_posts
DEALLOCATE cu_dnt_posts
IF LEN(@fid)>0
BEGIN
SET @fid = SUBSTRING(@fid,2,LEN(@fid)-1)
IF @chanageposts = 1
BEGIN
UPDATE [dnt_statistics] SET [totaltopic]=[totaltopic] - @topiccount, [totalpost]=[totalpost] - @postcount
-- 问题：列运算
UPDATE [dnt_forums]
SET [posts]=[posts] - @postcount,
[topics]=[topics] - @topiccount,
[todayposts] = [todayposts] - @todaycount
WHERE CHARINDEX(','+RTRIM([fid])+',', ','+@fid+',') > 0
END
-- 问题：列运算
DELETE FROM [dnt_favorites] WHERE CHARINDEX(','+RTRIM([tid])+',', ','+@tidlist+',') > 0 AND [typeid]=0
-- 问题：列运算
DELETE FROM [dnt_polls] WHERE CHARINDEX(','+RTRIM([tid])+',', ','+@tidlist+',') > 0
-- 问题：列运算
DELETE FROM [dnt_posts3] WHERE CHARINDEX(','+RTRIM([tid])+',', ','+@tidlist+',') > 0
-- 问题：列运算
DELETE FROM [dnt_mytopics] WHERE CHARINDEX(','+RTRIM([tid])+',', ','+@tidlist+',') > 0
END
-- 问题：列运算
DELETE FROM [dnt_topics] WHERE CHARINDEX((','+RTRIM([closed])+','), ','+@tidlist+',') > 0
OR CHARINDEX(','+RTRIM([tid])+',', ','+@tidlist+',') > 0
-- 问题：列运算
UPDATE [dnt_tags] SET [count]=[count]-1, [fcount]=[fcount]-1
WHERE [tagid] IN (SELECT [tagid] FROM [dnt_topictags]
WHERE CHARINDEX(','+RTRIM([tid])+',', ','+@tidlist+',') > 0 )
-- 问题：列运算
DELETE FROM [dnt_topictags] WHERE CHARINDEX(','+RTRIM([tid])+',', ','+@tidlist+',') > 0
-- 问题：列运算
DELETE FROM [dnt_topictagcaches] WHERE CHARINDEX(','+RTRIM([tid])+',', ','+@tidlist+',') > 0
OR CHARINDEX((','+RTRIM([linktid])+','), ','+@tidlist+',') > 0 end
go
删帖的操作流程是这样的：
1）传入@tidlist, 用逗号把帖子id隔开，比如“1,2,3,4,5” 这样的格式；
2）用游标 cu_dnt_posts 循环读取 dnt_posts3 表（此处用到了列运算，已标出）；
3）找到该贴子id关联的所有论坛id，放到@tempfidlist变量，后面会用到这个变量，这个语句
SELECT @tempfidlist = ISNULL([parentidlist],'') FROM [dnt_forums] WHERE [fid] = @tempFid
同时更新用户的发帖数量，这一句 UPDATE [dnt_users] SET [posts] = [posts] - 1 WHERE [uid] = @tempPosterid
4）关闭游标
5）更新统计信息，这句 UPDATE [dnt_statistics] SET [totaltopic]=[totaltopic] - @topiccount, [totalpost]=[totalpost] - @postcount
6）更新论坛发帖数量，这句 UPDATE [dnt_forums]
WHERE CHARINDEX(','+RTRIM([fid])+',', ','+@fid+',') > 0
7）删除收藏夹，删除回复表，删除用户发帖信息表 等等
8）删除主贴表，删除标签表 等等
这个过程代码量很大，逻辑稍微的有些复杂， 更糟糕的是里面大半的操作都做了列运算，charindex()这个函数就是，oh，my god，我们论坛目前的主贴表是 320万，其他回复表，最少的也有400万，最多有1000万，我可以分负责任的说，只要一运行这个过程，就死定了。不信？我们还是请profiler来帮我们看看吧（当然这是在测试环境，生产环境请勿尝试，否则后果自负）。
后台管理删帖界面：
点击提交，等了N久，得到下图（超时了）：
看看profiler跟踪的结果，如下图：
看看这几个性能参数 cpu 27360，reads 71290，duration 30016 ，看到这里应该了然了，不超时是不可能的。
优化的方案有很多种，我这里只提供一种， 修改他的存储过程，不修改他的业务逻辑，我的原理是，用in代替charindex()函数，到目前为止dnt所有的数据库性能问题都和charindex()这个函数有关，而我也都是改成in，这次比之前稍微有难度，因为他涉及到了游标,修改要谨慎，改过之后的过程脚本如下：
@tidlist AS VARCHAR(2000),
@chanageposts AS BIT
AS DECLARE @postcount int
DECLARE @topiccount int
DECLARE @todaycount int
DECLARE @sqlstr nvarchar(4000)
DECLARE @fid varchar(2000)
DECLARE @posterid varchar(200) declare @sql nvarchar(4000) SET @fid = ''
SET @posterid = ''
SET @postcount=0
SET @topiccount=0
SET @todaycount=0 IF @tidlist = ''
return set @sql =
'DECLARE @tempFid int ' + char(13) + char(10) +
'DECLARE @tempPosterid int ' + char(13) + char(10) +
'DECLARE @tempLayer int ' + char(13) + char(10) +
'DECLARE @temppostdatetime datetime ' + char(13) + char(10) +
'DECLARE @tempfidlist AS VARCHAR(1000) ' + char(13) + char(10) +
'SET @tempfidlist = '''' ' + char(13) + char(10) +
'declare @cu_dnt_posts cursor ' + char(13) + char(10) +
'set @cu_dnt_posts = CURSOR read_only forward_only FOR
SELECT [fid],[posterid],[layer],[postdatetime] FROM [dnt_posts3] WHERE tid in('
+ @tidlist