Problem You want to speed up read-only data access to a database in your application. Solution Use a DataReader instead of a DataAdapter to access the data. Examples 19-7, 19-8 through 19-9 show the .aspx file and VB and C# code-behind files for our application that demonstrates the performance difference between a DataReader and a DataAdapter using the OleDB managed provider. Figure 19-2 shows the output of the application. Refer to Recipe 19.4 for an equivalent example using the SQL Server managed provider. Discussion The CLR provides two primary methods for reading data from a database. The first is to use a DataReader, and the second is to use a DataAdapter in conjunction with a DataTable or DataSet. Figure 19-2. Measuring data reader and data adapter performance output 
The DataReader provides forward, read-only access to the data read from the database. It provides no mechanisms for randomly accessing the data. A DataAdapter, along with a DataTable or DataSet, provides random access to data. In addition, the data can be changed in the DataTable or DataSet, and the DataAdapter can be used to update the data in the database. Of the two access methods, the DataReader is the lightest and fastest and is preferable when you need to only read the data, as reflected in the results we show for our sample application in Figure 19-2. Our example reads 10KB and 100KB records from a SQL Server database table containing 500KB rows. The table contains five columns, of which three are retrieved in the query. The data indicates that using a DataAdapter is anywhere from 3868% slower than using a DataReader. See Also Recipe 19.4 Example 19-7. Measuring data reader and data adapter performance (.aspx) <%@ Page Language="VB" MasterPageFile="~/ASPNetCookbookVB.master" AutoEventWireup="false" CodeFile="CH19DataAccessPerformanceVB.aspx.vb" Inherits="ASPNetCookbook.VBExamples.CH19DataAccessPerformanceVB" Title="Data Access Performance" %> <asp:Content runat="server" ContentPlaceHolder> <div align="center" > Data Access Performance Using OleDB Provider (VB) </div> <table width="70%" align="center" border="0" > <tr> <td align="center">Rows Read</td> <td align="center">OleDBDataReader Time (mSec)</td> <td align="center">OleDBDataAdaptor Time (mSec)</td> </tr> <tr> <td align="center">10,000</td> <td runat="server" align="center"></td> <td runat="server" align="center"></td> </tr> <tr> <td align="center">100,000</td> <td runat="server" align="center"></td> <td runat="server" align="center"></td> </tr> </table> </asp:Content>
|
Example 19-8. Measuring data reader and data adapter performance code-behind (.vb) Option Explicit On Option Strict On Imports System.Configuration Imports System.Data Imports System.Data.OleDb Namespace ASPNetCookbook.VBExamples ''' <summary> ''' This class provides the code-behind for ''' CH19DataAccessPerformanceVB.aspx ''' </summary> Partial Class CH19DataAccessPerformanceVB Inherits System.Web.UI.Page '''*********************************************************************** ''' <summary> ''' This routine provides the event handler for the page load event. It ''' is responsible for initializing the controls on the page. ''' </summary> ''' ''' <param name='sender">Set to the sender of the event</param> ''' <param name="e">Set to the event arguments</param> Private Sub Page_Load(ByVal sender As Object, _ ByVal e As System.EventArgs) Handles Me.Load Dim dbConn As OleDbConnection = Nothing Dim strConnection As String Dim elapsedTime As TimeSpan Try 'get the connection string from web.config and open connection 'to the database strConnection = ConfigurationManager. _ ConnectionStrings("dbConnectionString").ConnectionString dbConn = New OleDbConnection(strConnection) dbConn.Open() 'get times for 10,000 records elapsedTime = getDataAdapterTime(dbConn, 10000) cellDA10K.InnerText = elapsedTime.TotalMilliseconds.ToString("0.0000") elapsedTime = getDataReaderTime(dbConn, 10000) cellDR10K.InnerText = elapsedTime.TotalMilliseconds.ToString("0.0000") 'get times for 100,000 records elapsedTime = getDataAdapterTime(dbConn, 100000) cellDA100K.InnerText = elapsedTime.TotalMilliseconds.ToString("0.0000";) elapsedTime = getDataReaderTime(dbConn, 100000) cellDR100K.InnerText = elapsedTime.TotalMilliseconds.ToString("0.0000") Finally 'clean up If (Not IsNothing(dbConn)) Then dbConn.Close() End If End Try End Sub 'Page_Load '''*********************************************************************** ''' <summary> ''' This routine retrieves the passed number of records from the database ''' using an OleDBDataReader and returns the elapsed time ''' </summary> ''' ''' <param name="dbConn"> ''' Set to an open connection to the database ''' </param> ''' <param name="numberOfRecords"> ''' Set to the number of records to read ''' </param> ''' ''' <returns> ''' Timespan set to the elapsed time requried to read the data ''' </returns> Private Function getDataReaderTime(ByVal dbConn As OleDbConnection, _ ByVal numberOfRecords As Integer) _ As TimeSpan Dim dCmd As OleDbCommand = Nothing Dim dr As OleDbDataReader = Nothing Dim strSQL As String Dim startTime As DateTime Dim elapsedTime As TimeSpan Dim bookTitle As String Dim isbn As String Dim price As Decimal Try startTime = DateTime.Now() 'build the query string and get the data from the database strSQL = "SELECT Top " & numberOfRecords.ToString() & " " & _ "BookTitle, ISBN, Price " & _ "FROM PerformanceTesting " & _ "ORDER BY PerformanceTestingID" 'read the data from the database dCmd = New OleDbCommand(strSQL, dbConn) dr = dCmd.ExecuteReader() Do While (dr.Read()) bookTitle = CStr(dr.Item("BookTitle")) isbn = CStr(dr.Item("ISBN")) price = CDec(dr.Item("Price")) Loop 'return the elapsed time elapsedTime = DateTime.Now.Subtract(startTime) getDataReaderTime = elapsedTime Finally If (Not IsNothing(dr)) Then dr.Close() End If End Try End Function 'getDataReaderTime '''*********************************************************************** ''' <summary> ''' This routine retrieves the passed number of records from the database ''' using an OleDbDataAdapter and returns the elapsed time ''' </summary> ''' ''' <param name="dbConn"> ''' Set to an open connection to the database ''' </param> ''' <param name="numberOfRecords"> ''' Set to the number of records to read ''' </param> ''' ''' <returns> ''' Timespan set to the elapsed time requried to read the data ''' </returns> Private Function getDataAdapterTime(ByVal dbConn As OleDbConnection, _ ByVal numberOfRecords As Integer) _ As TimeSpan Dim da As OleDbDataAdapter Dim dTable As DataTable Dim strSQL As String Dim startTime As DateTime Dim elapsedTime As TimeSpan startTime = DateTime.Now() 'build the query string and get the data from the database strSQL = "SELECT Top " & numberOfRecords.ToString() & " " & _ "BookTitle, ISBN, Price " & _ "FROM PerformanceTesting " & _ "ORDER BY PerformanceTestingID" 'read the data from the database da = New OleDbDataAdapter(strSQL, dbConn) dTable = New DataTable da.Fill(dTable) 'return the elapsed time elapsedTime = DateTime.Now.Subtract(startTime) getDataAdapterTime = elapsedTime End Function 'getDataAdapterTime End Class 'CH19DataAccessPerformanceVB End Namespace
|
Example 19-9. Measuring data reader and data adapter performance code-behind (.cs) using System; using System.Configuration; using System.Data; using System.Data.OleDb; namespace ASPNetCookbook.CSExamples { /// <summary> /// This class provides the code-behind for /// CH19DataAccessPerformanceCS.aspx /// </summary> public partial class CH19DataAccessPerformanceCS : System.Web.UI.Page { ///*********************************************************************** /// <summary> /// This routine provides the event handler for the page load event. /// It is responsible for initializing the controls on the page. /// </summary> /// /// <param name="sender">Set to the sender of the event</param> /// <param name="e">Set to the event arguments</param> protected void Page_Load(object sender, EventArgs e) { OleDbConnection dbConn = null; String strConnection; TimeSpan elapsedTime; try { // get the connection string from web.config and open a connection // to the database strConnection = ConfigurationManager. ConnectionStrings["dbConnectionString"].ConnectionString; dbConn = new OleDbConnection(strConnection); dbConn.Open( ); // get times for 10,000 records elapsedTime = getDataAdapterTime(dbConn, 10000); cellDA10K.InnerText = elapsedTime.TotalMilliseconds.ToString("0.0000"); elapsedTime = getDataReaderTime(dbConn, 10000); cellDR10K.InnerText = elapsedTime.TotalMilliseconds.ToString("0.0000"); // get times for 100,000 records elapsedTime = getDataAdapterTime(dbConn, 100000); cellDA100K.InnerText = elapsedTime.TotalMilliseconds.ToString("0.0000"); elapsedTime = getDataReaderTime(dbConn, 100000); cellDR100K.InnerText = elapsedTime.TotalMilliseconds.ToString("0.0000"); } finally { if (dbConn != null) { dbConn.Close(); } } } // Page_Load ///*********************************************************************** /// <summary> /// This routine retrieves the passed number of records from the database /// using an OleDBDataReader and returns the elapsed time /// </summary> /// /// <param name="dbConn"> /// Set to an open connection to the database /// </param> /// <param name="numberOfRecords"> /// Set to the number of records to read /// </param> /// /// <returns> /// Timespan set to the elapsed time requried to read the data /// </returns> private TimeSpan getDataReaderTime(OleDbConnection dbConn, int numberOfRecords) { OleDbCommand dCmd = null; OleDbDataReader dr = null; string strSQL = null; DateTime startTime; TimeSpan elapsedTime; String bookTitle; String isbn; Decimal price; try { startTime = DateTime.Now; // build the query string used to get the data from the database strSQL = "SELECT Top " + numberOfRecords.ToString() + " " + "BookTitle, ISBN, Price " + "FROM PerformanceTesting " + "ORDER BY PerformanceTestingID"; // read the data from the database dCmd = new OleDbCommand(strSQL, dbConn); dr = dCmd.ExecuteReader(); while (dr.Read()) { bookTitle = (String)(dr["BookTitle"]); isbn = (String)(dr["ISBN"]); price = Convert.ToDecimal(dr["Price"]); } //return the elapsed time elapsedTime = DateTime.Now.Subtract(startTime); return (elapsedTime); } finally { // clean up if (dr != null) { dr.Close(); } } } // getDataReaderTime ///*********************************************************************** /// <summary> /// This routine retrieves the passed number of records from the database /// using an OleDbDataAdapter and returns the elapsed time /// </summary> /// /// <param name="dbConn"> /// Set to an open connection to the database /// </param> /// <param name="numberOfRecords"> /// Set to the number of records to read /// </param> /// /// <returns> /// Timespan set to the elapsed time requried to read the data /// </returns> private TimeSpan getDataAdapterTime(OleDbConnection dbConn, int numberOfRecords) { OleDbDataAdapter da = null; DataTable dTable = null; string strSQL = null; DateTime startTime; TimeSpan elapsedTime; startTime = DateTime.Now; // build the query string used to get the data from the database strSQL = "SELECT Top " + numberOfRecords.ToString() + " " + "BookTitle, ISBN, Price " + "FROM PerformanceTesting " + "ORDER BY PerformanceTestingID"; // read the data from the database da = new OleDbDataAdapter(strSQL, dbConn); dTable = new DataTable(); da.Fill(dTable); // return the elapsed time elapsedTime = DateTime.Now.Subtract(startTime); return (elapsedTime); } // getDataAdapterTime } // CH19DataAccessPerformanceCS }
|
|
