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