Select Proper Cursor and Lock Types

Select Proper Cursor and Lock Types

You can improve performance of your ADO applications by properly selecting the Cursor and Lock Types while opening the recordsets. Cursor Type indicates the type of the cursor the provider should use when opening the recordset. This may be changed by the provider if the cursor type is not supported. Lock Type specifies the type of locking that the provider should use when opening the recordset.

If you are using SQL Server and server side cursors, then dynamic or forward only cursor types are more efficient when selecting the data. This is because the data is not copied into tempdb first. The keyset and static cursors require a copy of data to be put into tempdb in the SQL Server before the cursor is created. If you use SQL Trace, you will find a lot more activity for keyset and static cursors. As the number of rows will increase in the recordset the usage of keyset and static cursors will reduce the performance of the application.

The only advantage/disadvantage with forward-only cursors is that cursor movement can only be made one row at a time in a forward direction. If you are not going to change any data, then use read-only cursors. This way you will not waste the resources of the data store.


Share the Post: