SQL Cursor vs join

Cursor vs join

-- Illustrate doing a join vs ISAM like looping .   A huge performance diff.
-- Look at the value of @stmt_counter to see how many commands SQL Server
-- must execute in the cursor case/
use pubs
SET NOCOUNT ON
go

--- The select statement is lightning fast !
SELECT A.au_id, au_lname, title
FROM authors A 
JOIN titleauthor TA ON (A.au_id=TA.au_id)
JOIN titles T ON (T.title_id=TA.title_id)
ORDER BY A.au_id, title
go


-- Now a cursor solution - order of magnitude slower.
PRINT ''
PRINT ''
GO

declare @au_id char(11),@au_lname varchar(40),@title_id char(6),@au_id2 char(11),@title_id2 char(6),@title varchar(80),@stmt_counter int

DECLARE au_cursor CURSOR FOR
	SELECT au_id,au_lname FROM authors ORDER by au_id

DECLARE au_titles CURSOR FOR
	SELECT au_id,title_id FROM titleauthor ORDER BY au_id

DECLARE titles_cursor CURSOR FOR
	SELECT title_id,title FROM titles ORDER BY title

OPEN au_cursor 

select @stmt_counter=5  -- delcare variables, three cursor declares and an open so far).

FETCH NEXT FROM au_cursor INTO @au_id, @au_lname
select @stmt_counter=@stmt_counter+1

	WHILE (@@FETCH_STATUS =0 )
		BEGIN
			OPEN au_titles
			FETCH NEXT FROM au_titles INTO @au_id2,@title_id
			select @stmt_counter=@stmt_counter+2  
			-- An Open & Fetch every time through
		
			WHILE (@@fetch_status = 0)
			   	BEGIN
				-- If this is for the current author, then get titles too
				IF (@au_id=@au_id2)
					BEGIN
					OPEN titles_cursor
					FETCH NEXT FROM titles_cursor INTO @title_id2,@title
					select @stmt_counter=@stmt_counter+2  
					-- An Open & Fetch every time through
				
					WHILE (@@fetch_status = 0)
						BEGIN
						-- If right title_id, then display the values
						IF (@title_id=@title_id2)
							SELECT @au_id,@au_lname,@title

						FETCH NEXT FROM titles_cursor INTO 								                                                       @title_id2,@title
						select @stmt_counter=@stmt_counter+2  
						-- At min, a Fetch every time
						END
					CLOSE titles_cursor
					END
				FETCH NEXT FROM au_titles INTO @au_id2,@title_id
			    	END

			CLOSE au_titles
		FETCH NEXT FROM au_cursor INTO @au_id,@au_lname
		select @stmt_counter=@stmt_counter+2 -- An Open & Fetch every time through
		END
CLOSE au_cursor

DEALLOCATE titles_cursor
DEALLOCATE au_titles
DEALLOCATE au_cursor
select @stmt_counter=@stmt_counter+4 -- Close and Deallocate
select @stmt_counter