SQL Server performance optimization. net development cainiao Summary

1. ExecuteNonQuery and ExecuteScalar do not need to return the result set for data update. We recommend that you use ExecuteNonQuery. Because no result set is returned, network data transmission can be saved. It returns only the number of affected rows. If you only need to update the data, use ExecuteNonQuery to compare the performance overhead

1. ExecuteNonQuery and ExecuteScalar do not need to return the result set for data update. We recommend that you use ExecuteNonQuery. Because no result set is returned, network data transmission can be saved. It returns only the number of affected rows. If you only need to update the data, use ExecuteNonQuery to compare the performance overhead

1. ExecuteNonQuery and ExecuteScalar
You do not need to return a result set for data update. ExecuteNonQuery is recommended. Because no result set is returned, network data transmission can be saved. It returns only the number of affected rows. If you only need to update the data, ExecuteNonQueryPerformance.
ExecuteScalar only returns the first column of the First row in the result set. Use the ExecuteScalar method to retrieve a single value (for example, an ID number) from the database ). This operation requires less code than executing the operations required to generate a single value for the returned data using the ExecuteReader method.
You only need to update the data using ExecuteNonQuery. Use ExecuteScalar to query a single value.

Ii. SqlDataRead and Dataset Selection
Sqldataread advantages: fast data reading. If you do not need to process the returned data in large quantities, we recommend that you use SqlDataReader.PerformanceIt is much better than datset. Disadvantage: the connection to the database cannot be closed until the data is read. (SqlDataReader reads data quickly forward. The SqlDataReader class provides a method for reading data streams only retrieved from the SQL Server database. It uses the Local Network Data Transmission Format of SQL Server to directly read data from database connections. DataReader must be closed explicitly in time. Data connections can be released in a timely manner .)
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.

Data Binding Selection
3. Data Binding DataBinder
General Binding method <% # DataBinder. Eval (Container. DataItem, "field name") %> Use DataBinder. eval to bind data source (Dataread or dataset ). The data type eval will convert the data object into a string. A lot of work has been done in the underlying binding, and reflection is used.Performance. It is easy to use, but it affects the 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, if the data is directly converted to DataRowViewPerformanceGreatly improved ..
<% # Ctype (Container. DataItem, DataRowView). Row ("field name") %>
We recommend that you use <% # ctype (Container. DataItem, DataRowView). Row ("field name") %> to bind data. When the data volume is large, the speed can be increased by several hundred times. NOTE 2: 1. Add <% @ Import namespace = "System. Data" %>. 2. Note the case sensitivity of the field name ). 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"] %>

I. Considerations for using Ado.net
1. Select the appropriate transaction type
2. Use stored procedures
3. Design the data access layer based on the data usage method.
4. Apply for release as soon as necessary
5. cache data to avoid unnecessary operations
6. Use a service account to connect
7. Reduce round trips
8. Only the required data is returned.
9. Close a resource

Ii. Connection
Database connection is a shared resource with a high overhead of opening and closing. By default, Ado.net enables the connection pool mechanism. Closing a connection does not actually close a physical connection, but simply puts the connection back into the connection pool. Because the shared connection resources in the pool are always limited, if you do not close the connection as soon as possible after using the connection,
This may cause the thread requesting connection to be blocked, affecting the entire systemPerformancePerformance.
1. Open and Close the connection in the Method
This principle has several meanings:
1) The main purpose is to apply for and release as soon as necessary
2) Do not open a connection in the class constructor or release the connection in the destructor. This will depend on garbage collection, and garbage collection is only affected by memory,
Recovery Time is not fixed
3) do not pass connections between methods, which often leads to a long time for the connection to be opened.

The danger of passing connections between methods is highlighted here: a test case has been encountered in the stress test. When the number of users increases, this case should be different.
In this case, all connections in the connection pool are used long ago. After analysis, it is because method A passes an opened connection to Method B, and method B calls another
Open and Close the connection C method. During the entire operation of method A, it takes at least two connections to work successfully, and A connection occupies A very long time, which causes resource shortage in the connection pool, this affects the scalability of the entire system!

2. Close the connection explicitly
The Connection object can be disabled during garbage collection, but it is difficult to rely on garbage collection. We recommend that you use the using statement to explicitly close the connection, as shown in the following example:

Using (SqlConnection conn = new SqlConnection (connString) {conn. Open ();} // Dispose is automatically called on the conn variable here

3. Make sure that the connection pool is enabled.
Ado.net creates a connection pool for each different connection string. Therefore, ensure that the connection string does not contain user-related information. Note that the connection string is
Case sensitive.
4. Do not cache connections
For example, cache the connection to the Session or Application. This method makes no sense when the connection pool is enabled.

Iii. Command
1. Use ExecuteScalar and ExecuteNonQuery
If you want to return a single value like Count (*), Sum (Price), or Avg (Quantity), you can use the ExecuteScalar method. ExecuteScalar returns the value of the first column of the First row and returns the result set as a scalar value. ExecuteScalar not only simplifies the code, but also improvesPerformance.
When you use an SQL statement that does not return rows, such as modifying data (INSERT, UPDATE, or DELETE) or returning only output parameters or return values, use ExecuteNonQuery. This avoids any unnecessary processing for creating an empty DataReader.
2. Use Prepare
When you need to execute the same SQL statement multiple times, you can use the Prepare method to improve efficiency. It should be noted that it is completely unnecessary if only one or two executions are performed. For example:

Cmd. commandText = "insert into Table1 (Col1, Col2) values (@ val1, @ val2)"; cmd. parameters. add ("@ val1", SqlDbType. int, 4, "Col1"); cms. parameters. add ("@ val2", SqlDbType. NChar, 50, "Col2"); cmd. parameters [0]. value = 1; cmd. parameters [1]. value = "XXX"; cmd. prepare (); cmd. executeNonQuery (); cmd. parameters [0]. value = 2; cmd. parameters [1]. value = "YYY"; cmd. executeNonQuery (); cmd. parameters [0]. value = 3; cmd. parameters [1]. value = "ZZZ"; cmd. executeNonQuery ();

3. bind variables
The SQL statement must be compiled into an execution plan before execution. If a variable is bound, the execution plan can be reused by subsequent SQL statements. If the parameters are directly merged into the SQL statement, the execution plan is difficult to reuse because of the variable parameter values. For example, in the example given in the Prepare section above, if the parameter value is directly written into the insert statement, the above four calls will need to compile Four Execution plans.
To avoid this situationPerformanceAll variables must be bound.

4. DataReader
DataReader is most suitable for accessing read-only one-way datasets. Unlike DataSet, datasets are not all in the memory, but are sent with read requests. Once data in the data buffer is found to be read, the data block of the data buffer size is transmitted from the data source. In addition, DataReader maintains a connection and DataSet is disconnected from the connection.

1. explicitly disable DataReader
Similar to the connection, you also need to explicitly disable DataReader. In addition, if the Connection associated with DataReader is only a DataReader service, you can use the Command object's ExecuteReader (CommandBehavior. CloseConnection) method. This ensures that the Connection is automatically closed when the DataReader is disabled.
2. Use index number access instead of name index number access attribute
When you access a column attribute from the Row, the index number is slightly higher than the name method. This type can be considered if it is frequently called, for example in a loop.Optimization. Example:

Cmd. commandText = "select Col1, Col2 from Table1"; SqlDataReader dr = cmd. executeReader (); int col1 = dr. getOrdinal ("Col1"); int col2 = dr. getOrdinal ("Col2"); while (dr. read () {Console. writeLine (dr [col1] + "_" + dr [col2]);}

3. Access attributes using a typed Method
Access a column attribute from Row and explicitly specify the type using the GetString and GetInt32 methods. The efficiency of this method is slightly higher than that of the general GetValue method, because type conversion is not required.

4. Use multiple Datasets
In some scenarios, multiple datasets can be returned at a time to reduce the number of Network Interactions and improve efficiency. Example:

Cmd. commandText = "StoredProcedureName"; // The stored procedure returns multiple result sets. sqlDataReader dr = cmd. executeReader (); while (dr. read () // read first result set dr. nextResult (); while (dr. read ())

V. DataSet
1. Accelerate row search efficiency using Indexes
If you need to search for rows repeatedly, we recommend that you add an index. There are two methods:
1) set the PrimaryKey of the DataTable.
It is applicable to row search by PrimaryKey. Note that the DataTable. Rows. Find method should be called at this time. Generally, the Select method cannot use the index.
2) use DataView
It is applicable to the case where rows are searched by Non-PrimaryKey. You can create a DataView for the able and use the SortOrder parameter to indicate that the index is created. Then use Find or FindRows to Find rows.

1. Reduce Round Trips)
The following method can be used to reduce the round-trip between the Web server and Browser:
1. Enable cache for Browser
If the displayed content is static or has a long change period, Browser cache should be enabled to avoid redundant http requests.

2. Buffer page output
If possible, try to buffer the page output and send it to the client again after processing. This avoids multiple network interactions caused by frequent transmission of small pieces of content. Because the client cannot see the page content before page processing ends, if the size of a page is large, you can use the Response. Flush method. This method forces the output of content in the buffer so far. You should use a reasonable algorithm to control the number of times the Response. Flush method is called.

3. Use Server. Transfer to redirect requests
The Redirect request using the Server. Transfer method is better than the Response. Redirect method. The reason is that Response. Redirect will send a Response header to Broswer, pointing to the redirected URL in the Response Header, then Brower uses the new URL to re-send the request. The Server. Transfer method is a simple Server call without these overhead!
Note that Server. Transfer has limitations: First, it skips the security check; second, it is only applicable to page jumps within the same Web application.

Ii. Avoid blocking and long-time jobs
If you need to run a blocking or long-running operation, you can consider using an asynchronous call mechanism so that the Web server can continue to process other requests.
1. Use Asynchronous Method to call Web Services and remote objects
As long as it is possible to avoid synchronous calls to Web Services and remote objects during request processing, because it occupies ASP. NET thread pool, which will directly affect the Web server's ability to respond to other requests.

2. consider adding the OneWay attribute to the Web method or remote object method that does not require a returned value.
This mode allows the Web Server to return immediately after being called. You can decide whether to use this method based on the actual situation.

3. Use a work queue
Submit the job to the work queue on the server. The client sends a request to poll the job execution result.

Iii. Use Cache
Caching can largely determine the final state of ASP. NET ApplicationsPerformance. Asp.net supports page output caching and partial page caching, and provides Cache APIs to supply applications to Cache their own data. If cache is used, consider the following points:
1. Identify data with high creation and access costs
2. Evaluate the variability of data to be cached
3. evaluation data usage frequency
4. Easy-to-change data and unchanged data separation to be cached. Only unchanged data will be cached.
5. select an appropriate Cache mechanism (besides Asp.net Cache, Application state and Session state can also be used as Cache)

Iv. System Resources
1. Consider implementing resource pools to improvePerformance
2. Call Dispose or Close to release system resources.
3. Do not cache or occupy Resources in the resource pool for a long time
4. Apply as late as possible and release as early as possible

5. Multithreading
1. Avoid creating threads during request processing
Creating a thread during request execution is a costly operation that seriously affects the Web Server'sPerformance. If subsequent operations must be completed by a thread, we recommend that you use the thread pool to create/manage threads.

2. Avoid blocking the request processing thread

3. Avoid asynchronous calls
This is similar to 1. Asynchronous calls may lead to the creation of new threads and increase the burden on the server. Therefore, if no concurrent job is to be executed, do not execute asynchronous calls.

4. Do not rely on thread data slots or static thread Variables
Because the request execution thread is a working thread in the ASP. NET thread pool, the two requests of the same Client may not be processed by the same thread.

6. Page Processing
1. Minimize Page size
Including shortening the control name, CSS class name, removing unnecessary blank lines and spaces, and disabling unwanted ViewState
2,OptimizationA complex and costly cycle
3. Use the computing resources of the client to transfer some operations to the client.
4. Enable the Buffer for page output)
If the Buffer mechanism is disabled, use the following method.
Use a program to open the page output cache:
Response. BufferOutput = true;
Use the @ Page switch to enable the Page output buffer mechanism:
<% @ Page Buffer = "true" %>

Use the Web. config or Machine. config configuration file Node:

5. Use Page. IsPostBack OptimizationPage output
6. Improve cache efficiency and reduce rendering time by separating different content on the page

VII. ViewState
ViewState is a mechanism designed by Asp.net to track status information between page backhaul by server controls.
1. initialize control properties at appropriate time points
ASP. the properties set by the NET control during the execution of constructor and initialization are not tracked and changes to the properties are tracked after the initialization phase, and finally recorded in the _ VIEWSTATE of the IE page. Therefore, selecting a proper execution point of the initialization control attribute can effectively reduce the page size.

2. Carefully select the content to be placed in ViewState.
The content put in ViewState will be serialized/deserialized. Asp.net is serialized for basic types such as String, Integer, and Boolean.OptimizationIf Array, ArrayList, and HashTable are used to store basic types, Type Converter is required for other types. Otherwise, expensive binary serialization programs are used.

3. Disable ViewState.
If you do not need to track the page status, for example, the page does not return (PostBack), you do not need to handle server control events, or the control content is recalculated every time the page is refreshed, you do not need to use ViewState to record the page status. You can set the EnableViewState attribute for a specific WebControl, or at the page level:
// Http://www.bjp111.com
<% @ Page EnableViewState = "false" %>