Performance Optimization--sql statement (cont.)

The previous blog describes one of the optimizations that you have encountered in your project using SQL statements (Performance optimization--sql statements), but not completely. During the contrast, the Max function was not taken into account, and a test was made after a reminder, and many things were learned during the test. Now to share with you

The last time I used the execution efficiency of SELECT COUNT (*) and select *, because my requirement was to get a total number of data to automatically give the new ID, and then the Netizen gave the new ID that can be given using Max. This is also a good idea (we used the function at the time, just because the system data itself is not suitable for the function), and then I tested the performance of the Max function.

First Test:       

Declare @dddd Datetime Set @dddd =getdate () Select MAX (num) from T_basicinformationselect [Takes time]=datediff (MS, @dddd, GetDate ())

Test results:

By contrast, the efficiency of using this method is not as good as the efficiency of SELECT COUNT (*) (at the time measured at 3ms), so is the fact true? Later, I looked up some information and found that Max's efficiency is relatively higher, so why is our test results like this? Let's proceed with the test.

Second Test:  

① Statement One

Declare @ddd Datetime Set @ddd =getdate () select MAX (ID) from T_selllogdetailselect [Takes time]=datediff (MS, @ddd, GETDATE ())

Test results:

② Statement Two       

Declare @dddd Datetime Set @dddd =getdate () Select MAX (Foodid) from T_selllogdetailselect [spends time]=datediff (MS, @dddd, GetDate ())

Test results:

③ Statement three      

Declare @ddddd Datetime Set @ddddd =getdate () Select MAX (orderId) from T_selllogdetailselect [spends time]=datediff (MS, @ddddd, GetDate ())

Test results:

Doing the second test will find that the same table, the different fields, the Max function performs differently. Then, starting with the data type, the data type is as follows:

In this way, the different problems of our test results will be solved, in the implementation of the Max function, depending on the data type and different execution efficiency. If the primary key is the integer type, the efficiency is highest, and the int is more efficient than the character type when it is not a primary key. In fact, these are easy to understand, each has its own way of use.

Summary:

After the first test, there were no other things to consider, and then there was something else to watch out for in Max. Therefore, in our normal study, we must learn to expand learning, not only the results you want. The result is the system needs, but for the learning period of our, this is far from enough, we must continue to explore, in order to win greater progress.

Performance Optimization--sql statement (cont.)