SQL Statement Performance Optimization (cont.) _mssql

The previous article describes one of the best ways to use SQL statements (Performance Tuning--SQL statements) that you encounter in your project, but that's not complete. In the process of comparison, the Max function is not taken into account, after a person to remind quickly to do a test, testing process and learned a lot of things.

The last time you used the execution efficiency issue with SELECT COUNT (*) and select *, because my requirement was to get a total number of data to automatically give the new ID, and then the user gives the new ID in the way that Max is available. This is also a good idea (we used the function, just because the system data itself is not suitable for the function), and then I did a test of the performance of the Max function.

First time Test:

Declare @dddd Datetime Set @dddd =getdate ()
select MAX (num) from t_basicinformation
Select [Takes time]=datediff (ms,@ Dddd,getdate ())

Test results:

By contrast, the efficiency of using this method seems to be less efficient than select COUNT (*) (at the time measured at 3ms), is that really the case? Then I looked up some data and found that Max's efficiency was relatively high, so why did our test result be like this? Let's get to the test.

Second Test:

① Statement A

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

Test results:

② Statement II

Declare @dddd Datetime Set @dddd =getdate ()
Select MAX (foodid) from T_selllogdetail
Select [Takes time]=datediff (ms,@ Dddd,getdate ())

Test results:

③ Statement Three

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

Test results:

After the second test you will find that the same table, different fields, the Max function does not perform the same effect. And then starting with the data type, the data type is as follows:

In this way, our test results will be solved by different problems, in the implementation of the Max function, depending on the data type of different execution efficiency. If it is a primary key and is an integral type, the efficiency is highest, and the int is more efficient than the character type when not the primary key. In fact, these are very 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 were some places to watch out for Max. Therefore, in our ordinary study, we must learn to expand learning, not only to the results of their own wants. The result is certainly the system needs, but for us in the study period, this is far from enough, we have to continue to explore, in order to win greater progress.

About the SQL statement performance optimization, small series to introduce to everyone here, hope to help everyone!