Use OLEDB Instead of ODBC

Another area where you can see immediate improvement is the technique you use to connect to your database. Consider moving from ODBC to an OLEDB connection.

If you are using an ODBC connection, you are probably using a System DSN or a File DSN to connect to your database. You can even use a DSNLess connection. You have a connection string that looks like one of the following:

DSN=DataSourceName; UID=userid; PWD=password;

Or

FILEDSN=FullpathtoFileDSN

Instead of using ODBC, use OLEDB. OLEDB is Microsoft's COM-based interface to databases and other data sources. A full explanation of OLEDB is beyond scope for this little article, but you can find more details at Microsoft's Universal Data Access site . OLEDB is also the underlying technology on which ADO rests on. Your ADO calls from an ASP page are first sent to OLEDB before they make their way to the next layer below. If you use an ODBC data source to your database, consider the layers the request and data have to travel through:

ASP - ADO - OLEDB - OLEDB Provider for ODBC - ODBC - Database

Most commonly used databases, SQL Server and Access included, now provide OLEDB providers that allow you to access the database directly from the OLEDB layer. You effectively bypass the ODBC layer by making a call using the OLEDB provider for the database. If you use OLEDB instead of ODBC, your data request and the data travel through one less layer:

ASP - ADO - OLEDB - OLEDB Provider for your database - Database

The result: OLEDB turns out to be faster than ODBC for the same database. In fact, in test results published in Wrox Press' book ADO 2.0 Programmer's Reference, OLEDB connections outperformed ODBC connections when used with server side cursors (the default in ADO) against both SQL Server and Access. Generally, you can expect a 10 to 30% speed improvement simply by changing over to OLEDB.

To use an OLEDB connection string with SQL Server, use the following as your connection string when you open the ADO Connection object:

StrConnString = "Provider=SQLOLEDB;" & _
	"Password=YourPassword;" & _
	"User ID=YourUserID;" & _
	"Initial Catalog=YourDatabaseName;" & _
	"Data Source=YourServerNameOrIPAddress;"

If you are using Microsoft Access, use the following as your connection string when you open the ADO Connection object:

StrConnString = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=FullPathToYourMDBFile;" & _
"Persist Security Info=False;"

Remember to fill in the appropriate places within the connection strings before using it in your code. Here is a trick to quickly and easily generate your connection string if you already have the latest ADO or Microsoft Data Access Components (MDAC) installed on your machine.

• On your Windows 98/NT/2000 desktop, right click and Select New, Text Document.
• Change the name of the document from the default that Windows provides to "Sample.udl" (without the double quotes). If the system warns you that changing the extension is not recommended, select yes to proceed.
• Double click on the UDL file to bring up the "Data Link Properties" wizard.
• First, click on the "Provider" tab and choose the OLEDB provider of your choice. What you can select will depend on what OLEDB providers are installed on your machine. If you plan to use this on your Web server, make sure you are performing these steps on your Web server, or make sure it has the right OLEDB providers installed. OLEDB providers are included as part of the MDAC.
• Click Next to set the connection properties. Provide a user name and password if required. If you wish to include the password within your connection string, click the 'Save password' option if available.
• Finally, click the Test Connection button to make sure that your connection string works. Click OK to close the wizard.
• Right click on the UDL file on your desktop and open it with Notepad. The UDL file is a very simple text file. The last line in the file is your entire connection string, and usually begins with the word "Provider." Copy and paste this line into your ASP page and you are all set.

Note: OLEDB is only marginally faster than ODBC if you are using a client-side cursor—a disconnected recordset. However, marginal is better than nothing!