Optimize Your DiscuzNT and run it.

Last year, I used DiscuzNT3.0 for secondary development and performance optimization, but I have never had the chance to write down the time relationship. Let's take a long 5.1 vacation and write an article about disruptive things.

I have read the blog of Dai zhenjun from yuanli, and I know that the old generation is a member of the DiscuzNT team. I learned a lot from his blog, the blog I wrote here provides my own views and solutions for some problems. It does not mean to be specific to anyone and adheres to the principles of technical exchanges.

DiscuzNT gave me the impression that

1) powerful functions. All the things you can think of are available;

2) performance needs to be optimized. A large amount of data may cause performance bottlenecks, which is the purpose of this Article ). Due to lack of experience, there are not enough arguments in the previous blog posts. Today we will provide more illustrated arguments.

Now, let's go and start our optimization journey today.

The system environment is as follows:

Software environment:DiscuzNT3.0, sqlserver2000, windwos server 2003

Data Environment:There are about 2.2 million records in the primary table dnt_topics, 3 in the reply table, about 4 million in dnt_posts1, about 5 million in dnt_posts2, about 5 million in dnt_posts3, about 1.7 million in the attachment table, and about 0.2 million in the User table dnt_users, forum table dnt_forums: About 5000 forums

Symptom:When reading the post, if the post contains attachments, it will be very difficult;

Purpose:Speed up viewing, especially when attachments exist.

Hands-on:Find showtopic. aspx. cs. The Code is as follows:

 
 

  
  
postlist = Posts.GetPostList(postpramsInfo, out attachmentlist, ismoder == 1);  
 
 

Let's look at the code of the Posts. GetPostList) method:

 
 

  
  
/// <Summary>
  
  
/// Obtain the specified post DataSet
  
  
/// </Summary>
  
  
/// <Param name = "_ postpramsinfo"> parameter list </param>
  
  
/// <Returns> specify the post DataSet condition </returns>
  
  
Public static List <ShowtopicPagePostInfo> GetPostList (PostpramsInfo postpramsInfo, out List <ShowtopicPageAttachmentInfo> attachList, bool isModer)
  
  
{
  
  
List <ShowtopicPagePostInfo> postList = Data. Posts. GetPostList (postpramsInfo );
  
  
Int adCount = Advertisements. GetInPostAdCount ("", postpramsInfo. Fid );
  
  
 
  
  
Foreach (ShowtopicPagePostInfo postInfo in postList)
  
  
{
  
  
LoadExtraPostInfo (postInfo, adCount );
  
  
}
  
  
AttachList = new List <ShowtopicPageAttachmentInfo> ();
  
  
If (postList. Count = 0)
  
  
Return postList;
  
  
 
  
  
String pidList = GetPidListWithAttach (postList );
  
  
AttachList = Attachments. GetAttachmentList (postpramsInfo, pidList );
  
  
ParsePostListExtraInfo (postpramsInfo, attachList, isModer, postList );
  
  
Return postList; 23}
 
 

It can be seen from this that DiscuzNT is a good idea to assemble all the post IDS into the form of "id1, id2, id3, id4" and then import them into the database to avoid multiple database calls, now let's take a look at it and see that it calls the database script. It calls this process dnt_getattachmentlistbypid and uses profiler to track this process to see its performance.

As shown in the figure above, exec dnt_getattachmentlistbypid @ pidlist = '000000' indicates that the cpu Of the script is 5163797, reads = 4531, and duration = 152641. It is very impressive, if there are 10 people calling this process at the same time, it is estimated that the database is under heavy pressure. If there are 100 people, it is hard to imagine. So how can we optimize this process? First, let's take a look at how it is written and whether indexes are used.

 
 

  
  
ALTER   PROCEDURE [dnt_getattachmentlistbypid]  
  
  
@pidlist varchar(500)  
  
  
AS 
  
  
SELECT   
  
  
[aid],  
  
  
[uid],  
  
  
[tid],  
  
  
[pid],  
  
  
[postdatetime],  
  
  
[readperm],  
  
  
[filename],  
  
  
[description],  
  
  
[filetype],  
  
  
[filesize],  
  
  
[attachment],  
  
  
[downloads],  
  
  
[attachprice],  
  
  
[width],  
  
  
[height]   
  
  
FROM [dnt_attachments]   
  
  
WHERE CHARINDEX(','+RTRIM([dnt_attachments].[pid])+',', ','+@pidlist+',')>0GO 
 
 

The primary search condition here is pid. If an index is created on the pid column and the index can be used in the process, the results will be better. The optimization work is divided into the following steps:

1) whether the pid column has an index;

2) whether the index is used in the process;

3) Optimize SQL scripts;

4) tracking the optimized results;

Let's go down one step by one:

1) sp_helpindex dnt_attachments check whether there is an index. For example, you can see that the pid column has an index. If there is no index, create an index.

2) Check whether indexes are used. CTRL + L check the execution plan of the following statement. The index he uses is PK_dnt_attachments, and the expected pid is useless.

3) if the expected index is not used, we will optimize it. In the above dnt_getattachmentlistbypid process, where charindex (',' + RTRIM ([dnt_attachments]. [pid]) + ',' + @ pidlist + ',')> 0 performs column operations on the pid, which is the culprit, we can try to remove this column operation. The process is changed to the following:

 
 

  
  
ALTER    PROCEDURE [dnt_getattachmentlistbypid]  
  
  
@pidlist varchar(500)  
  
  
AS 
  
  
 
  
  
declare @sql nvarchar(2000)  
  
  
 
  
  
set @sql = '  
  
  
SELECT   
  
  
[aid],  
  
  
[uid],  
  
  
[tid],  
  
  
[pid],  
  
  
[postdatetime],  
  
  
[readperm],  
  
  
[filename],  
  
  
[description],  
  
  
[filetype],  
  
  
[filesize],  
  
  
[attachment],  
  
  
[downloads],  
  
  
[attachprice],  
  
  
[width],  
  
  
[height]   
  
  
FROM [dnt_attachments]   
  
  
WHERE pid in (' + @pidlist + ')'  
  
  
 
  
  
exec(@sql)  
  
  
GO 
 
 

4) after the optimization, We will track the performance after optimization. Let's take a look at tracking the same process. For the same parameter, 2nd are before optimization, and 4th are after optimization, the optimization effect is quite unsatisfactory)

So far, our optimization has come to an end.