Performance when using a Recordset, should you create a separate connection object?
To answer this question correctly, you need to test the results in two different situations: the first is to perform a database processing per page, and the second is to perform multiple database processing per page.
In the previous example, we have created a separate connection object and passed it to the ActiveConnection attribute of the recordset. However, it is also possible to simply pass the connection string into this property, thereby avoiding an additional step, which is to ado__03.asp and configure a separate component in the script:
objrs.activeconnection = Application ("Conn")
Although we still created a connection in the recordset, it was created in a very optimized scenario, so we started off with a 23% reduction in startup time, as expected, with almost no difference in the display time of each record.
So, our second rule is:
* When a single Recordset is used, the connection string is passed to the ActiveConnection property.
The following determines whether this logic still holds when multiple recordsets are created on a page. To test this, I introduced the For loop, repeating the previous example 10 times. In this test, we will also look at 3 options:
First, we create and destroy connection objects (ado__04.asp) in each loop:
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, create a separate connection object outside of the loop, and share it with each recordset (ado__05.asp):
Set objconn = Server.CreateObject ("ADODB. Connection ")
objConn.Open application ("Conn")
Dim I
For i = 1 to 10
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
Next
Objconn.close
Set objconn = Nothing
Third, the connection string is passed to the ActiveConnection property (ado__06.asp) in each loop:
Dim I
For i = 1 to 10
Set objRS = Server.CreateObject ("ADODB. Recordset ")
objrs.activeconnection = Application ("Conn")
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
Next
As you may have guessed, creating and destroying connection objects in each loop is a inefficient approach. But surprisingly, just passing a connection string in each loop is a little less efficient than sharing a single Connection object.
Nevertheless, our 3rd rule is:
* When multiple recordsets are used on a page, create a Connection object and reuse it in the ActiveConnection property.
Which of the types of pointers and locks are most effective?
So far, all of our tests have used only a forward (Forward only) pointer to loop through the recordset. However, ADO also provides 3 types of pointers to the recordset: Static, Dynamic, and keyset. Each provides additional functionality, such as moving forward and backwards, and the ability to see changes when someone else establishes the data. However, it is not the scope of this article to discuss the implications of these pointer types. I'll leave that to you. The following are various types of comparative analysis.
These additional pointers significantly result in greater load (ado__03.asp) than their homogeneous forward. In addition, these pointers are slower during the cycle. One piece of advice I'd like to share with you is to avoid the idea: "I need a dynamic pointer from time to moment, so I just always use it." ”
In essence, the same problem applies to the type of lock. The previous test used only a read only (read-only) type of lock. However, there are three different types of locks: Lock pessimistic, lock optimistic, and lock Batch optimistic. As with pointers, these locks provide additional functionality and control for processing data in a recordset. Again, I will learn the proper use of each lock setting for your own content.
So the logic that leads us to consider rule 4 is simple: Use the simplest pointers and types of locks that best fit your task.
What is the best way to get a recordset?
So far, we've only recovered the recordset through the Recordset object. But ADO also provides some indirect ways to get a recordset. The next test compares the values in ado__03.asp with the creation of a Recordset object (conn_01.asp) directly from a connection object.
Set objconn = Server.CreateObject ("ADODB. Connection ")
objConn.Open application ("Conn")
Set objRS = objConn.Execute (Application ("SQL"))
We see that the load has a slight increase, showing that the time of each record has not changed.
Then, let's look at creating a Recordset object directly from a command object (cmd__01.asp):
Set objcmd = Server.CreateObject ("Adodb.command")
objcmd.activeconnection = Application ("Conn")
objCmd.CommandText = Application ("SQL")
Set objRS = Objcmd.execute
We see again that the load has a slight increase, and each record has a nominal difference in the display time. Although the last two methods have little impact on performance, there is a big problem to consider.
Creating a Recordset from a recordset class provides the greatest flexibility in controlling how recordsets are handled. While other methods do not raise an overwhelming performance problem, you are confused by what type of pointer and lock type you return by default, which is not necessarily optimal for your particular needs.
So, unless you need other methods for some particular reason, follow rule 5th: Set the Recordset to the Adodb.recordset class to get the best performance and maximum flexibility.
Should recordsets be disconnected?
ADO provides a choice to disconnect a recordset, which restores all data in a forward query, closes the connection, and uses a local (or client) pointer to move through the data set. This also provides an opportunity to release the connection early. This situation is for processing remote data
