Performance Optimization in ASP. NET Design

1. 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. Its performance is much better than that of 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.

2. 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 data, ExecuteNonQuery has a low performance cost.

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. Select the ExecuteScalar data binding option for a single value query.

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. 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. 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"] %>

The easiest way to view the status of each Execution Process on the page is to view details by setting the trace attribute of the page to true.

I. Using Stored Procedures:

1. Performance: stored procedures provide advanced features not available in many standard SQL languages. The function of passing parameters and executing logical expressions helps Application designers process complex tasks. In addition, the stored procedure is stored on the local server, reducing the network transmission bandwidth and execution time required to execute the process. (The stored procedure has prepared SQL statements, so the execution speed is much faster than that of SQL statements in the program)

2. program structure: From the Perspective of program scalability, using stored procedures will facilitate future modifications to the program. For example, if the database structure changes, you only need to modify the corresponding storage structure and the calling part of the program. This part does not fall into the scope of this article, and belongs to the aspect of program structure design. So do not expand here.

3. Program security: using stored procedures can avoid SQL Injection attacks.

Ii. Optimization of query statements (for SQL server2000)

Many write SQL statements for the purpose, regardless of the SQL statement execution efficiency. Here I only provide a method to optimize the table sequence. (The optimization and principles of SQL statements will be discussed in My SQL server2000 study notes)

The SQL statement execution efficiency can be viewed by the SQL server2000 query analyzer.

Optimize table sequence: In general, sqlserver automatically optimizes table connections. Example: select name, no from A join B on A. id = B. id join C on C. id = A. id where name = 'wang'

Although Table A is listed first in From, then B, and finally C. However, SQL server may first use table c. Its selection principle is to limit the query to a single row or a few rows to reduce the total data volume to be searched in other tables. In most cases, SQL server will make the best choice, but if you find that a complex join query is slower than expected, you can use the set forceplan statement to force SQL server to use the table in the order that the table appears. In the preceding example, add: set forceplan on ....... The execution sequence of the set forceplan off table will be executed in the order you wrote. In the query analyzer, view the two execution efficiency types to select the table connection sequence.

Use set forceplan to select the table join Sequence

3. Page optimization (. aspx)

Mainly for page attributes

1. EnableViewState (view State of the page ). Set this parameter to false unless otherwise specified. When ViewState is used, each object must be first serialized to ViewState, and then deserialized by returning the object. Therefore, ViewState has no cost. Minimize the number of objects in ViewState. Viewstate can be disabled in the following cases:

(1) Page Control (. ascx)

(2) The page is not returned to itself.

(3) You do not need to process control events.

(4) The control does not have dynamic or data-bound attribute values (or each postpack is processed in the Code)

ViewState is disabled for a single Page or every Page, as shown in the following figure: <% @ Page EnableViewState = "False" %> for each Page: in the web. in config, <Pages EnableViewState = "false"/> EnableSessionState can be kept by default (if sessionstate is used on the page, it will occupy resources ). EnableViewStateMac: If there are no special security requirements, keep the default value.

• 2 pages in total:
• Previous Page
• 1
• 2
• Next Page