16.4. Speeding Up Data Access to a SQL Server Database Using the SQL Provider
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.
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
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 ...
Become an O’Reilly member and get unlimited access to this title plus top books and audiobooks from O’Reilly and nearly 200 top publishers, thousands of courses curated by job role, 150+ live events each month,
and much more.
Read now
Unlock full access