To read the article online, visit http://www.4GuysFromRolla.com/webtech/062799-3.shtml

Cursor & LockType Performance Issues


Whenever you are retrieving information from a database using ADO, you are using a Recordset object. Recordset objects can be created implicitly or explicitly. Many developers use code similar to this:

Dim objConn
Set objConn = Server.CreateObject("ADODB.Connection")
objConn.Open "DSN=SomeDSN"

Dim objRS
Set objRS = objConn.Execute("SELECT * FROM MyTable")

In this scenario, objRS is the Recordset object. It was created implicitly by ADO. When a Recordset is created implicitly, the default cursor and locktypes are assigned to the recordset. When you execute a query, you are returned a set of rows. The cursor determines how the rows in the set are iterated through, and if the rows accurately reflect the real-time state of the database. There are four types of cursors: ForwardOnly, Static, Dynamic, and Keyset. Each of these cursors treats the set of data returned by SQL differently. For more information on cursors, be sure to read Recordset Cursors: Choose the Right Cursor for the Right Job.

The locktype determines how to lock the records when you try to update a row in your resultset. Since multiple folks could have sets of data that overlap, the question exists, "How does the database handle individual updates to these sets of data, which may overlap?" There are also four types of locking: ReadOnly, LockOptimistic, LockPessimistic, and adLockBatchOptimistic. So, if you open up a recordset with ReadOnly locking, you cannot update the rows of data in your current result set. In fact, in my opinion, you never should update a recordset. Rather, if you wish to update a row in a table, issue an UPDATE SQL command or use a stored procedure. (Just my two cents.)

You cannot set a recordset object's locktype and cursor type when you create it implicitly. To set its locktype and cursor, you must create the recordset explicitly. To do this, you can use the following code:

Dim objConn
Set objConn = Server.CreateObject("ADODB.Connection")
objConn.Open "DSN=SomeDSN"

'EXPLICITLY Create a recordset object
Dim objRS
Set objRS = Server.CreateObject("ADODB.Recordset")

'Now you can play with the locktype
objRS.LockType = adLockReadOnly

'Now set the cursor
objRS.CursorType = adOpenForwardOnly

Pretty simple. This, of course, assumes you have included ADOVBS.inc. You'll notice that I used the two constants adLockReadOnly and adOpenForwardOnly. These map to numeric values in ADOVBS.inc. To learn how to include ADOVBS.inc in your ASP pages, or to obtain a copy of ADOVBS.inc, be sure to read ADOVBS.inc... use it!

There are those developers who dislike using the adOpenForwardOnly cursor, because it is a one-way cursor; i.e., you can iterate from front to back only. This forward-only movement prevents developers from using the .RecordCount property to obtain the number of records in the recordset. (This is because .RecordCount moves to the end and then to the beginning of the recordset in order to count the number of records in the recordset. Since a forward-only cursor can't move backwards, .RecordCount can't move back to the beginning of the recordset.) Due to this reason, some developers open all their recordsets as adOpenDynamic or adOpenStatic. This is not a good idea, since the forward-only cursor is the most efficient cursor available. (As an asside, here's an article that helps explain how not to count the number of records in your recordset.)

To determine what recordset cursor and locktype were the most efficient, I performed some heavy duty stress testing. For the complete stress test report (i.e. the steps involved and the results for the various locktype/cursor combinations, be sure to read the locktype/cursor performance report.

Hopefully this article has convinced you of two things: first, create your recordset's explicitly. From a code maintenance prospective it's easier to see what's happening when you create every object explicitly. Also, explicit creation allows you to alter the locktype and cursor type for the recordset object. The second thing that this article has hopefully convinced you of is that you should always strive to use the adOpenForwardOnly cursor, and the adReadOnly locktype.

If you need some further convincing, be sure to read the locktype/cursor performance report.

Happy Programming!


Article Information
Article Title: 4GuysFromRolla.com - Cursor & LockType Performance Issues
Article Author: Scott Mitchell
Published Date: Sunday, June 27, 1999
Article URL: http://www.4GuysFromRolla.com/webtech/062799-3.shtml


Copyright 2017 QuinStreet Inc. All Rights Reserved.
Legal Notices, Licensing, Permissions, Privacy Policy.
Advertise | Newsletters | E-mail Offers