Recommendations for some SQL Server performance optimizations in NET development

First, ExecuteNonQuery and ExecuteScalar

Updates to the data do not need to return a result set, and ExecuteNonQuery is recommended. The network data transfer is omitted because no result set is returned. It simply returns the number of rows affected. If you only need to update the data with ExecuteNonQuery performance is less expensive.

ExecuteScalar it returns only the first column of the first row in the result set. Use the ExecuteScalar method to retrieve a single value (such as an ID number) from the database. This operation requires less code than the operations that are required to generate a single value by using the ExecuteReader method, which returns data.

Just update the data with ExecuteNonQuery. Single value queries using ExecuteScalar.

Ii. selection of Sqldataread and datasets

Sqldataread Advantage: Read data very quickly. If you do not need to do a lot of processing of the returned data, it is recommended to use SqlDataReader, which has a much better performance than Datset. Disadvantage: Close the connection to the database until the data is read out. (SqlDataReader read data is fast forward.) The SqlDataReader class provides a way to read a forward-only stream of data retrieved from a SQL Server database. It uses the SQL Server native Network data transfer format to read data directly from the database connection. DataReader need to be in time to explicitly close. The connection to the data can be released in a timely manner. )

The dataset reads the data and slows it down in memory. Cons: High memory usage. If you need to do a lot of processing of the returned data with a dataset, you can reduce the connection operation to the database. Pros: You can close the connection to the database with just one connection.

In general, read large amounts of data, do not do a lot of processing to return data with SqlDataReader. It is appropriate to use Datset for large amounts of returned data. The selection of SqlDataReader and datasets depends on the implementation of the program functions.

Selection of data bindings

Third, the data binding DataBinder

The general binding method <%# DataBinder.Eval (Container.DataItem, "field name")%> with DataBinder.Eval binding does not have to care about the data source (Dataread or DataSet). Do not care about the type of data eval converts the data object to a string. A lot of work was done at the bottom of the binding, using reflective performance. This is because it is easy to use, but it affects data performance.

See <%# DataBinder.Eval (Container.DataItem, "field name")%>. When binding to a DataSet, DataItem is actually a datarowview (if it is a data reader (Dataread) it is a idatarecord. As a result, a direct conversion to DataRowView will bring a significant boost to performance.

<%# ctype(Container.DataItem,DataRowView).Row("字段名") %>

Binding to data is recommended using <%# CType (Container.dataitem,datarowview). Row ("field name")%>. Increase the speed by up to hundreds of times times when the volume of data is large. NOTE 2:1. You need to add <%@ Import namespace= "System.Data"%>.2 to the page. Note the case of the field name (pay special attention). If inconsistent with the query, in some cases results in slower than <%# DataBinder.Eval (Container.DataItem, "field name")%>. If you want to increase speed further, you can use <%# CType (Container.dataitem,datarowview). Row (0)%> method. But its readability is not high.

The above is the vb.net of the wording. In C #:

<@% ((DataRowView)Container.DataItem)["字段名"] %>

Some thinking principles of applying ADO

1. Select the appropriate transaction type

2. Using Stored Procedures

3. Design the data access layer based on the way data is used

4. Apply if necessary and release as soon as possible

5. Cache data to avoid unnecessary operations

6. Connect using the service account

7. Reduced round trip

8. Return only the data you need

9. Close a resource that can be closed

Second, Connection

A database connection is a shared resource, and the overhead of opening and closing is large. The connection pooling mechanism is enabled by default, and closing the connection does not actually close the 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 you use the connection,

It is possible that the thread that is requesting the connection is blocked, affecting the performance of the entire system.

1. Open and close the connection in the method

This principle has several layers of meaning:

1) The main purpose is to apply and release as soon as necessary

2) do not open the connection in the constructor of the class, release the connection in the destructor. Because this will depend on garbage collection, and garbage collection is only affected by memory,

The timing of recycling is uncertain

3) do not pass the connection between methods, which often causes the connection to remain open for too long

Here we emphasize the dangers of passing connections between methods: A test case was encountered in a stress test, and when the number of users is increased, the case is more than

Case long ago, all connections in the connection pool were used. After analysis, it is because the a method passes an open connection to the B method, and the B method calls a

The C method that opens and closes the connection on its own. During the whole operation of the A method, it needs to occupy at least two connections to be able to work successfully, and one of the connections occupies a very long time, resulting in a tight connection pool resources, affecting the overall scalability of the system!

2. Explicitly close the connection

The connection object itself can be turned off during garbage collection, and relying on garbage collection is a bad strategy. It is recommended that you explicitly close the connection using a using statement, as in the following example:

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

3. Ensure connection pooling is enabled

ADO creates a connection pool for each of the different connection strings, so make sure that the connection string does not appear to be relevant to the specific user. Also, be aware that the connection string is

Case-sensitive.

4. Do not cache the connection

For example, cache the connection to a session or application. This does not make sense when connection pooling is enabled.

Third, Command

1. Using 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, returning the result set as a scalar value. ExecuteScalar not only simplifies the code, it also improves performance because it can be done in a single step.

Use ExecuteNonQuery when using SQL statements that do not return rows, such as modifying data (INSERT, update, or delete), or returning only output parameters or return values. This avoids any unnecessary processing that is used to create an empty DataReader.

2. Using Prepare

When you need to repeat the same SQL statement multiple times, consider using the prepare method to improve efficiency. It is important to note that if you do it only once or two times, it is completely unnecessary. 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,  "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. Using bound variables

The SQL statement needs to be compiled into an execution plan before execution. If the binding variable is used, then the execution plan can be reused by the subsequent execution of the SQL statement. If you merge the parameters directly into the SQL statement, the execution plan is difficult to reuse because of the changing values of the parameters. For example, given in the prepare section above, if the parameter values are written directly into the INSERT statement, then the four calls above will need to compile four execution plans.

To avoid this loss of performance, it is required to use the binding variable mode.

Iv. DataReader

The DataReader is best suited for accessing read-only unidirectional data sets. Unlike datasets, datasets are not all in memory, but the data blocks of a data buffer size are transferred from the data source as soon as the data in the data buffer is found to be read, with the read request being issued. Additionally, the DataReader remains connected, and the dataset is disconnected from the connection.

1. Explicitly close DataReader

Similar to a connection, you also need to explicitly close DataReader. Also, consider using the ExecuteReader (commandbehavior.closeconnection) mode of the Command object if the connection associated with DataReader is only for DataReader services. This ensures that when the DataReader is closed, the connection is automatically turned off.

2. Access attributes with index number access instead of name index number

Accessing a column property from row uses an index number in a slightly higher way than using a name. This type of optimization can be considered if it is frequently called, such as in loops. Examples are as follows:

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. Accessing properties using typed methods

Accessing a column property from row, using GetString, GetInt32, an explicit method of specifying a type, has a slightly higher efficiency than a common GetValue method, because it does not require a type conversion.

4. Use multi-data sets

Some scenarios may consider returning multiple datasets at once to reduce network interactions and increase efficiency. Examples are as follows:

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

1. Use index to speed up the efficiency of finding rows

If you need to find rows repeatedly, it is recommended that you increase the index. There are two ways of doing this:

1) Set the PrimaryKey of the DataTable

Applies to the case where rows are found by PrimaryKey. Note that the DataTable.Rows.Find method should be called at this time, and the general usage Select method cannot take advantage of the index.

2) Use DataView

Applies to the case where rows are found by Non-primarykey. A DataView can be created for the DataTable and indexed by the SortOrder parameter indication. Then find the row using Find or FindRows.

One, reduce the return trip (reduce Round trips)

Use the following method to reduce the round trip between the Web server and browser:

1. Enable caching for browser

If the rendered content is static or has a long period of change, you should enable the browser cache to avoid making redundant HTTP requests.

2. Buffered page output

If possible, buffer the page output as much as possible, and then transfer to the client at the end of processing, which avoids the multiple network interactions caused by the frequent delivery of small chunks of content. Because this way the client cannot see the page content until the end of the page processing, consider using the Response.Flush method if the size of a page is large. This method forces the output of the contents of the buffer so far, and you should use a reasonable algorithm to control the number of calls to the Response.Flush method.

3. Using Server.Transfer Redirect request

Redirecting requests using the Server.Transfer method is better than the Response.Redirect method. The reason is that Response.Redirect sends a response header back to Broswer, indicating the redirected URL in the response header, and then brower the request again with the new URL. The Server.Transfer method is directly a simple service-side call, without these costs at all!

It is important to note that Server.Transfer has limitations: first, it skips security checks, and second, it applies only to pages that are within the same web app.

Second, avoid blocking and long-time operation

If you need to run blocking or long-running operations, consider using the mechanism of asynchronous calls so that the Web server can continue to process other requests.

1. Invoking Web services and remote objects asynchronously

As long as it is possible to avoid synchronous calls to Web services and remote objects during the processing of requests, because it occupies a worker thread in an ASP. NET thread pool, this will directly affect the ability of the Web server to respond to other requests.

2. Consider adding the OneWay property to a Web method or a remote object that does not require a return value

This mode allows the Web server to return immediately after it is called. The use of this method may be determined on the basis of actual circumstances.

3. Use Work queue

Submits the job to the work queue on the server. The client polls the execution results of the job by sending a request.

Third, using the cache

Caching can determine to a large extent the ultimate performance of an ASP. ASP. NET supports page output caching and page partial caching, and provides the cache API for applications to cache their own data. You can consider the following points when using the cache:

1. Identify data that is expensive to create and access

2. Assess the variability of cache data needs

3. Frequency of use of evaluation data

4, will be cached data separation of volatile data and unchanging data, only cache unchanging data

5. Select the appropriate caching mechanism (except the ASP. Application state and session state can also be used as a cache)

Iv. system resources

1. Consider implementing a resource pool to improve performance

2. Explicitly call Dispose or close to release system resources

3. Do not cache or consume resources in a resource pool for a long time

4. Apply as late as possible, release as early as possible

Five, multi-threaded

1. Avoid creating threads during request processing

Creating a thread during the execution of a request is a costly operation that can severely affect the performance of your Web server. If subsequent operations must be done with threads, it is recommended to create/manage threads through the thread pool.

2. Avoid blocking threads that handle requests

3. Avoid asynchronous calls

This is similar to the situation in 1. An asynchronous call can cause a new thread to be created, increasing the burden on the server. Therefore, do not execute an asynchronous call if there are no concurrent jobs to execute.

4. Do not rely on thread data slots or thread static variables

Because the thread executing the request is a worker thread in the ASP. Two requests from the same client are not necessarily handled by the same thread.

Six, page processing

1. Minimize page size

This includes shortening the name of the control, the name of the CSS class, removing unnecessary blank lines and spaces, disabling unwanted viewstate

2. Optimization of complex and costly cycles

3, reasonable use of client computing resources, to transfer some operations to the client for

4. Buffer to enable page output

If the buffer mechanism is closed, you can open it in the following way.

To open a page output cache using a program:

Response.BufferOutput = true;<%@ Page Buffer = "true" %>

Use the <pages> node for the Web. config or machine.config configuration file:

buffer="true" …>

5. Optimize page output with Page.IsPostBack

6, by separating the different content of the page, to improve the efficiency of the cache and reduce the time of rendering

Seven, ViewState

ViewState is a mechanism that ASP. NET is designed to track state information between page callbacks for a service-side control.

1. Initialize control properties at the appropriate point in time

Asp. NET controls are not tracked for properties that are set during the execution of constructors, during initialization, and are tracked after the initialization phase and are eventually recorded in the __viewstate of IE pages. Therefore, choosing a reasonable execution point to initialize the control property can effectively reduce the page size.

2, carefully choose to put in the content of ViewState

The content placed in the ViewState is serialized/deserialized, and ASP. NET is optimized for serialization of basic types such as String, Integer, Boolean, if the array, ArrayList, and Hashtable are stored with a higher efficiency , but other types need to provide a type Converter, or a costly binary serializer will be used.

3. Close ViewState

If you do not need to track page state, such as the page does not return (PostBack), do not need to handle server-side control events, or every time the page refreshes, the control content will be recalculated, then you do not need to use ViewState to record the page state. You can set the EnableViewState property on a specific WebControl, or at the page level:

<%@ Page EnableViewState="false" %>

Welcome to my public number (Sync update article): Donet technology sharing platform
Read the original

Recommendations for some SQL Server performance optimizations in NET development