Performance Analysis of Dataset and SqlDataRead reading Databases

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. SqlDataReader
Class provides a method to read data streams only retrieved from the SQL Server database. It uses SQL Server
The Local Network Data Transmission Format for reading data directly from the database connection. 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.

* In general, 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. Use ExecuteScalar to query a single value.

Data Binding Selection

3. Data Binding databinder

General Binding method <% # DataBinder. Eval (Container. DataItem, "field name") %>

Binding with DataBinder. eval does not require you to care about the 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. Pay attention to two aspects during use: 1. You need to add <% @
Import namespace = "System. Data" %>. 2. Pay attention to the Case sensitivity of field names ). If it is inconsistent with the query, in some cases, the ratio of <% # DataBinder. Eval (Container. DataItem, "field name ")
%> Slow. 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"] %>