Wednesday, August 6, 2008

Some thing good about cursors performance

Mostly cursors are notorious for performance degradation and resource consumption. And are used as last option always. But there is situation when cursors may help you to boost the performance. If your application or procedure requires the repeated use of a set of records,it is faster to create a cursor once and reuse it several times than to repeatedly query the database. But do not forget to properly close the cursor at end and also deallocate the resources.
Follow these steps to create, use, and close a database cursor:

  • Create the cursor
  • Open the cursor for use within the procedure or application
  • Fetch a record's data one row at a time until you have reached the end of the cursor's records
  • Close the cursor when you are finished with it
  • Deallocate the cursor to completely discard it

Consider the following example of cursor created for pubs..authors
DECLARE @fName VARCHAR(40)
DECLARE @lName VARCHAR(40)
DECLARE @city VARCHAR(40)

DECLARE OAKLAND CURSOR FOR
SELECT au_lname, au_fname, city
FROM pubs..authors
OPEN OAKLAND

FETCH NEXT FROM OAKLAND INTO @fName, @lName, @city

WHILE (@@FETCH_STATUS <> -1)
BEGIN
FETCH NEXT FROM OAKLAND INTO @fName, @lName, @city
IF (@city = 'OAKLAND')
BEGIN
PRINT @city
END
END
GO
CLOSE OAKLAND

DEALLOCATE OAKLAND

GO

You may find more detail use of cursors in online books.

1 comment:

  1. nice to see some thing good about cursors

    ReplyDelete

Any Comments: