Simple Cursor Example : FORWARD_ONLY vs FAST FORWARD

Download PDF

First lets take a look at two queries using CURSORS, the first one will use the FORWARD_ONLY type cursor, and the second will use the FAST_FORWARD type cursor. These two types sound very similar, but perform quite differently.

DECLARE @firstName as NVARCHAR(50);
DECLARE @middleName as NVARCHAR(50);
DECLARE @lastName as NVARCHAR(50);
DECLARE @phone as NVARCHAR(50);
DECLARE @PeoplePhoneCursor as CURSOR;

SET @PeoplePhoneCursor = CURSOR FORWARD_ONLY FOR
SELECT TOP 10 FirstName, MiddleName, LastName, PhoneNumber
  FROM person.Person p
 INNER JOIN person.personphone pp on p.BusinessEntityID = pp.BusinessEntityID;

OPEN @PeoplePhoneCursor;
FETCH NEXT FROM @PeoplePhoneCursor INTO @firstName, @middleName, @lastName, @phone;
 WHILE @@FETCH_STATUS = 0
BEGIN
	 PRINT ISNULL(@firstName, '') + ' ' +
	       ISNULL(@middleName, '') + ' ' +
	       ISNULL(@lastName, '')  +
	       ' Phone: ' + ISNULL(@phone, '') ;
	 FETCH NEXT FROM @PeoplePhoneCursor INTO @firstName, @middleName, @lastName, @phone;
END
CLOSE @PeoplePhoneCursor;
DEALLOCATE @PeoplePhoneCursor;

Now for the FAST_FORWARD CURSOR example. Notice only one line has changed, that’s the line that says “SET @PeoplePhoneCursor = CURSOR FAST_FORWARD FOR”.

DECLARE @firstName as NVARCHAR(50);
DECLARE @middleName as NVARCHAR(50);
DECLARE @lastName as NVARCHAR(50);
DECLARE @phone as NVARCHAR(50);
DECLARE @PeoplePhoneCursor as CURSOR;

-- HERE IS WHERE WE SET THE CURSOR TO BE FAST_FORWARD
SET @PeoplePhoneCursor = CURSOR FAST_FORWARD FOR
SELECT TOP 10 FirstName, MiddleName, LastName, PhoneNumber
  FROM person.Person p
 INNER JOIN person.personphone pp on p.BusinessEntityID = pp.BusinessEntityID;

OPEN @PeoplePhoneCursor;
FETCH NEXT FROM @PeoplePhoneCursor INTO @firstName, @middleName, @lastName, @phone;
 WHILE @@FETCH_STATUS = 0
BEGIN
	 PRINT ISNULL(@firstName, '') + ' ' +
	       ISNULL(@middleName, '') + ' ' +
	       ISNULL(@lastName, '')  +
	       ' Phone: ' + ISNULL(@phone, '') ;
	 FETCH NEXT FROM @PeoplePhoneCursor INTO @firstName, @middleName, @lastName, @phone;
END
CLOSE @PeoplePhoneCursor;
DEALLOCATE @PeoplePhoneCursor;

At this point the two queries aren’t that different. Let take a look at how they perform.

To do this, First I turn on Actual Execution Plans, then I highlight both queries in SSMS and run them. The results look something like this.
forward_only_cursor1
From which we can see that opening the cursor takes 8% of the cost of the entire batch, and each step through the cursor takes up another 6% of the entire batch. So overall the first example using the FORWARD_ONLY cursor takes 72% of the entire batch.

When we look at the plan associated with the second cursor loop, using the FAST FORWARD cursor option, we see different numbers:

fast_forward_cursor

Again, opening the CURSOR takes 8% of the batch, then each lop through takes 1% for a total of 18% of the cost of the entire batch. The missing 10% is accounted for in rounding errors since SQL Server Management Studio only shows whole numbers on the percentage of the batch.

With can see with this example the FORWARD_ONLY CURSOR takes 4 times the time as the FAST FORWARD CURSOR, and the number continues to widen as the number of times the cursor loops is executed.

FAST FORWARD CURSORS are usually the fastest option with SQL Server. There may be cases where another option may work better, but the FAST FORWARD CURSOR is a good place to start if you must use a CURSOR.

 

Related Links

 

Tagged with: , , , , , , ,

Leave a Reply

Your email address will not be published. Required fields are marked *

*

Time limit is exhausted. Please reload CAPTCHA.