-- 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