March 17, 2006 at 2:34 pm
DB Guru's,
Please suggest me, the pros and cons of these two approaches.
List out the situation/context to go for either of these approaches.
Is there any other best way to do....
Approach 1:
SET NOCOUNT ON
DECLARE @au_id varchar(11), @au_fname varchar(20), @au_lname varchar(40),
@message varchar(80)
PRINT '-------- Utah Authors report --------'
DECLARE authors_cursor CURSOR FOR
SELECT au_id, au_fname, au_lname
FROM authors
WHERE state = 'UT'
ORDER BY au_id
OPEN authors_cursor
FETCH NEXT FROM authors_cursor
INTO @au_id, @au_fname, @au_lname
WHILE @@FETCH_STATUS = 0
BEGIN
PRINT ' '
SELECT @message = '----- Books by Author: ' +
@au_fname + ' ' + @au_lname
PRINT @message
FETCH NEXT FROM authors_cursor
INTO @au_id, @au_fname, @au_lname
END
CLOSE authors_cursor
DEALLOCATE authors_cursor
GO
Approach 2:
SET NOCOUNT ON
DECLARE @au_id varchar(11), @au_fname varchar(20), @au_lname varchar(40),
@message varchar(80),@Error integer, @Rowcount integer
PRINT '-------- Utah Authors report --------'
SELECT @au_id = ''
WHILE 1 = 1
BEGIN
SELECT @au_id = MIN(au_id)
FROM authors WHERE state = 'UT'
AND (au_id > @au_id)
SELECT @Error = @@error,
@Rowcount = @@rowcount
IF (@Rowcount = 0) OR (@au_id IS NULL)
BREAK
SELECT @au_id = au_id,
@au_fname = au_fname,
@au_lname = au_lname
FROM authors
WHERE state = 'UT'
AND au_id = @au_id
ORDER BY au_id
PRINT ' '
SELECT @message = '----- Books by Author: ' +
@au_fname + ' ' + @au_lname
PRINT @message
END --WHILE Loop through each record
Thanks,
Ganesh
March 17, 2006 at 3:00 pm
How about just:
PRINT '-------- Utah Authors report --------' SELECT '----- Books by Author: ' + au_fname + ' ' + au_lname FROM authors WHERE state = 'UT' ORDER BY au_id
Then there is no cursor, no loop.
Of course, if you actually want to print book titles also, I would still look for a way to do it as a set. Perhaps something like this with a temp table (warning: just typed this here, not tested, syntax may have mistakes).
SELECT au_id, 2 as seq, title INTO #tmp FROM authors a inner join titleauthor ta on a.au_id = ta.au_id inner join titles t on ta.title_id = t.title_id WHERE state='UT'
INSERT INTO #tmp SELECT au_id, 1 as seq, '----- Books by Author: ' + au_fname + ' ' + au_lname FROM authors WHERE state = 'UT'
INSERT INTO #tmp VALUES(0,0,'-------- Utah Authors report --------')
SELECT title FROM #tmp ORDER BY seq, au_id
I'm sure testing would have to bear it out. But I have to believe that use just set queries are going to be better than cursors and/or while loops.
Hope this helps
Mark
March 17, 2006 at 3:21 pm
Thanks mark....
Sorry, I didn't frame my question properly....
My question was whether using cursor is optimized or using a while loop is optimized.
Consider a situation I have to loop through all the records and subsequently call a stored procedures....
Thanks,
Ganesh
March 17, 2006 at 4:08 pm
From an optimization standpoint, you may get better performance from your while loop due to the overhead that a cursor adds when allocating/deallocating, but then again, you may not. I would personally not use either approach. Any type of structure, cursors or loops, where you are performing row-by-row processing is terribly inefficient compared to set based processing.
So to answer your question, in order to compare cursors with any other type of loop, you will need to consider each on a case by case basis. Sometimes a cursor will outperform a while loop and vice versa. With that said, you should always explore methods of accomplishing your goal with a set based approach and only use row-by-row processing as a last resort. The vast majority of tasks that most people use cursors for can be done set based.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply