The following guidelines suggest ways to make data access in your application efficient.
-
Use SQL Server and stored procedures for data access Of all the data access methods provided by the .NET Framework, using SQL Server for data access is the recommended choice for building high-performance, scalable Web applications. When using the managed SQL Server provider, you can get an additional performance boost by using compiled stored procedures wherever possible instead of SQL commands. For information about using SQL Server stored procedures, see Configuring Parameters (ADO.NET).
-
Use the SqlDataReader class for a fast forward-only data cursor The SqlDataReader class provides a forward-only data stream retrieved from a SQL Server database. If you can use a read-only stream in your ASP.NET application, the SqlDataReader class offers higher performance than the DataSet class. The SqlDataReader class uses SQL Server's native network data-transfer format to read data directly from a database connection. For example, when binding to the SqlDataSource control, you will achieve better performance by setting the DataSourceMode property to DataReader. (Using a data reader incurs loss of some functionality.) Also, the SqlDataReader class implements the IEnumerable interface, which enables you to bind data to server controls as well. For more information, see the SqlDataReader class. For information about how ASP.NET accesses data, see Accessing Data with ASP.NET.
-
Cache data and page output whenever possible ASP.NET provides mechanisms for caching page output or data when they do not need to be computed dynamically for every page request. In addition, designing pages and data requests to be cached, particularly in areas of your site where you expect heavy traffic, can optimize the performance of those pages. Using the cache appropriately can improve the performance of your site more than using any other feature of the .NET Framework.
When using ASP.NET caching, note the following. First, do not cache too many items. There is a memory cost for caching each item. Items that are easily recalculated or rarely used should not be cached. Second, do not assign cached items a short expiration time. Items that expire quickly cause unnecessary turnover in the cache and can cause extra work for cleanup code and for the garbage collector. You can monitor the turnover in the cache due to items expiring by using the Cache Total Turnover Rate performance counter associated with the ASP.NET Applications performance object. A high turnover rate can indicate a problem, especially when items are removed before they expire. (This situation is sometimes known as memory pressure.)
For information about how to cache page output and data requests, see ASP.NET Caching Overview.
-
Use SQL cache dependency appropriately ASP.NET supports both table-based polling and query notification, depending on the version of SQL Server you are using. Table-based polling is supported by all versions of SQL Server. In table-based polling, if anything in a table changes, all listeners are invalidated. This can cause unnecessary churn in the application. Table-based polling is not recommended for tables that have many frequent changes. For example, table-based polling would be recommended on a catalog table that changes infrequently. It would not be recommended for an orders table, which would have more frequent updates. Query notification is supported by SQL Server 2005. Query notification supports specific queries, which reduces the number of notifications sent when a table is changed. While it can provide better performance than table-based polling, it does not scale to thousands of queries.
For more information on SQL cache dependency, see Walkthrough: Using ASP.NET Output Caching with SQL Server or Caching in ASP.NET with the SqlCacheDependency Class.
-
Use data source paging and sorting rather the UI (user interface) paging and sorting The UI paging feature of data controls such as DetailsView and GridView can be used with any data source object that supports the ICollection interface. For each paging operation, the data control queries the data source for the entire data collection and selects the row or rows to display, discarding the remaining data. If a data source implements DataSourceView and if the CanPage property returns true, the data control will use data source paging instead of UI paging. In that case, the data control will query for only the row needed for each paging operation. Thus, data source paging is more efficient than UI paging. Only the ObjectDataSource data source control supports data source paging. To enable data source paging on other data source controls, you must inherit from the data source control and modify its behavior.
-
Balance the security benefit of event validation with its performance cost Controls that derive from the System.Web.UI.WebControls and System.Web.UI.HtmlControls classes can validate that an event originated from the user interface that was rendered by the control. This helps prevent the control from responding to spoofed event notification. For example, the DetailsView control can prevent processing of a Delete call (which is not inherently supported in the control) and being manipulated into deleting data. This validation has some performance cost. You can control this behavior using the EnableEventValidation configuration element and the RegisterForEventValidation method. The cost of validation depends on the number of controls on the page, and is in the range of a few percent.
Security Note: |
|---|
| It is strongly recommended that you do not disable event validation. Before disabling event validation, you should be sure that no postback could be constructed that would have an unintended effect on your application. |
-
Avoid using view state encryption unless necessary View state encryption prevents the users from being able to read the values in the hidden view state field. A typical scenario is a GridView control that carries an identifier field in the DataKeyNames property. The identifier field is needed to coordinate updates to records. Because you do not want the identifier visible to users, you can encrypt view state. However, encryption has a constant performance cost for initialization and an additional cost that depends on the size of view state being encrypted. The encryption is set up for each page load, so the same performance effect occurs on every page load.
-
Use SqlDataSource caching, sorting, and filtering If the DataSourceMode property of the SqlDataSource control is set to DataSet, the SqlDataSource is able to cache the result set from a query. If data is cached in this way, the filtering and sorting operations of the control (configured with the FilterExpression and SortParameterName properties) use the cached data. In many cases your application will run faster if you cache the entire dataset, and use the FilterExpression and SortParameterName properties to sort and filter, rather than using SQL queries with "WHERE" and "SORT BY" clauses that access the database for each select action.