The fastest database connection

By Michiel van Otegem
1 December 2000

The faster your database connection, the more data you can retrieve from it. Therefore it is important to use the fastest possible database connection in your application, especially when you have a lot of users on your site. There are multiple ways to make a database connection with ASP, or actually ADO (ActiveX Data Objects). Only three of them are really worth while to use: ODBC via DSN, ODBC without DSN and OLEDB (without DSN).

The slowest of the three is ODBC via DSN. ODBC stands for Open DataBase Connectivety and is a standard for database connections, almost every database supports ODBC. In Windows you can create a so called DSNs with the ODBC manager (Control Panel). The DSN contains the required information to open a database of a certain type. For the user it doesn't matter which database, because everything works via the ODBC standard. The database is easy to open in ADO with
objConn.Open "DSN=Databasename;Uid=Admin;Pwd=;"

or when no username and password is needed
objCn.Open "Databasename"

The connection above is slow because of the overhead from ODBC and because ADO has to retrieve the data of the DSN from the registry everytime. You can also create a so called DSN-less connection. With ODBC this is possible with this code:
objCn.Open "Driver={Microsoft Access Driver (*.mdb)};Dbq=db.mdb;Uid=Admin;Pwd=;"

With this the ODBC database driver for the appropriate database will be specified first (in this case Access), after that the path to the database and next again username and password.
Advantage of this method is that you don't have to make a DSN and therefore you can't forget it. This connection however still uses ODBC, which is relatively slow, because it doesn't talk to the database directly, but through an extra layer (OLEDB).

The fastest method to set up a database connection is via OLEDB. OLEDB can be compared to ODBC, only newer, more flexible and faster. Drawback is that OLEDB supports a less databases. The most important ones that do have OLEDB support are Access, SQL Server and Oracle.
For OLEDB the connection string looks a bit different again:
objCn.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=db.mdb;User Id=admin;Password=;"

Because Provider can have very divergent values (with ODBC this is less the case), it is difficult to decide what your connection string should look like with different databases. On http://www.able-consulting.com/ado_conn.htm you can find a complete overview of connection strings.

OLEDB is by far the fastest way to set up a database connection. This is true for the set up of the connection itself, that can be up to 4 times faster with SQL Server, and for the retrieving of data. With Access this can be up to almost ten times (!) faster. See for more information the article OLEDB for me on 4 Guys From Rolla.