(Author: Green Apple studio compilation, 2000 at 13:45 on November 13)
When using a recordset, should a separate Connection object? If you want to answer this question correctly, you need to verify the test results in two different situations: The first is to perform a database processing per page, the second is Pages perform multiple database processing. In the previous example, we have created a separate connection object and passed it to the ActiveConnection property of the recordset. However, it is also possible to pass the connection string to this property, so that an additional step is to be avoided, that is, in the script (ADO__03.asp), and configure a separate component: objrs.activeConnection = Application ("conn") We still create a connection in the recordset, but it is created in a very optimized, so we just see that the startup time is 23% lower than the previous test, like expected, each record There is almost no difference in display time. Therefore, our second rule is: * When using a single recordset, the connection string is passed to the ActiveConnection property. Below you want to determine if multiple record sets are created on a page, this logic is still established. To test this situation, I introduced the For loop and repeated the previous example 10 times.
In this test, we will also study 3 options: First, we create and destroy the Connection object in each loop (ADO__04.asp): 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 the 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 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 REC ORDS FOUND ") Else 'Write Headings ...' Write Data ... End if Objrs.close set objrs = Nothing Next Objconn.close set objconn = Nothing third, pass the connection string to the ActiveConnection property in each loop (ADO__06.asp): 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 You may have guessed, in each loop Creating and destroying the Connection object is a low efficiency. But amazing is that only the efficiency of transmitting the connection string in each cycle is only a little bit lower than the efficiency of the shared single. Despite this, our Article 3 Rules is: * Create a Connection object when using multiple record sets on a page, reuse it in the ActiveConnection property. In the type of pointers and locks, which is the most effective? So far, all of our tests use only a pointer for forward only in the recordset. However, ADO also provides three types of pointers for records: Static, Dynamic, and KeySet (keyboard). Each provides additional functions, such as moving and backward movement, and when others are established, you can see the modified function. However, the connotation of these pointer types is not the scope discussed herein. I left these to you. Below is a comparative analysis of various types. These additional pointers have significantly caused a larger load (ADO__03.asp) compared to their similar Forward Only. In addition, these pointers are slower during cycle. I want to share with you to avoid this idea: "I need a Dynamic pointer from time to time, so I simply use it." From essentially, the same problem is also suitable for lock type. Only the READ ONLY type lock is only used in the previous test. However, there are three types of locks: Lock Pessimistic, Lock Optimistic and Lock Batch Optimistic. Like the choice of pointers, these locks also provide additional functions and controls for data in the processing record set. Similarly, I will give you myself for you to learn the appropriate use of each lock setting. So guiding us to consider the logic of rule 4 very simple: using the simplest pointer and lock of the task that best suits you. What is the best way to get a record set? So far, we just recover records through the Recordset object. But ADO also provides some indirect methods for acquiring records. The next test compares the values in the ADO__03.asp to create a recordset object directly from a Connection object (conn_01.asp). Set objconn = server.createObject ("adoDb.connection") Objconn.open Application ("conn") set objrs = objconn.execute (Application ("SQL")) We see, there is a slight increase in load, showing each The record time has not changed.
Then, let's see 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 a slight increase in the load again, and each record has a nominal difference. Although the last two methods have little effect on performance, there is a big problem that needs to be considered. Creating a recordset via the Recordset class For control how to handle the record set provides maximum flexibility. Although other methods have not proposed an overwhelming performance problem, you will be confused by the default states and lock types, which are not necessarily optimal for your specific needs. So, unless you need other methods because of some special reason you need other methods: Examples of the record set to get the best performance and maximum flexibility through the AdoDb.Recordset class. Should I disconnect the record? ADO provides a selection for disconnecting a recordset, and the recording set will resume all data in a forward query, turn off the connection, and use a local (or customer) pointer to move in the data set. This also provides an opportunity for an early release connection. This situation is necessary for processing remote data services, because the data must be disconnected from the database. But is it good for ordinary uses? Here we added CursorLocation property, set to open after closing the connection record (CLIENT1.asp): Set objRS = Server.CreateObject ( "ADODB.Recordset") objRS.CursorLocation = 3 'adUseClient objRS.ActiveConnection = Application ( "Conn") objRS .LockType = 1 'AdlockReadOnly objrs.open application ("sql") objrs.activeConnection = Nothing Theoretically, this technology should result in faster performance. There are two reasons: First, when moving in the recordset, avoiding the repeated request by the connection; secondly cancel the connection to reduce the resource requirements. However, when using the client pointer, the efficiency is low. May be due to the use of the customer pointer position, no matter what your settings, Cursortype is modified to static. Rules 6 is this: unless it is required in an open environment, avoiding disconnects. What is the best way to set up recordset properties? All of the previous tests are set directly to the properties of the record set through separate attribute settings. But the RecordSet.open function can receive additional parameters for all properties we need.
