Problem You want to speed up data access in an application that will always be used with SQL Server. 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 and then use the SqlCommand, SqlDataReader, and SqlDataAdapter objects as required by your application. To test the SQL provider, we have implemented our example from Recipe 19.3 and replaced the geTDataReaderTime and geTDataAdapterTime methods in the code-behind with the code shown in Examples 19-10 (VB) and 19-11 (C#). The output of the test is shown in Figure 19-3. Figure 19-3. Performance using SQL managed provider output 
Discussion The 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 databaseincluding SQL Server, Access, Oracle, and many othersusing an OleDB (or ODBC) layer. OleDB communicates to a data source through 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 access SQL Server directly, 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 a later release, however. Comparing the results in Examples 19-10 (VB) and 19-11 (C#) with the results shown in Recipe 19.3 indicates that the SQL Server provider is faster than the OleDB provider when accessing SQL Server; access using a DataReader is approximately twice as fast, while access using a DataAdapter is 75100% 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 8088% slower than the DataReader with the SQL Server provider. See Also Recipe 19.3; search for "The .NET Framework Data Provider for SQL Server" in the MSDN Library Example 19-10. Methods using SQL provider (.vb) '''*********************************************************************** ''' <summary> ''' This routine retrieves the passed number of records from the database ''' using a SqlDataReader 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 SqlConnection, _ ByVal numberOfRecords As Integer) _ As TimeSpan Dim dCmd As SqlCommand = Nothing Dim dr As SqlDataReader = 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 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 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 a SqlDataAdapter 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 SqlConnection, _ ByVal numberOfRecords As Integer) _ As TimeSpan Dim da As SqlDataAdapter 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 SqlDataAdapter(strSQL, dbConn) dTable = New DataTable da.Fill(dTable) 'return the elapsed time elapsedTime = DateTime.Now.Subtract(startTime) getDataAdapterTime = elapsedTime End Function 'getDataAdapterTime
|
Example 19-11. Methods using SQL provider (.cs) ///*********************************************************************** /// <summary> /// This routine retrieves the passed number of records from the database /// using a SqlDataReader 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(SqlConnection dbConn, int numberOfRecords) { SqlCommand dCmd = null; SqlDataReader 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 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 (dr != null) { dr.Close(); } } } // getDataReaderTime ///*********************************************************************** /// <summary> /// This routine retrieves the passed number of records from the database /// using a SqlDataAdapter 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(SqlConnection dbConn, int numberOfRecords) { SqlDataAdapter 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 SqlDataAdapter(strSQL, dbConn); dTable = new DataTable(); da.Fill(dTable); // return the elapsed time elapsedTime = DateTime.Now.Subtract(startTime); return (elapsedTime); } // getDataAdapterTime
|
|
