Using Data Access Best Practices | Performance Tuning and Optimizing ASP.NET Applications

Good data access code begins with making good design decisions, as discussed in the previous sections. In addition, it helps to follow a list of best practices, which you can think of as a checklist that keeps your code efficient and may also buy you extra performance. The following are some generally recognized best practices for writing data access code:

Use the appropriate data access object.
Use stored procedures, not embedded Transact-SQL.
Use complex stored procedures, not multiple retrievals.
Use SQL data types with SQL Server.
Use connection pooling.
Use centralized data access functions.
Use good programming sense.
Use exception handling appropriately.
Use helper technologies.

Now, let's discuss each of these in turn .

Use the Appropriate Data Access Object

You have seen the distinctiveness of each of the ADO.NET objects. The easiest and smartest step you can take is to always use the most appropriate data object for the given scenario. Always use streaming data access for read-only, data retrieval operations. Use the DataSet object for data update operations only if you need to perform the updates in disconnected mode. (Alternatively, write dedicated update stored procedures that resolve updates for you.) Use the DataView object when you want to work with filtered views of a larger DataSet object. The DataView object provides many of the benefits of the DataSet object, but without as much overhead.

Use Stored Procedures, Not Embedded Transact-SQL

An accepted Transact-SQL design approach is to compile your Data Manipulation Language (DML) statements into stored procedures. Stored procedures execute much faster than Transact-SQL statements because they are precompiled on the database server and are reusable. The ADO.NET Command object will allow you to execute embedded Transact-SQL statements directly by assigning the statement to the CommandText property and by setting the CommandType enumeration to Text . You may have seen code that dynamically assembles a Transact-SQL string. However, you should always avoid this approach. Not only does Transact-SQL execute more slowly than a stored procedure, but you can also introduce parsing errors into the Transact-SQL statement, which will in turn generate runtime errors. Worse yet, if your application executes any dynamic Transact-SQL statement, then you may inadvertently allow the application to execute commands that modify the database structure (such as dropping tables). Always execute stored procedures, and use input parameters, preferably with strong typing.

Tip

Keep the size of the returned resultset to a minimum by filling it with just the records that you need. This is especially important for database query results that will be marshaled over the wire to Web clients . Also, avoid using the wildcard (*) in SQL queries. Always specify the exact fields you want to extract.

Use Complex Stored Procedures, Not Multiple Retrievals

Use complex stored procedures that return multiple resultsets, rather than making multiple calls to multiple stored procedures. ADO.NET makes it easy to work with multiple resultsets, for example, by using the SqlDataReader object's NextResult() method. This is code that demonstrates how to iterate through every record in every returned resultset:

 sqlDR = objDB.RunQueryReturnDR("MyStoredProcedure") Dim arrResult(0) As String Do     While sqlDR.Read() ' Position the pointer on the first record         i += 1         ReDim Preserve arrResult(i)          arrResult(i) = sqlDR("ProductNumber")      End While Loop While (sqlDR.NextResult()) ' Move to the next resultset

If possible, write your stored procedures to batch- related resultsets. This helps to reduce network traffic and overhead on the database server.

Use SQL Data Types with SQL Server

The .NET Framework and SQL Server use different data types that do not always convert with each other. The System.Data.SqlTypes namespace provides a set of .NET Framework structures that represent SQL Server data types in the managed environment. In addition, the SqlDataReader class provides typed accessor methods that automatically map retrieved field values into the appropriate structure. Always use typed accessor methods when retrieving SQL Server data to avoid type conversion errors.

Use Connection Pooling

Always use connection pooling. The SQL Server managed provider supports connection pooling by default with little effort required on the part of the developer. The most work you have to do is to modify the connection string to override default settings for connection pooling parameters.

Use Centralized Data Access Functions

Always centralize your data access functions in a dedicated class file. This enables you to maintain your database code in one central location, which makes it easier to write and to maintain. The sample project that accompanies this chapter provides one example of a data access class (the source code file is DataAccess.vb ). It implements wrapper functions for basic data access operations, including:

Executing a stored procedure and returning a DataReader
Executing a stored procedure and returning a DataSet
Executing a stored procedure and returning an XmlReader
Executing a stored procedure with no return value

These wrapper functions encapsulate the details of setting up the Connection and Command objects, as well as any additional objects. This code becomes repetitive, and the last thing you want to do is to have the same constructs included in dozens of locations throughout your code. Not only is this hard to maintain, but it also artificially inflates the size of the application executable.

Note	Microsoft provides a .NET component called the Data Access Application Block , which contains optimized data access wrapper functions.You can read more about this and download the code at: http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnbda/html/daab-rm.asp .

Use Good Programming Sense

The .NET managed runtime environment provides advanced garbage collection designed to optimize resource usage and remove unnecessary references. However, you should always implement the fundamental housekeeping rules that keep your code readable and efficient. In particular, always clear object references when you are done with them. If you use data connections, make sure you keep the connection open for as short a time as possible. Open the connection just before it is needed, and close it as soon as it is no longer required. Never leave open connections in your code, especially if you are working with streaming data objects such as the DataReader. In summary, close connections and clear unused object references: This is good coding practice, and it makes good sense.

Use Exception Handling Appropriately

Exception handling is expensive, especially throwing errors. Always implement structured error handling in your applications, but design your code to avoid falling into exception handlers. This may seem obvious except to developers who are used to coding inline, such as when using classic ASP. Inline code often uses On Error Resume Next constructs that allow code to continue executing past errors in order to then check for an expected error result. This approach is unnecessary in the .NET managed environment. Design your code to use exception handlers as destinations of last resort. Use the multitude of error and type checking functions to detect errors before the compiler is forced to raise a runtime error.

Throw exceptions only if absolutely necessary because this is an expensive operation. The exception classes provide a large amount of information that may go unused by the calling code that receives the thrown error. In this case, it is better to raise a custom error, using Err.Raise, than to throw an exception. This operation will transmit basic error information, such as an error number, source, and message, but it will avoid expensive information such as the detailed call stack.

Finally, if you provide more than one Catch statement with differing filter criteria, remember to order them from most-specific type to least-specific type. For example:

 Try Catch SqlErr as SqlException Catch err As Exception Finally End Try

Use Helper Technologies

ASP.NET provides technologies that complement ADO.NET in providing optimized data access. In particular, ASP.NET supports a sophisticated set of caching options ranging from page-level output caching to data caching using the Cache API. Caching is an important consideration in the design of ASP.NET applications. It is considerably faster to read data from a cache than it is to access it fresh from a data source. Caching reduces the number of queries executed against the database and delivers data more responsively. Caching does come with a price in terms of increased memory usage, particularly if you are caching large DataSets. However, caching is efficient in ASP.NET and will almost always prove to be worth the small price you pay in increased resource usage. The one caveat with caching is that it can only provide a stale view of data, so you need to factor in the appropriate refresh rate into a caching implementation.

Note	Please refer to Chapter 5,"Caching ASP.NET Applications," for a complete discussion on caching.

ASP.NET and the .NET Framework provide additional features that can serve as helper technologies for data access. Above all, keep in mind that with ASP.NET you have access to the full .NET Class Framework, and this alone is a powerful advantage, both for data access code and beyond.