Recipe 16.3 Speeding Up Read-Only Data Access

     

16.3.1 Problem

You want to speed up read-only data access to a database in your application.

16.3.2 Solution

Use a DataReader instead of a DataAdapter to access the data.

Example 16-7 through Example 16-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 16-2 shows the output of the application. Refer to Recipe 16.4 for an equivalent example using the SQL Server managed provider.

Figure 16-2. Measuring data reader and data adapter performance output
figs/ancb_1602.gif

16.3.3 Discussion

The common language runtime (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 .

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 only need to read the data, as reflected in the results we show for our sample application in Figure 16-2. Our example reads 10K and 100K records from a SQL Server database table containing 500K rows. The table contains five columns , of which three are retrieved in the query. The data indicates that using a DataAdapter is anywhere from 12% to 21% slower than using a DataReader .

16.3.4 See Also

Recipe 16.4

Example 16-7. Measuring data reader and data adapter performance (.aspx)
 <%@ Page Language="vb" AutoEventWireup="false"           Codebehind="CH16DataAccessPerformanceVB.aspx.vb"           Inherits="ASPNetCookbook.VBExamples.CH16DataAccessPerformanceVB" %> <!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN"> <html>   <head>     <title>Data Access Performance</title>     <link rel="stylesheet" href="css/ASPNetCookbook.css">   </head>   <body leftmargin="0" marginheight="0" marginwidth="0" topmargin="0">     <form id="frmDataAccessPerformance" method="post" runat="server">       <table width="100%" cellpadding="0" cellspacing="0" border="0">         <tr>           <td align="center">             <img src="images/ASPNETCookbookHeading_blue.gif">           </td>         </tr>         <tr>           <td class="dividerLine">             <img src="images/spacer.gif" height="6" border="0"></td>         </tr>       </table>       <table width="90%" align="center" border="0">         <tr>           <td align="center">&nbsp;</td>         </tr>         <tr>           <td align="center" class="PageHeading">             Data Access Performance Using OleDB Provider (VB)           </td>         </tr>         <tr>           <td><img src="images/spacer.gif" height="10" border="0"></td>         </tr>         <tr>           <td align="center">             <table width="100%" 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 id="cellDR10K" runat="server" align="center"></td>   <td id="cellDA10K" runat="server" align="center"></td>  </tr>               <tr>                 <td align="center">100,000</td>  <td id="cellDR100K" runat="server" align="center"></td>   <td id="cellDA100K" runat="server" align="center"></td>  </tr>             </table>           </td>         </tr>       </table>     </form>   </body> </html> 

Example 16-8. Measuring data reader and data adapter performance code-behind (.vb)
 Option Explicit On  Option Strict On '----------------------------------------------------------------------------- ' '   Module Name: CH16DataAccessPerformanceVB.aspx.vb ' '   Description: This module provides the code behind for the  '                CH16DataAccessPerformanceVB.aspx page ' '***************************************************************************** Imports Microsoft.VisualBasic Imports System Imports System.Configuration Imports System.Data Imports System.Data.OleDb Namespace ASPNetCookbook.VBExamples   Public Class CH16DataAccessPerformanceVB     Inherits System.Web.UI.Page     'controls on the form     Protected cellDR10K As System.Web.UI.HtmlControls.HtmlTableCell     Protected cellDR100K As System.Web.UI.HtmlControls.HtmlTableCell     Protected cellDA10K As System.Web.UI.HtmlControls.HtmlTableCell     Protected cellDA100K As System.Web.UI.HtmlControls.HtmlTableCell     '*************************************************************************     '     '   ROUTINE: Page_Load     '     '   DESCRIPTION: This routine provides the event handler for the page load     '                event.  It is responsible for initializing the controls      '                on the page.     '-------------------------------------------------------------------------     Private Sub Page_Load(ByVal sender As System.Object, _                           ByVal e As System.EventArgs) Handles MyBase.Load       Dim strConnection As String       Dim elapsedTime As TimeSpan       'get the connection string from web.config       strConnection = _           ConfigurationSettings.AppSettings("dbConnectionString")  'get times for 10,000 records   elapsedTime = getDataAdapterTime(strConnection, 10000)   cellDA10K.InnerText = elapsedTime.TotalMilliseconds.ToString("0.0000")   elapsedTime = getDataReaderTime(strConnection, 10000)   cellDR10K.InnerText = elapsedTime.TotalMilliseconds.ToString("0.0000")   'get times for 100,000 records   elapsedTime = getDataAdapterTime(strConnection, 100000)   cellDA100K.InnerText = elapsedTime.TotalMilliseconds.ToString("0.0000")   elapsedTime = getDataReaderTime(strConnection, 100000)   cellDR100K.InnerText = elapsedTime.TotalMilliseconds.ToString("0.0000")  End Sub  'Page_Load     '*************************************************************************     '     '   ROUTINE: getDataReaderTime     '     '   DESCRIPTION: This routine retrieves the passed number of records from     '                the database using an OleDBDataReader and returns the     '                elapsed time     '-------------------------------------------------------------------------  Private Function getDataReaderTime(ByVal strConnection As String, _   ByVal numberOfRecords As Integer) _   As TimeSpan   Dim dbConn As OleDbConnection   Dim dCmd As OleDbCommand   Dim dr As OleDbDataReader   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   'open connection to database   dbConn = New OleDbConnection(strConnection)   dbConn.Open( )   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   'clean up   If (Not IsNothing(dbConn)) Then   dbConn.Close( )   End If   End Try   End Function  'getDataReaderTime  '*************************************************************************     '     '   ROUTINE: getDataAdapterTime     '     '   DESCRIPTION: This routine retrieves the passed number of records from     '                the database using an OleDbDataAdapter and returns the     '                elapsed time     '-------------------------------------------------------------------------  Private Function getDataAdapterTime(ByVal strConnection As String, _   ByVal numberOfRecords As Integer) _   As TimeSpan   Dim dbConn As OleDbConnection   Dim da As OleDbDataAdapter   Dim dTable As DataTable   Dim strSQL As String   Dim startTime As DateTime   Dim elapsedTime As TimeSpan   Try   'open connection to database   dbConn = New OleDbConnection(strConnection)   dbConn.Open( )   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   Finally   'clean up   If (Not IsNothing(dbConn)) Then   dbConn.Close( )   End If   End Try   End Function  'getDataAdapterTime   End Class  'CH16DataAccessPerformanceVB  End Namespace 

Example 16-9. Measuring data reader and data adapter performance code-behind (.cs)
 //---------------------------------------------------------------------------- // //   Module Name: CH16DataAccessPerformanceCS.aspx.cs // //   Description: This module provides the code behind for the  //                CH16DataAccessPerformanceCS.aspx page // //**************************************************************************** using System; using System.Configuration; using System.Data; using System.Data.OleDb; namespace ASPNetCookbook.CSExamples {   public class CH16DataAccessPerformanceCS : System.Web.UI.Page   {     // controls on the form     protected System.Web.UI.HtmlControls.HtmlTableCell cellDR10K;     protected System.Web.UI.HtmlControls.HtmlTableCell cellDR100K;     protected System.Web.UI.HtmlControls.HtmlTableCell cellDA10K;     protected System.Web.UI.HtmlControls.HtmlTableCell cellDA100K;          //************************************************************************     //     //   ROUTINE: Page_Load     //     //   DESCRIPTION: This routine provides the event handler for the page      //                load event.  It is responsible for initializing the      //                controls on the page.     //------------------------------------------------------------------------     private void Page_Load(object sender, System.EventArgs e)     {       String strConnection;       TimeSpan elapsedTime;       // get the connection string from web.config       strConnection =           ConfigurationSettings.AppSettings["dbConnectionString"];  // get times for 10,000 records   elapsedTime = getDataAdapterTime(strConnection, 10000);   cellDA10K.InnerText = elapsedTime.TotalMilliseconds.ToString("0.0000");   elapsedTime = getDataReaderTime(strConnection, 10000);   cellDR10K.InnerText = elapsedTime.TotalMilliseconds.ToString("0.0000");   // get times for 100,000 records   elapsedTime = getDataAdapterTime(strConnection, 100000);   cellDA100K.InnerText = elapsedTime.TotalMilliseconds.ToString("0.0000");   elapsedTime = getDataReaderTime(strConnection, 100000);   cellDR100K.InnerText = elapsedTime.TotalMilliseconds.ToString("0.0000");  }  // Page_Load     //************************************************************************     //     //   ROUTINE: getDataReaderTime     //     //   DESCRIPTION: This routine retrieves the passed number of records from     //                the database using an OleDBDataReader and returns the     //                elapsed time.     //------------------------------------------------------------------------  private TimeSpan getDataReaderTime(String strConnection,   int numberOfRecords)   {   OleDbConnection dbConn = null;   OleDbCommand dCmd = null;   OleDbDataReader dr = null;   string strSQL = null;   DateTime startTime;   TimeSpan elapsedTime;   String bookTitle;   String isbn;   Decimal price;   try   {   // open connection to database   dbConn = new OleDbConnection(strConnection);   dbConn.Open( );   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 (dbConn != null)   {   dbConn.Close( );   }   }   }  // getDataReaderTime  //************************************************************************     //     //   ROUTINE: getDataAdapterTime     //     //   DESCRIPTION: This routine retrieves the passed number of records from     //                the database using an OleDbDataAdapter and returns the     //                elapsed time.     //------------------------------------------------------------------------  private TimeSpan getDataAdapterTime(String strConnection,   int numberOfRecords)   {   OleDbConnection dbConn = null;   OleDbDataAdapter da = null;   DataTable dTable = null;   string strSQL = null;   DateTime startTime;   TimeSpan elapsedTime;   try   {   // open connection to database   dbConn = new OleDbConnection(strConnection);   dbConn.Open( );   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);   }   finally   {   // clean up   if (dbConn != null)   {   dbConn.Close( );   }   }   }  // getDataAdapterTime  }  // CH16DataAccessPerformanceCS } 



ASP. NET Cookbook
ASP.Net 2.0 Cookbook (Cookbooks (OReilly))
ISBN: 0596100647
EAN: 2147483647
Year: 2006
Pages: 179

Similar book on Amazon

flylib.com © 2008-2017.
If you may any questions please contact us: flylib@qtcs.net