The challenge:
Recently on the 15seconds ASP email list there was some discussion about whether using a DSN, DSN-Less or OLEDB connection string to open an ADO connection would result in faster performance.

It was argued that using a DSN required a registry lookup to determing the connection string's parameters, and that this method would thus be slower than using a DSN-Less connection string or an OLEDB connection string where the parameters are supplied directly.

TestBed
The following diagram outlines my test lab. There are three client machines. Two are Pentium 166 MHz with 64 MB RAM running Windows NT 4 Workstation SP6. The 3rd client machine is a PentiumIII 700 MHz with 384 MB of RAM running Windows XP Professional. Each of the client machines is running Microsoft's WAST v1.1.

There are two servers. The webserver is a PentiumIII 650 MHz with 384 MB of RAM running Windows 2000 Server SP2, with IIS v5 and MDAC v2.7. The database server is a dual PentiumIII 866 MHz with 512 MB of RAM running Windows 2000 Server SP2 and SQL Server 2000 SP2

The Test
The test involved using Web Application Stress Tool to hit pages using differing connection strings over a period of two minutes each (with a 5 second warmup and cooldown period). The tests involved using opening an ADODB connection using a DSN, DSN-less and OLEDB connection strings stored as application variables, and lastly an OLEDB connection string stored in an include file (to test whether an application variable lookup was a limiting factor).

The Results

The Code
The code for the DSN connection string test:

<%
Option Explicit
Dim objConn

Set objConn = Server.CreateObject("ADODB.Connection")
objConn.Open "DSN=DSNTest;UID=***;PWD=***;"

objConn.Close
Set objConn = Nothing
%>

For the DSNLess and OLEDB connection string tests the connection was allocated to an application variable (in the global.asa), as follows:

Sub Application_OnStart
   Application("DSNLessConnStr") = "Provider=MSDASQL;Driver={SQL Server};Server=KJServer1;UID=***;PWD=***"
   Application("OLEDBConnStr") = "Provider=SQLOLEDB;Data Source=KJServer1;User ID=***;Password=***"
End Sub

The code for the DSN-Less connection string test is as follows. The OLEDB test involved the same code with the application variable changed to use an OLEDB connection string instead.

<%
Option Explicit
Dim objConn

Set objConn = Server.CreateObject("ADODB.Connection")
objConn.Open Application("DSNLessConnStr")

objConn.Close
Set objConn = Nothing
%>

In case a bottleneck developed in the lookup of the application variable's value, the following code was used to provide an OLEDB connection string via an include file instead:

<%
Option Explicit
Dim objConn
Dim strConn
%>
<!-- #include file="ConnString.asp" -->
<%
Set objConn = Server.CreateObject("ADODB.Connection")
objConn.Open strConn

objConn.Close
Set objConn = Nothing
%>

Conclusions
It appears that the limiting factor in all the tests was the underlying network, which was saturated by page requests. The actual connection string used did not appear to be a limiting factor. To test this I performed a 5th test where only two clients where used. In this fifth test (against the registry based DSN connection string) the number of individual page requests per client increased from ~9,000 to ~14,000 however the total number of page requests served did not increase, remaining at ~28,000.

The other possible conclusion would be that the web, or database server was swamped with connection requests. However it appears to make little difference which connection string method is used, since all methods topped out at very similar numbers of requests served per minute

Whilst I personally recommend that developers use native OLEDB Providers for SQL Server or Access/Jet (Oracle is a different matter) it appears, looking only at performance, that the actual connection string used does not appear to have any significant impact upon the web application.

Any comments on the above are more than welcome!

Back to the experiments listing.