SQL statement performance optimization (continued) and SQL statement Performance Optimization

SQL statement performance optimization (continued) and SQL statement Performance Optimization

In the previous article, I introduced an optimization method (Performance Optimization-SQL statement) that I encountered in the project, but it was not complete enough. In the comparison process, the max function was not taken into account. After reminders, I quickly did a test and learned a lot during the test.

The execution efficiency of select count (*) and select * was used last time, because I needed to obtain a total number of data to automatically generate a new id, then, the user gave a new id using max. In fact, this is also a good idea (we also used this function at the time, but it is not suitable for using this function because of system data problems ), then I tested the performance of the max function.

First test:

Declare @ dddd Datetime Set @ dddd = GETDATE () select MAX (num) from T_BasicInformationselect [time spent] = DATEDIFF (MS, @ dddd, GetDate ())

Test results:

Through comparison, we found that the execution efficiency of this method seems to be less efficient than that of select count (*) (the time measured at that time was 3 ms). Is that true? Later, I checked some information and found that the efficiency of max is relatively higher. Why is the test result so? Let's continue the test.

The second test:

① Statement 1

Declare @ ddd Datetime Set @ ddd = GETDATE () select MAX (id) from t_SellLogDetailselect [time spent] = DATEDIFF (MS, @ ddd, GetDate ())

Test results:

② Statement 2

Declare @ dddd Datetime Set @ dddd = GETDATE () select MAX (foodId) from t_SellLogDetailselect [time spent] = DATEDIFF (MS, @ dddd, GetDate ())

Test results:

③ Statement 3

Declare @ ddddd Datetime Set @ ddddd = GETDATE () select MAX (orderId) from t_SellLogDetailselect [time spent] = DATEDIFF (MS, @ ddddd, GetDate ())

Test results:

After the second test, we will find that the max function execution effects of different fields in the same table are different. Then we start with the data type. The data type is as follows:

In this way, we can solve different problems in the test results. during the execution of the max function, the execution efficiency varies depending on the data type. If it is a primary key and an integer type, the efficiency is the highest. If it is not a primary key, the efficiency of int type is higher than that of character type. In fact, these are easy to understand and have their own use methods.

Summary:

After the first test, I did not consider other things. Later, I found that there are some things to be aware of in max. Therefore, in our ordinary study, we must learn to expand, not just the results we want. Results are certainly required by the system, but for us in the learning period, this is far from enough. We need to constantly explore to win more progress.

I will introduce the performance optimization of SQL statements here, and hope to help you!

Articles you may be interested in:

• MySQL latency Association Performance Optimization Method
• Amazing MySQL query performance optimization
• MySQL performance optimization (2)
• Optimize SQL statements to improve database performance