Recently, a new version of the system is about to be released. During the beta customer test, many problems were exposed. In addition to some business and abnormal problems, the others are all focused on performance. I am fortunate to have the opportunity to come into contact with these performance tuning opportunities, and of course I have to learn and summarize.

Performance optimization is a commonplace issue. Typical performance issues include slow page response, interface timeout, high server load, low concurrency, frequent database deadlocks, etc. There are many reasons for performance issues, such as disk I/O, memory, network, algorithm, large data volume, etc. We can roughly divide performance issues into four levels: code level, database level, algorithm level, and architecture level.
So below I will share with you the tools, methods and techniques for performance tuning based on actual performance optimization cases.

2. Let’s talk about mentality first

When it comes to performance issues, you may first think of trouble or headache, because generally performance issues are more urgent, which may affect customer experience at the least and cause financial losses at the worst. Moreover, performance issues are more hidden and difficult to find. Therefore, we have no idea where to start for a while, and at this time, we are likely to reject it from the bottom of our hearts and are unwilling to take on this hot potato.

And it just so happens that performance tuning is an important indicator of a programmer's level.

Because dealing with emergencies such as bugs, crashes, tuning, and intrusions can better reflect the gap between mediocre programmers and ideal programmers than programming itself. When faced with an unknown problem, how to locate the core problem under complex conditions, how to analyze the potential causes of the problem, how to eliminate interference and restore a minimum verifiable scenario, and how to grasp key data to verify your own guesses and experiments are all the best scenarios to reflect the programmer's thinking ability. Yes, in terms of the criteria for measuring ideal programmers, thinking ability is more important than experience.

So, if you are not content with mediocrity, please embrace every opportunity for performance tuning. When you have the right mindset, half of the performance problems you face are already solved.

3. Let’s talk about skills

When you encounter a performance issue, don't rush to use the tool first. First, understand the background of the problem and the severity of the problem. Then make an estimate based on your own experience. For example, a customer reported a performance issue and said that the system was down and had caused financial losses. This kind of issue involving money is more sensitive to everyone. Decide whether to take the blame based on your level. This is not escaping, but self-knowledge.

After understanding the background of the problem, the next step is to try to reproduce the problem. If it can be reproduced in the test environment, then this problem is easy to track and analyze. If the problem cannot be reproduced stably or can only be reproduced in the production environment, then the problem is relatively difficult. At this time, you should immediately collect on-site evidence, including but not limited to capturing dumps, collecting application and system logs, paying attention to CPU memory status, database backups, etc., and then you may try to reproduce it again, such as restoring the customer database to the test environment to reproduce it.

Regardless of whether the problem can be reproduced, the next step is to roughly classify the problem, whether it is a business logic problem at the code level, an operation time-consuming problem at the database level, or a throughput problem at the system architecture. How to determine it? I tend to start with the database. My usual practice is to use a database monitoring tool to track the SQL time consumption first. If a long SQL statement is monitored, it is basically a problem at the database level, otherwise it is at the code level. If it is at the code level, after studying the code, it can be refined into an algorithm or architecture level problem.

After determining the type of problem, it is time to use tools to accurately locate the problem point:

• For SQL time-consuming issues, it is recommended to use the free Plan Explorer to analyze the execution plan.
• To locate code problems, we recommend using the Performance Analysis that comes with VS, followed by RedGate's performance analysis suite .NET Developer Bundle; then there is Jet Brains' dotTrace -- .NET performance profiler, dotMemory-- .NET memory profiler; and then there is the anti-human Windbg; and so on.

After accurately locating the problem points, it is time to start optimization. I believe that at this step, it is the choice of optimization strategy, which I will not expand here.

After optimization, of course we have to test it at the end. After all, we have to have a good idea of ​​how much we have optimized.

The above is a bit long-winded, so let’s get straight to the case.

4. Case Study

Below I will share my optimization cases at the code level, database level, and algorithm level.

4.1. SQL Optimization Case

Case 1: Customers reported that it took about 10 minutes to compile a settlement report to collect data from ten days.

Since I just learned how to use RedGate's analysis tool a few days ago, I wanted to use the tool to analyze the problem after trying to reproduce it locally. However, this tool always reports an error when starting a site in webdev mode. At that time, I was stubborn and kept trying to solve the error problem of this tool. As a result, I couldn't solve it after working for a long time. Finally, I had to give up the tool and choose to use SQL Server Profiler to monitor the time consumption of SQL statements. As soon as I tracked it, the problem was directly exposed, and the entire screen was full of repeated SQL statements, as shown in the figure below.

Now the problem is obvious. Most likely the code is looping and concatenating SQL statements. Searching the code based on the SQL keywords captured, it turns out to be the case.

#region updates the intermediate temporary table data that combines the data of the three tables. When the direct transfer order of the upstream document is pushed down multiple times, the transfer quantity and price and tax total are only calculated once string sSql = string.Format(@
"SELECT FENTRYID FROM {0} GROUP BY FENTRYID HAVING COUNT(FENTRYID) > 1", sJoinDataTempTable);
using(IDataReader reader = DBUtils.ExecuteReader(this.Context, sSql)) {
    while (reader.Read()) {
        sbSql.AppendFormat(@"
UPDATE {0} SET FDIRECTQTY = 0,FALLAMOUNT = 0 
WHERE FSEQ NOT IN (
SELECT TOP 1 FSEQ FROM {0} WHERE FENTRYID = {1}) AND FENTRYID = ({1});"
, sJoinDataTempTable, Convert.ToInt32(reader["FENTRYID"]));
        listSqlObj.Add(new SqlObject(sbSql.ToString(), new List < SqlParam > ()));
        sbSql.Clear();
    }
}
#endregion

Seeing this code, let's not judge the pros and cons of this code, because after all, the code comments are clear, saving us the effort of sorting out the business. This SQL is mainly intended to do deduplication processing, and it is obvious that the wrong solution is chosen. The modified code is as follows:

string sqlMerge = string.Format(@"
merge into {0} t1
using(
select min(Fseq) fseq,Fentryid from {0} t2 group by fentryid
) t3 on (t1.fentryid = t3.fentryid and t1.fseq <> t3.fseq)
when matched then
update set t1.FDIRECTQTY = 0, t1.FALLAMOUNT = 0
", sJoinDataTempTable);

listSqlObj.Add(new SqlObject(sqlMerge, new List < SqlParam > ()));
sbSql.Clear();

After the modification, the test time for the same amount of data was reduced from 10 minutes to about 10 seconds.

4.2. Code Optimization Case

Case 2: The customer reported that it took about 7 minutes to save 100 lines of sales order for shipment verification.

After getting this problem and reproducing it locally, I found that there was no abnormality in monitoring the SQL time consumption, so I focused on analyzing the code. Because the business logic of the sendable quantity verification is extremely complex, and this function is implemented directly in a class file, 3500+ lines of code, plus sporadic comments, are really daunting. Escape is not a solution, so I still use tools to analyze it.
This time I chose the Performance Profiler that comes with VS, which is an extremely powerful performance tuning tool in the development environment. For our current case, we only need to track the dll corresponding to the specified service. The steps are as follows:

1. Analyze-->Profiler-->New Performance Session
2. Open Performance Explorer
3. Find the newly added Performance Session, right-click Targets, and then select Add Target Binary to add the dll file to be tracked.
4. Run the application
5. Select Performance Session, right-click Attach to the corresponding process to track and analyze performance
6. During the tracking process, you can pause and stop tracking at any time

After the tracking is completed, the sampling results of this case are as follows:

At the same time, Performance Profiler also gives suggestions for the problem, as shown below:

Among them, the first and fourth items roughly indicate that the program consumes a lot of I/O, and the first generation of GC has too high a percentage of garbage that is not released in time. According to the sampling results in the above figure, we can directly see that the performance bottleneck is caused by frequent operations on DataTable in the code. After reading the code, I found that this is indeed the case. All quantity statistics are processed by looping through DataTable in the code. The final optimization strategy is equivalent to a major refactoring, moving all the calculation logic in the code that traverses DataTable to SQL. Due to the large amount of code, it will not be released.

Case 3: Customer feedback showed that it took about 40 minutes to import 1,000 orders in batches and was prone to interruption.

Again, we still try to rewrite locally first. After testing, it takes about 5 minutes to import 101 documents in batches. Next, we open the SQL monitoring tool and no time-consuming statements are found. However, considering that it is a batch import operation, although the time consumption for each statement is not much, it is obvious when multiplied by the base of 100. Next, we use RedGate's Ants Performance Profiler to track it.

This tool is quite intuitive and can monitor both code and SQL execution at the same time. The first step is to create a new Profiler Session, and the second step is to set it up, as shown below. Choose the appropriate tracking method based on your application category.

To address this issue, we tracked the call stack and SQL time consumption results as shown below:

First, from the Hit Count in the call stack, we can see that it is a batch process because the entry function is called only once; second, we can see that the code processes each document in a loop because the Hit Count matches the number of documents we import in batches; third, 10201 suddenly appears. If you have a certain degree of numerical sensitivity, you have found the cause of this performance problem. I won't keep you in suspense here, 101 x 101 = 10201.
Did you understand what is going on? There is a situation where loops are nested. Let's walk through the code to confirm:

//Save.cs
public override void EndOperationTransaction(EndOperationTransactionArgs e) {
    //Omit other code foreach(DynamicObject dyItem in e.DataEntitys) {
        //WriteBackReceiveBill wb = new WriteBackReceiveBill();
        wb.WriteBackForSave(e, this.Context);
    }
}

//WriteBackReceiveBill .cs
public void WriteBackForSave(EndOperationTransactionArgs e, Context contx) {
    //Omit other codes:
    foreach(DynamicObject item in e.DataEntitys) {
        //do something 
    }
}

Well, there is an empty loop in the outer layer but it does nothing. The modification is very simple, just delete the invalid outer loop.

4.3. Algorithm Optimization Case

Case 4: A full-process tracking report times out.

This report is used to track the business process data flow of all documents from order placement to delivery. All process data is stored in the database table according to the tree result, similar to this:

The process in the figure is:
Sales Contract-->Sales Order-->Delivery Notice-->Sales Delivery Notice

In order to construct a flowchart, the previous processing method was to retrieve the process data and construct the flowchart through code. This is the reason for the poor performance.

In this case, we need to use our accumulated experience. For tree-structured tables, we can also query directly through SQL, which requires the use of SQL Server's CTE syntax for recursive query.

5. Summary

Performance tuning is a gradual process. It cannot be achieved overnight. It is important to accumulate little by little. This article does not elaborate on the selection and use of tools, and I hope readers will not get hung up on this. When you really want to solve a problem, I believe that using tools will not be difficult for you.

Finally, I will summarize my tuning ideas:

1. Adjust your mindset and respond positively
2. Understand the performance context, collect evidence, and try to reproduce
3. Problem classification: first monitor the SQL time consumption to roughly determine whether it is caused by SQL or code level
4. Use performance analysis tools to identify problem areas
5. Tuning test