Optimizing .NET Data Access

Introduction

This chapter examines asynchronous processing, caching, paging, batching , and class-specific methods and techniques to improve application performance. Before optimizing any application, profile it to ensure that you have a good understanding of where the real bottlenecks are.

A query can run asynchronously on background threads to improve application responsiveness and perceived performance by not blocking processing. This can also be used to give the user an opportunity to cancel a request that is taking too long. Recipe 9.1 shows how to use a background thread to run a query. Recipe 9.2 shows how to let the user cancel a query running on a background thread.

Caching data allows data to be retrieved once and saved in order to service subsequent requests for the same data. The load on the database server is reduced, potentially improving application performance. On the downside, cached data becomes less current (and less accurate) over time. The .NET Framework provides classes to allow both client- and server-side caching of data. On the client-side, caching requires few server-side resources, but increases network bandwidth required to move data back and forth with each round trip. Caching on the server-side consumes more network resources; however, it is less expensive in terms of bandwidth required. In either case, applications should be designed to retrieve the minimum data necessary to optimize performance and scalability. Recipe 9.3 shows how to use caching in an ASP.NET application.

Even if data is not cached, it can still get out of date. A timer can track the time that data was retrieved from the database in order to periodically refresh the data and thus present the data with a current view. Recipe 9.14 shows how to use the extended properties of a DataSet to automatically refresh the data a user sees.

This chapter also covers the following:

Paging

Paging is common in applications where a subset of a result seta pageis displayed to the user. The way that paging is implemented affects both scalability and performance. .NET provides automatic paging in many Windows Forms and Web Forms controls; however, manual paging offers the best performance. This allows paging requirements to be met exactly, rather than automatically, which is the default. Recipe 9.4 shows a high-performance custom paging solution.

Moving large amounts of data

Storing binary large objects (BLOBs) in a database is becoming an increasingly viable option as vendors enhance database capabilities in response to demands that ubiquitous high-bandwidth has created for storing digital assets. Storing BLOBs in a database is simpler than other approaches because there is no need to synchronize database fields acting as pointers to an external repository such as the file system. BLOBs are easier to administer and are automatically backed up with the database. Built-in database functionality, such as full-text searching, can be used on BLOB fields, and it leverages tools already in the database rather than requiring external tools. Recipe 9.11 shows how to store and retrieve BLOBs in a SQL Server and Recipe 9.12 shows how to store and retrieve BLOBs in an Oracle database.

The DataSet is an in-memory database containing both relation and constraint objects to maintain the integrity of the data. These objects, can, however, slow performance when filling a DataSet with large amounts of data that has complex interdependencies and constraints. Turning off the constraints temporarily can sometimes improve performance in these situations, which Recipe 9.9 discusses.

Large amounts of data sometimes need to be loaded into a SQL Server database quickly. SQL XML Bulk Load objects allow XML data to be loaded into SQL Server tables providing high performance when large amounts of data need to be inserted. All that is required is an XML schema and an XML document or fragment containing the data. Recipe 9.5 shows how to do this.

Minimizing roundtrips and conversions

A DataAdapter makes a roundtrip to update the data source for every row that has been changed. In some situations, this can cause performance problems. You can batch these DataAdapter updates by handling DataAdapter events. Roundtrips are reduced and performance is improved. Recipe 9.13 shows how this is done.

DataReader typed accessor methods improve performance by eliminating type conversions when retrieving data from a DataReader . You can dynamically retrieve column ordinals at runtime and use them instead of column names to further improve performance when accessing data with a DataReader . Recipe 9.6 and Recipe 9.7 show how to use these techniques with a DataReader .

Simplifying your code

There are several ways to get a single data value from a result set without incurring the overhead of using a DataSet . The ExecuteScalar( ) method returns the first value of the first row in the result set. A stored procedure output parameter can be used similarly to getting a single row of data. A DataReader can also be used. If the DataReader might return multiple rows, remember to call the Cancel( ) method before calling Close( ) so that the remaining rows are not transmitted unnecessarily back to the client who needs only one value. The ExecuteScalar( ) and stored procedure output parameter approaches offer better performance than using a DataReader . Recipe 9.10 shows how to retrieve a single value efficiently .

Debugging

Visual Studio .NET supports debugging SQL Server stored procedures both in standalone mode and from managed code when called using the .NET provider for SQL server. This can help to optimize and troubleshoot stored procedures. Recipe 9.8 shows how to debug stored procedures from Visual Studio .NET.