SQL optimized ASP. NET ADO

The original Published time: 2009-08-26--from my Baidu article [imported by moving tools]

The general direction of consideration

1) Design the data access layer according to the way the data is used

2) cache data to avoid unnecessary operations

3) Connect using the service account

4) Connect as necessary, release as soon as possible

5) Close the resource that can be closed

6) Reduced round trip

7) return only the required data

8) Select the appropriate transaction type

9) Using Stored procedures

Determine how cross-layer data is delivered based on performance, maintainability, and implementation difficulty

2 Concrete implementations

1) Select the appropriate data Provider
Use the dedicated data Provider as much as possible, and here is a performance comparison table

As can be seen from the above figure SqlClient speed is the fastest, the main reason is that other data providers have undergone several levels of conversion, as shown below

As can be seen from the figure, SqlClient direct access to the DB netlib and other data providers have undergone a two-layer conversion, so in the design of multi-tier applications, not the more the better, but should be in the scalability and performance between the compromise, increase the level will degrade performance.

1) Database connection

I. Open and close the connection in the method, that is, do not open the connection in the class's constructor, and close the connection in the class's destructor.

II. The connection is closed explicitly after use. Because of the connection pooling support, shutting down the connection simply puts the connection back into the connection pool, which is not really destroyed and does not result in a performance overhead, but increases the available connections in the connection pool and improves performance.

III. When using DataReaders, specify CommandBehavior.CloseConnection

Iv. when using Fill () and Updata (), do not manually open the connection. Because the DataAdapter will automatically open the connection, if it is a command it needs to be turned on manually.

V. Avoid checking the State property of the OleDbConnection, which has a significant performance overhead.

VI. Use connection pooling. This approach can significantly improve performance. By default, connection pooling is used when you connect to a database through SqlClient, and you can control the connection pool's maximum value, minimum value, and whether connection pooling is enabled through the connection string.

2) SQL instructions

I. Check the input of SQL and use parameters to directly use character connection to be susceptible to injection attacks.

II. Return only the rows and examples needed

Iii. using paging functionality for large data sets

Iv. batch executes SQL to avoid multiple round trips.

V. Use the ExecuteNonQuery method if no data is returned

Vi. when returning a scalar, use the ExecuteScalar method

VII. Do not use CommandBuilder during run time, although it is very convenient, but expensive.

3) Stored Procedures

I. Use stored procedures as much as possible

II. For OleDbCommand, the instruction type is CommandType.Text

III. Using SqlCommand, instruction type CommandType.StoredProcedure

Iv. using output parameters whenever possible

V. Consider set NOCOUNT on in SQL Server to turn off the Count function for SQL Server.

4) Business

5) Using Parameters

I. Using parameters on stored procedures

Ii. creating parameters and specifying types

Iii. parameter objects can be cached

6) DataReader and datasets

I. DataReader object needs to be closed

II. When using DataReader, use CommandBehavior.CloseConnection to close the connection

III. DataReader applications in read-only, forward-only tumbling data access scenarios

Iv. only want to quickly access data, do not need the cache function should use DataReader

Datasets are used when data caches are required and are passed between tiers. It can store multiple result sets, and can locate data freely while offline.

Overall, improving performance can reduce scalability and maintenance challenges, improve performance in the event of functional and non-functional requirements, and consider performance at design time, and good design is much better than poor design modification later in the performance.

SQL optimized ASP. NET ADO