Based on the results of these tests, I have several recommendations for optimal data access performance. The first recommendation is that caching be used wherever possible. These tests demonstrated that even when no network latency is involved between the application server and the database, accessing a cached DataTable was 17% faster than using a DataReader to hit the database (network latency would greatly increase this advantage, as the latency time would be added for every row of data the DataReader returned). In cases where caching is not appropriate, however, the DataReader is clearly faster than the DataTable, beating it by about 12% in these tests. However, when using the DataReader, it should always be wrapped in a using statement to ensure that it is properly disposed of. A single unclosed DataReader on a given site could cause the site to become unresponsive, and resulted in a 93% degradation in performance in these tests versus properly destroyed DataReaders.
Listing 1 shows an example of a Data Access Layer method for returning a DataReader, using a Delegate technique, and the delegate definition.
Listing 1 – Returning a DataReader Using a Delegate To Ensure Cleanup
public delegate object BorrowReader(IDataReader reader);
public static object LendAuthorsReader(BorrowReader borrower)
{
using(SqlConnection conn = new SqlConnection(_connectionString))
{
SqlCommand cmd = new SqlCommand("SELECT * FROM Authors", conn);
conn.Open();
SqlDataReader reader = cmd.ExecuteReader();
return borrower(reader);
}
}
To call the method from a business object or ASP.NET page, the code would look something like this:
Data.LendAuthorsReader(new Data.BorrowReader(DisplayDelegateReader));
The code that actually uses the Reader is found in the DisplayDelegateReader method, which must match the delegate defined above, that is, it must return object and must take a single IDataReader parameter. Listing 2 shows the method used in these tests.
Listing 2 – Calling the Data Access code and using the DataReader
private object DisplayDelegateReader(IDataReader reader)
{
int authorCount = 0;
while(reader.Read())
{
authorCount++;
}
reader.Close();
if(authorCount > 0)
{
ResultLabel.Text = authorCount + "authors found.";
}
else
{
ResultLabel.Text = "Failed to find authors.";
}
return null;
}