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: