Program | Performance VII, which method is the best way to extract recordsets?
So far we have been extracting recordsets by creating Recordset objects, but ADO also provides an indirect method of Recordset extraction. The following tests compare ado__03.asp and create recordsets (conn_01.asp) directly from the Connection object:
Set objconn = Server.CreateObject ("ADODB. Connection ")
objConn.Open application ("Conn")
Set objRS = objConn.Execute (Application ("SQL"))
You can see a slight increase in page overhead and no change in the display time of individual records.
Now let's take a look at creating a Recordset object directly from the command object (cmd__02.asp):
Set objcmd = Server.CreateObject ("Adodb.command")
objcmd.activeconnection = Application ("Conn")
objCmd.CommandText = Application ("SQL")
Set objRS = Objcmd.execute
Similarly, page overhead also increases slightly, while the display time of individual records does not change in nature. The difference in performance between these two methods is small, but we still have one important issue to consider.
When you create recordsets from the Recordset class, we are able to control how recordsets are handled with the greatest flexibility. Since the following two methods do not have overwhelming performance, we mainly consider the default return cursor types and record locking, for some occasions, the default value is not necessarily the most ideal.
Therefore, unless you need to select the following two methods for special reasons, we recommend that you consider this rule:
L Instantiate recordsets through the Adodb.recordset class for the best performance and flexibility.
Should you use a local recordset?
ADO allows the use of a local (client) recordset, at which point the query extracts all the data in the recordset, and the connection can be closed immediately after the query completes, and the data is later accessed using a local cursor, which facilitates the release of the connection. Using a local Recordset is important for accessing remote Data services that require data to be used offline, so is it also helpful for ordinary applications?
Let's add the CursorLocation property and close the connection after the recordset is opened (client1.asp):
Set objRS = Server.CreateObject ("ADODB. Recordset ")
Objrs.cursorlocation = 2 ' adUseClient
objrs.activeconnection = Application ("Conn")
Objrs.locktype = 1 ' adlockreadonly
Objrs.open application ("SQL")
Objrs.activeconnection = Nothing
In theory, this approach is beneficial for the following two reasons: first, it avoids repeatedly requesting data through the connection while moving between records; second, it eases resource requirements because of the ease with which the connection can be released. However, the use of local recordsets from the table above appears to be less useful for improving efficiency. This may be because when you use a local recordset, the cursor always becomes a static type, regardless of what the program sets.
The 6th rule is as follows:
L Avoid using a recordset unless you do require it to be localized.
