ASP Performance Test Part II (EXT) (v)

Should you create a separate connection object when using recordsets?
To correctly answer this question, we must analyze the tests under two different conditions: first, the page has only one database transaction; second, the page has multiple database transactions.
In the previous precedent, we created a separate connection object and assigned it to the recordset's ActiveConnection property. However, as ado__03.asp shows, we can also assign the connection string directly to the ActiveConnection property, and the additional step of initializing and configuring the Connection object in the script can be omitted.
objrs.activeconnection = Application ("Conn")
Although the Recordset object still creates a connection, the creation at this time is done under highly optimized conditions. As a result, the page overhead has dropped by 23% compared to the previous test, and as expected, the display time for individual records has not changed materially.
Therefore, our second rule is as follows:
If you use only one recordset, assign the connection string directly to the ActiveConnection property.
We then check whether the above rules are still valid when the page uses more than one recordset. To test this scenario, we introduce a for loop that repeats the precedent 10 times. In this test, we will look at three different changes:
First, as shown in ado__04.asp, create and dismantle connection objects in each cycle:
Dim I
For i = 1 to 10
Set objconn = Server.CreateObject ("ADODB. Connection ")
objConn.Open application ("Conn")
Set objRS = Server.CreateObject ("ADODB. Recordset ")
Objrs.activeconnection = objconn
Objrs.cursortype = 0 ' adopenforwardonly
Objrs.locktype = 1 ' adlockreadonly
Objrs.open application ("SQL")

If objrs.eof Then
Response.Write ("No Records Found")
Else
' Write headings
...
' Write Data
...
End If
Objrs.close
Set objRS = Nothing
Objconn.close
Set objconn = Nothing

Next
Second, as shown in ado__05.asp, create the connection object outside the loop, and all recordsets share the object:
Set objconn = Server.CreateObject ("ADODB. Connection ")
objConn.Open application ("Conn")
Dim I
For i = 1 to 10
Set objRS = Server.CreateObject ("ADODB. Recordset ")