Performance Optimization--sql statements

The previous time the personnel user put forward a requirement: When the file is added, the file number is generated automatically, but the speed is slow. Each time you go to add a page, the file number will always come out in a few seconds. The user then asks for the number to appear when it enters the page.

When I saw this problem, I thought it might be a question of query method. The first is the error in the query process, and the second is that it takes too long to look up data from the database. If you have a train of thought, do it.

First, to exclude the idea, when adding information, you need to query the method only one, query out the total number of records, and then generate a new file number. In the process of the code, there are some problems in the SQL statements that are used. As follows:

public string Querybasicfileid ()        {                        string-sql = "SELECT * from T_basicinformation";                       List<t_basicinformation> Basicinfo = this. Currentdal.loaditems (SQL). ToList ();                      String Countbasic = (Basicinfo.count + 1). ToString ("000000");                       String id = DateTime.Now.ToString ("yyyyMMdd") + "" + Countbasic;            return ID;        }

Reading this method will find the problem, the first is that the total number of queries only need to use the count (*) method, and here is a select *, which means that you need to query all the record information. From the surface we can see that select COUNT (*) is not the same speed as SELECT *, so how big is the difference?

Monitor in SQL Server to see how fast the two can differ.

First Use " select * ":     

Declare @d Datetime Set @d=getdate () SELECT * from T_basicinformationselect [spend time]=datediff (Ms,@d,getdate ())

The "SELECT COUNT (*)" is executed as follows:     

Declare @dd Datetime Set @dd =getdate () Select COUNT (*) from T_basicinformationselect [Takes time]=datediff (MS, @dd, GETDATE ())

Through the comparison between the two found that their speed will be at least 500 times times, of course, from the implementation of the results, the difference between them is only 1 seconds, perhaps from the intuitive point of view, the difference is not big. However, careful will find that in the system using the underlying framework, relative to the use of the underlying, the speed of the query relative to the direct use of native SQL will be reduced, if you add in the program to run and use inefficient statements, then its speed is not the difference between the 1 seconds.

Summary:

Seemingly the same line of code, in different environments produces the effect is not the same. So, at any time, it's not going to be a feature implementation. It also needs to be considered in terms of the performance of the program and the experience of the user. If the function is only realized, efficiency is not mentioned, then the good program will not be loved by users.

Performance Optimization--sql statements