ASP. NET program optimization suggestions) I. Database Operations

1. Close the database connection immediately after use

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 to pass identity authentication, which consumes server resources.

Source. 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.

Usage (recommended)
Using (sqlconnection conn = new sqlconnection (connstr ))
{} // Do not show off

Or
Try {conn. open ();}
Catch {}
Finally {conn. Close ();}

2. Use stored procedures whenever possible and Optimize Query statements

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. Among all data access methods provided by. NET Framework, SQL Server-based data access is a recommended choice for generating high-performance, scalable Web applications. When using a hosted SQL Server Provider, you can use compiled stored procedures instead of special queries to obtain additional performance improvements.

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.

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, if a query statement does not contain a subquery statement, only useful data and fields should be returned as much as possible to make full use of indexes.

3. Use sqldatareader for read-only data access. Do not use dataset.

The sqldatareader class provides a method for reading data streams only retrieved from the SQL Server database. If you are allowed to use an ASP. NET application when creating it, The sqldatareader class provides higher performance than the dataset class. This is because sqldatareader uses the Local Network Data Transmission Format of SQL Server to directly read data from the database connection. In addition, the sqldatareader class implements the ienumerable interface, which allows you to bind data to server controls. As a powerful and offline database, dataset has a relatively high performance cost.

Sqldataread advantages: fast data reading. If you do not need to process the returned data in large quantities, we recommend that you use sqldatareader. Its performance is much better than that of datset. Disadvantage: the connection to the database cannot be closed until the data is read.

Dataset reads data and caches it in the memory. Disadvantage: High memory usage. If a large amount of processing is required for the returned data, it is better to use dataset to reduce the connection operations to the database. Advantage: Only one connection is required to close the connection to the database.

Generally, sqldatareader is used to read a large amount of data and not process the returned data in large quantities. It is more appropriate to use datset to process a large amount of returned data. The selection of sqldatareader and dataset depends on the implementation of program functions.

4. Data Binding databinder

General Binding method <% # databinder. eval (container. dataitem, "field name") %>

Binding with databinder. Eval does not require concern about the data source (read or dataset ). The data type Eval will convert the data object into a string. It does a lot of work on the underlying binding and uses the reflection performance. This is because it is easy to use, but affects data performance.

Let's see <% # databinder. eval (container. dataitem, "field name") %>. When dataset is bound, dataitem is actually a datarowview (if it is bound to a data reader (dataread), it is an idatarecord .) Therefore, directly converting to datarowview will greatly improve the performance.

<% # Ctype (container. dataitem, datarowview). Row ("field name") %>

We recommend that you use <% # ctype (container. dataitem, datarowview). Row ("field name") %> to bind data. Pay attention to the following two aspects during use:
1. Add <% @ import namespace = "system. Data" %>.
2. Pay attention to the Case sensitivity of field names ). If it is inconsistent with the query, it may be slower than <% # databinder. eval (container. dataitem, "field name") %> in some cases. To further increase the speed, you can use the <% # ctype (container. dataitem, datarowview). Row (0) %> method. But its readability is not high.

The above is the VB.net method. In C #: <% # (datarowview) container. dataitem) ["field name"] %>

5. Multiple result sets are returned.

Whether sqldatareader or datset, multiple result sets are returned. Then, RD. nextresult () or ds. Tables [I] is used to process the data separately, reducing the number of times the database is reconnected. At the same time, try to replace complicated dataset secondary processing with more efficient SQL statements.