Optimizing ADO.NET Code

for RuBoard

Several ways exist to increase the performance of ADO.NET code in your Web or Windows forms applications. In this section, you'll see some direct improvements you can make to your ADO.NET code to improve the speed of your data access.

`DataReader` Versus `DataSet`

As you know, you have two different ways of retrieving data from your data source using ADO.NET. You can use a DataAdapter object to fill a DataSet with data, and then work with the data in the DataSet or display it on a form using a DataGrid or other mechanism for display. In a number of cases, you need a DataSet : You might need to perform calculations or otherwise work on several items within the DataSet at once. However, if you're just displaying data, the DataReader object is a more appropriate choice.

The DataReader opens a read-only, forward-only view of your data, with no more than one row of data in memory at one time. By using a DataReader to bind to your controls, you will incur the lowest amount of system overhead. By using a DataReader object instead of the DataSet , you can definitely improve the performance of your site. For small sets of data, this difference is barely noticeable. However, as the amount of data increases , the performance gains realized by the DataReader become more evident. In a quick test using 500 rows of data, the DataSet took twice as long as the DataReader to retrieve the result set and display it on the Web form. In the same test using 1,000 rows of data, the DataSet took several times longer than the DataReader and consumed a much greater amount of memory. However, keep in mind that certain actions are unavailable when using a DataReader , such as automatic paging using the DataGrid .

Managing Database Connections

Automatic connection pooling is provided by the data providers. A different connection pool is created for each connection string used in an application. In addition to this automatic connection pooling, you can speed up your code by keeping your connection to the database open during several queries that happen in rapid succession rather than opening and closing the connection between each call to the database.

Choose Your Data Provider Wisely

Your choice of data provider can have very significant effects on the performance of your application. Always choose a managed provider to connect to your data source, if one is available. One example of a managed provider is the System.Data.SqlClient namespace, which is a managed provider for Microsoft SQL Server versions 7.0 and up.

If no managed provider is available, your next best choice for performance is probably to use the System.Data.OleDb managed provider to access your data source through OLE DB. Though this adds one level of indirection in accessing your data when compared to using a direct managed provider such as the one for SQL, using OLE DB still provides relatively fast data access.

Lastly, if no OLE DB provider is available for your data source, you can use the ODBC managed provider available separately from the Microsoft .NET Framework SDK. You can download it for free from the download section at http://www.microsoft.com. ODBC is the slowest option of those listed here, but it also supports a much wider range of data sources.

Use " `SELECT *` " Sparingly

To simplify your database queries, you might be tempted to use " SELECT * " to return all available database columns in a query. Though this command makes your code easier to read, you could return a large amount of data that you don't need. Specify only the column names that you plan to use.

Automatically Generated Commands

For applications that require high performance, do not use automatically generated commands. The DataAdapter needs to make additional calls to the server in order to generate the commands. This additional overhead can needlessly slow down your application.