ADO.NET

In the early 1990s, I was working on a project in which we were creating an accounts receivable system. Because the users of the system would be dealing with people whose accounts were past due, the major concern of the client was that the windows open as quickly as possible. The ultimate goal was to have the main window in the application open in three seconds or less. We tried our best to optimize the application by removing unnecessary code from the window initialization code and optimized the database with appropriate indexes. We were still only able to get the window to open in about eight seconds. Our last resort was to change the way we retrieved the data from the database.

Rather than run a series of select statements to populate the customer information, account information, and customer contact history, we defined separate events for each one. In the individual event handlers, we made a connection to the database and retrieved the appropriate data. The end result was that the main window opened in about one second. The data were then retrieved from the database and the individual controls were populated. This was done in an unpredictable order, but with a little more tweaking of the database and some additional events, we were able to get the timing down so that the form would seem to populate from the top down.

When we showed the customers, they were very happy because the customer names and other relevant information were populated within the requested three seconds. The rest of the information took longer to populate, but because the customer service representative could begin talking with the person, the end user was happy. In the final analysis, the entire screen took around 12 seconds to populate.

Our final solution that loaded the data asynchronously turned out to be about 50% slower than the most optimized solution that we could create using a synchronous connection. After a little research, the reason for the increase in total time became clear. Whereas the synchronous request opened one connection to the database and serialized the SQL statements over it, the asynchronous method opened multiple connections to the database. Each connection retrieved a set of data independently and then the connection was closed. The overhead of the connections was a larger portion of the overall solution. In addition, because each query had to join to the customer table, there was a lot more lock contention on the customer table. In our case, we were lucky that all of the queries were read queries. If we had been forced to use an update query, we might have had even bigger performance problems.

Fast forward 10 years to the present time and you will find that the situation has not changed very much. There are many different ways to create asynchronous code. However, from a single connection to an SQL server, you are not allowed to work on a connection while you have an open result set (see Figure 1).

When the Connection Busy button is clicked, the code in Listing 1 is run. The first line of code establishes a connection string to the database using integrated security. In the second and third lines, an ADO.NET connection is created and opened. This establishes the single database connection. In the next block of code, an SQL statement is created to retrieve the Order ID, Company Name, and Order Date for a particular customer order. Then an SqlDataReader is created and the first row is read in. The values are then written to a textbox and the code is basically repeated to create another SqlDataReader. When the Read() command is called, an orderDetailDr error occurs (see Figure 2).

The error occurs because ADO.NET and SQL Server allow only one set of results to be outstanding at any one time. To get around this problem, you can open two connections to the database. This will accomplish the goal of enabling you to retrieve the orders and order details records and to display them interleaved. The problem with this solution is that it opens two database connections that tend to be very expensive relative to the data that they retrieve. Another option would be to retrieve the data into two different DataTables inside a DataSet and then programmatically iterate over the rows printing out the data.

With ADO.NET 2.0, you will have another option. You can use the Multiple Active Result Sets (MARS) functionality enabled by ADO.NET 2.0 and SQL Server 2005 to have multiple outstanding requests. MARS does not allow for parallel execution of your code. Instead, it allows SELECT and BULK INSERT statements to be interleaved so that you can have any number of outstanding requests for data open and still be able to retrieve a new set of data. If commands other than a SELECT or BULK INSERT are issued on a MARS connection, the batches in the connections will be interleaved at the statement boundary. This means that if you issue two batches containing DML and DDL statements on the same connection, a statement from one of the batches will be executed. While that statement is executing, all other statements will be blocked. When that statement completes, another statement will be chosen from one of the two batches and will run until it is completed. In contrast, if two batches contain SELECT or BULK INSERT statements, the statements can be completely interleaved. This enables a few rows to be retrieved from the first statement in the first batch, after which rows from the first statement in the second batch can be retrieved. The rows can be retrieved from the two batches in any order without either statement having to complete first. By interleaving the retrieval of the data, SQL Server can manage its resources more efficiently and your application should perform better. MARS will work with any of the supported APIs, so you can use it with SqlClient, OleDb, or Odbc database connections.

By taking the code in Listing 1 and changing the connection string properties to use MARS, I was able to populate the list box with one row of data from the Orders table and then multiple rows of data from the Order Details table (see Figure 3).

Another improvement expected to be included in ADO.NET 2.0 is the ability to execute commands asynchronously. With ADO or ADO.NET v1.0, each command on a connection is executed in turn and the other commands wait for the previous ones to finish.

With MARS, you can interleave multiple commands on a single connection, but MARS still does not allow you to execute multiple commands concurrently or to access different data stores. If you want to start multiple commands and have them execute without having to wait for them to complete, you can use asynchronous commands to begin executing the commands and then use one of three methods to determine when the commands have completed. The three methods for determining when a command has completed are:

• Polling: You can start all the tasks and then repeatedly check each one to see if it has completed.
• Callback: You can specify a method to be executed when the command has completed.
• Wait: You can decide to wait for one command to finish or for all the commands to finish. You use a WaitHandle to determine when a method has completed.

The current beta copy of SQL Server 2005 to which I have access does not implement asynchronous connections, however, it looks like there will be several new methods added to ADO.NET that will allow you to execute asynchronous commands. Each method has several overloads that return an instance of an Sql AsyncResult class that implements the IAsyncResult interface. The methods are summarized in Table 1.

The IAsyncResult interface is currently available in the .NET Framework and is used in many places today, including asynchronous file I/O and the proxy classes that are generated for XML Web services. As a result, if you are already familiar with the asynchronous processing model in the .NET Framework, you will not need to learn anything new to begin making asynchronous database calls. The properties of the IAsyncResult interface are illustrated in Table 2.

The method that you use to determine whether or not a command has completed depends on the way in which you are planning to use the command and any results that it might return. Polling is useful if you are doing a small number of related operations inside a tight loop, but it does not work very well if you have a large number of disparate operations to perform. A WaitHandle is useful if you want to begin processing results as soon as one result set has returned, or if you want to set a timeout value for the amount of time you are willing to wait for an operation to complete. A callback is useful if you want to process the results from the command as you would an event handler.

With the addition of MARS and asynchronous queries, Microsoft will finally give database developers a way to access the database and take full advantage of the power of today's database servers. ADO.NET developers will have a supported way to help their applications scale and perform their best using familiar programming methods.

Add Your Feedback

In order to post a comment you need to be registered and logged in.

Register | Log in

Please wait while we process your request...