Practical experience of ADO. Net Without retained exposure (2) [reprinted]

Search data in Dataset

When querying rows matching specific conditions in dataset, you can use index-based search to improve search performance. When the primarykey value is assigned to the able, an index is created. When dataview is created for the datatable, an index is also created. The following are some indexing-based search techniques.

1) If you want to query the columns that make up the primarykey of the datatable, use able. Rows. Find instead of datatable. Select.

2) For queries involving non-primary key columns, you can use dataview for multiple queries of data to improve performance. When the sorting order is applied to dataview, an index is created for search. Dataview exposes the find and findrows methods to query data in the basic datatable.

3) If you do not need a sort view for a table, you can still create a dataview for the datatable to use index-based search. Note that this will benefit only when multiple query operations are performed on the data. If you only execute a single query, the processing required to create an index will reduce the performance improvement caused by using the index.

Dataview Construction

If dataview is created and the attributes of sort, rowfilter, or rowstatefilter are modified, dataview creates an index for the data in the basic datatable. When creating a dataview object, you must use the dataview constructor. It uses the sort, rowfilter, and rowstatefilter values as the constructor parameters (together with the basic able ). The result is an index created once. Create an "empty" dataview and then set the sort, rowfilter, or rowstatefilter attributes. This will cause the index to be created at least twice.

Paging

Ado. Net can explicitly control what data is returned from the data source and how much data is locally cached in the dataset. There is no unique answer to the paging of the query results, but there are some design applications belowProgramSkills to consider.

1) avoid using the dataadapter. Fill overload with startrecord and maxrecords values. When dataset is filled in this way, only the number of records specified by the maxrecords parameter (starting from the record identified by the startrecord parameter) is used to fill the dataset, but the complete query is always returned in any case. This will lead to unnecessary processing, which is used to read "unwanted" records. In addition, in order to return additional records, unnecessary server resources will be exhausted.

2) the technology used to return only one page of records at a time is to create an SQL statement and combine the WHERE clause, order by clause, and top predicates. This technique depends on a method that uniquely identifies each row. When browsing the next page, modify the WHERE clause to include all records whose unique identifiers are greater than the last unique identifiers of the current page. When browsing a previous page record, modify the WHERE clause to make it return records with all unique identifiers smaller than the first unique identifiers on the current page. Both queries only return the top page of the record. When you browse a previous page, you need to sort the results in descending order. This will effectively return the last page of the query (if needed, you may have to re-sort the results before it is displayed ).

3) Another technique that returns only one page of records at a time is to create an SQL statement that combines the use of top predicates with embedded select statements. This technique does not rely on a method that uniquely identifies each row. The first step to using this technology is to multiply the number of pages required by the page size. The results are then passed to the top predicates of the SQL query, which are sorted in ascending order. Then, this query is embedded into another query. The latter selects the top page size from the embedded query results in descending order. Essentially, the last page of the embedded query is returned. For example, to return the third page of the query result (the page size is 10), you should write the following command:

Select top 10 * from [from: 51item.net] (Select top 30 * from MERs order by id asc) as Table1 Order by ID DESC

Note: The returned results page is displayed in descending order. Sort again if needed.

1) if the data does not change frequently, You can locally maintain a record cache in dataset to improve performance. For example, you can store 10 pages of useful data in the local dataset, and query new data from the data source only when the user browses the first and last pages beyond the cache.

Fill dataset with schema

When dataset is filled with data, the dataadapter. Fill method uses the existing architecture of dataset and fills it with the data returned from selectcommand. If no table name in dataset matches the name of the table to be filled, the fill method creates a table. By default, fill only defines the column and column type.

By setting the missingschemaaction attribute of dataadapter, You can override the default behavior of fill. For example, to create a table schema for fill, it also includes primary key information, unique constraints, column attributes, whether to allow null, maximum column length, read-only columns, and automatically incremental columns, the dataadapter. missingschemaaction is specified as missingschemaaction. addwithkey. Alternatively, before calling dataadapter. Fill, you can call dataadapter. fillschema to ensure that the schema is in place when the dataset is filled.

The call to fillschema generates an additional route to the server for retrieving additional architecture information. To achieve optimal performance, you must specify the dataset architecture before calling fill, or set the missingschemaaction of dataadapter.

Best practices for using commandbuilder

Assuming that selectcommand executes a single select table, commandbuilder automatically generates the insertcommand, updatecommand, and deletecommand attributes of dataadapter Based on the selectcommand attribute of dataadapter. The following are some tips for using commandbuilder for optimal performance.

1) The use of commandbuilder should be limited to design or ad hoc solutions. The processing required to generate the dataadapter command attribute will affect the performance. If you know the content of the insert, update, and delete statements in advance, you can set them explicitly. A good design technique is to create a stored procedure for the insert/update/delete command and explicitly configure the properties of the dataadapter command to use them.

2) commandbuilder uses the selectcommand attribute of dataadapter to determine the values of other command attributes. If the selectcommand of dataadapter has been changed, make sure to call refreshschema to update the command attributes.

3) if the dataadapter command property is empty (the command property is empty by default), commandbuilder generates only one command for it. Commandbuilder does not overwrite the command attributes explicitly. If you want commandbuilder to generate a command for the previously set command attribute, set the command attribute to null.

Batch Processing SQL statement

many databases support merging or batch processing multiple commands into one single command for execution. For example, SQL Server allows you to use semicolons (;) to separate commands. Combining Multiple commands into a single command can reduce the number of server trips and improve application performance. For example, you can store all the predefined deletions locally in the application, and then issue a batch processing command call to delete them from the data source.
although this can improve the performance, managing data updates in dataset may increase the complexity of applications. To keep it simple, you may need to create a dataadapter for each able in dataset.