Recipe 16.4 Speeding Up Data Access to a SQL Server Database Using the SQL Provider

     

16.4.1 Problem

You want to speed up data access in an application that will always be used with SQL Server.

16.4.2 Solution

Use the SQL Server managed provider instead of the OleDB managed provider for accessing the data in the database.

In the code-behind class for the page, open a connection to a SQL Server database using the SQLConnection class.

To test the SQL provider, we have implemented our example from Recipe 16.3 and replaced the getDataReaderTime and getDataAdapterTime methods in the code-behind with the code shown in Example 16-10 (VB) and Example 16-11 (C#). The output of the test is shown in Figure 16-3.

Figure 16-3. Performance using SQL managed provider output
figs/ancb_1603.gif

16.4.3 Discussion

The common language runtime (CLR) provides four managed providers for accessing data in a database: SQL, OleDB , ODBC, and Oracle. The OleDB and ODBC providers can be used to access virtually any database ”including SQL Server, Access, Oracle, and many others ”using an OleDB (or ODBC) layer. OleDB communicates to a data source through both the OleDB service component, which provides connection pooling and transaction services, and the OleDB provider for the data source. In contrast, the SQL Server provider uses a proprietary protocol to directly access SQL Server, eliminating the additional layer of the OleDB service component and thereby improving performance. It can only be used to access SQL Server 7.0 or later release, however.

Comparing the results in Example 16-10 (VB) and Example 16-11 (C#) with the results shown in Recipe 16.3 indicates that the SQL Server provider is substantially faster than the OleDB provider when accessing SQL Server; access using a DataReader is nearly twice as fast, while access using a DataAdapter is 65-80% faster.

The data also indicates that when using the SQL Server provider, the difference between using the DataReader and the DataAdapter is more significant. The DataAdapter is 25-35% slower than the DataReader with the SQL Server provider.

16.4.4 See Also

Recipe 16.3; search for "The .NET Framework Data Provider for SQL Server" in the MSDN Library

Example 16-10. Methods using SQL provider (.vb)
 '*************************************************************************     '     '   ROUTINE: getDataReaderTime     '     '   DESCRIPTION: This routine retrieves the passed number of records from     '                the database using an SqlDataReader and returns the     '                elapsed time     '-------------------------------------------------------------------------     Private Function getDataReaderTime(ByVal strConnection As String, _                                        ByVal numberOfRecords As Integer) _                      As TimeSpan  Dim dbConn As SqlConnection   Dim dCmd As SqlCommand   Dim dr As SqlDataReader  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 SqlConnection(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 SqlCommand(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 SqlDataAdapter and returns the     '                elapsed time     '-------------------------------------------------------------------------     Private Function getDataAdapterTime(ByVal strConnection As String, _                                         ByVal numberOfRecords As Integer) _                      As TimeSpan  Dim dbConn As SqlConnection   Dim da As SqlDataAdapter   Dim dTable As DataTable  Dim strSQL As String       Dim startTime As DateTime       Dim elapsedTime As TimeSpan       Try         'open connection to database  dbConn = New SqlConnection(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 SqlDataAdapter(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 

Example 16-11. Methods using SQL provider (.cs)
 //************************************************************************     //     //   ROUTINE: getDataReaderTime     //     //   DESCRIPTION: This routine retrieves the passed number of records from     //                the database using an SqlDataReader and returns the     //                elapsed time.     //------------------------------------------------------------------------     private TimeSpan getDataReaderTime(String strConnection,       int numberOfRecords)     {  SqlConnection dbConn = null;   SqlCommand dCmd = null;   SqlDataReader dr = null;  string strSQL = null;       DateTime startTime;       TimeSpan elapsedTime;       String bookTitle;       String isbn;       Decimal price;       try       {         // open connection to database  dbConn = new SqlConnection(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 SqlCommand(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 SqlDataAdapter and returns the     //                elapsed time.     //------------------------------------------------------------------------     private TimeSpan getDataAdapterTime(String strConnection,       int numberOfRecords)     {  SqlConnection dbConn = null;   SqlDataAdapter da = null;  DataTable dTable = null;       string strSQL = null;       DateTime startTime;       TimeSpan elapsedTime;       try       {         // open connection to database  dbConn = new SqlConnection(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 SqlDataAdapter(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 



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

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