SQL statements in ASP. NET

Database Connection and Shutdown

To access database resources, you need to create a connection, open a connection, and close the connection. These processes need to exchange information with the database multiple times for authentication, which consumes server resources. ASP. NET provides a Connection Pool to improve the impact of enabling and disabling databases on performance. The system places the user's database connection in the connection pool. If necessary, the connection is taken out. When the connection is closed, the connection is withdrawn and the next connection request is waited. The size of the connection pool is limited. If you still need to create a connection after the connection pool reaches the maximum limit, the performance will be greatly affected. Therefore, after a database connection is established, the connection is enabled only when operations are required. The connection is closed immediately after use, so as to minimize the time for opening the database connection and avoid exceeding the connection limit.

1. Use stored procedures

Stored procedures are a set of pre-compiled SQL statements stored on the server, similar to the batch processing files in the DOS system. Stored Procedures provide the ability to access databases immediately and process information quickly. Using the stored procedure can avoid multiple compilation of commands. After one execution, the execution plan will reside in the cache. In the future, you only need to directly call the binary code in the cache. In addition, the stored procedure runs on the server and is independent from the ASP. NET program to facilitate modification. The most important thing is that it can reduce the transmission of database operation statements over the network.

2. Optimize Query statements

In ASP. NET, the ADO connection consumes a considerable amount of resources. The longer the SQL statement runs, the longer the system resources are occupied. Therefore, try to use optimized SQL statements to reduce execution time. For example, a query statement that does not contain a subquery statement makes full use of indexes.

String operation performance optimization

1. Use the ToString method of Value Type

When connecting strings, you often use the "+" sign to directly add numbers to strings. This method is simple and can get the correct results. However, because different data types are involved, the numbers must be converted to the reference type through the boxing operation before they can be added to the string. However, the packing operation has a great impact on the performance, because during such processing, a new object will be allocated in the managed heap, and the original value will be copied to the newly created object. The Value Type ToString method can be used to avoid packing and improve application performance.

2. Use the StringBuilder class

The String class object cannot be changed. In essence, the re-assignment of the String object re-creates a String object and assigns the new value to the object, the ToString method does not significantly improve the performance. When processing strings, it is best to use the StringBuilder class. Its. NET namespace is System. Text. This class does not create a new object, but directly performs operations on strings through Append, Remove, Insert, and other methods, and returns the operation results through the ToString method.

Its definition and operation statement are as follows:

 
 

  
  
Int num;
  
  
System. Text. StringBuilderStr=NewSystem. Text. StringBuilder (); // create a string
  
  
Str. Append (num. ToString (); // Add the numeric value num
  
  
Response. Write (str. ToString); // display the operation result
 
 

1. Introduction to ASP. NET Framework
2. Introduction to ASP. NET Applications
3. Processing Methods of ASP. NET framework
4. Analysis on ASP. NET Security Architecture
5. Overview ASP. net mvc and FubuMVC core framework