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 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 |