This tip comes from Michael B.
|Performance Issues with Cursors|
There's an interesting article over at SQLTeam.com that looks at the performance of cursors compared to using
single SELECT statements a number of times. You may wish to read this article before deciding on whether or
not to use a cursor!|
Databases are good at handling sets of data, but oftentimes, as developers, we're interested in a particular
row (or rows) of a set of data. For example, in an ASP page you may wish to display the contents of a particular
SQL query. Note to do this, though, you must sequentially step through each row in the resulting Recordset.
In order to accomodate that, database systems offer the use of cursors, which allow a set of data to be
iterated through. In fact, when using a Recordset object, you can specify what type of cursor to use (the
default is a
adForwardOnly cursor, meaning that you can only iterate forward through the results);
for more information on ADO's Recordset's
CursorType property, be sure to read:
Cursor & LockType Performance Issues.
While the Recordset object uses cursors implicitly, if you wish to programmatically iterate through the results of a SQL query in a stored procedure, you must explicitly create and use a cursor. While the syntax and sematics for this task are not overly complex, it does involve a bit of code and can be a rather mundane task if you are working on a project that requires many stored procedures using many cursors. Therefore, to help expedite the process of creating and using cursors, I wrote this little ASP page that allows the user to specify a cursor name and the name of the column they are interested in. After entering this information, the syntax for the cursor is created - all the developer has left to do is write the SQL query to populate the cursor and the needed code to work with the current row from the query in the appropriately marked positions! (To learn more about cursors be sure to read: Cursors: An Overview; for more information about stored procedures, be sure to read: Writing a Stored Procedure.)
The code for the ASP page to automatically generate the cursor code can be seen below. You can even try out a live demo.
|Return to user tips...|