please tell what is wrong with this cursor

Question

please tell what is wrong with this cursor

anshu84onnet

SSC Eights!

Points: 836
More actions
May 2, 2010 at 8:16 am

#220396

Hi i am new to new cursor can some body please tell me what is wrong with this cursor taking too much time in executing and not giving out any result
declare @test-2 varchar(200)
declare test_cursor cursor read_only
for
select physical_name from sys.master_files
open test_cursor
fetch next from test_cursor
into @test-2
while @@fetch_status = 0
begin
print @test-2
end
close test_cursor
deallocate test_cursor

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 1

Why are you using a cursor in the first place? They are slow, slower than set-based operations in the vast majority of cases.

I can see that this is test code. Are you just playing with cursors, or is there something that you're trying to achieve? If the latter, what? There's a good chance that it can be done without a cursor at all.

As for that code, it'll execute forever because you're printing the same value again and again as you're never fetching anything other than the first row from the cursor. You need another FETCH NEXT within the loop.

Excerpt:

fetch next from test_cursor into @test-2 -- get the first row

while @@fetch_status = 0

begin

print @test-2

fetch next from test_cursor into @test-2 -- get the next row.

end

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

george sibbald SSC Guru Points: 104210 More actions · Answer 2

You have opened the cursor and then gone into a loop which you will never break out of because you are not fetching the next record in the cursor

try this

declare @test-2 varchar(200)

declare test_cursor cursor read_only

for

select physical_name from sys.master_files

open test_cursor

fetch next from test_cursor

into @test-2

while @@fetch_status = 0

begin

print @test-2

fetch next from test_cursor

into @test-2

end

close test_cursor

deallocate test_cursor

Once that is done, throw that code away. you don't need a cursor here, try this, see how much better ir is, to code and in performance.

select physical_name from sys.master_files

Steer clear of cursors for everything except database housekeeping tasks, e.g. looping through all databases to back them up.

---------------------------------------------------------------------

george sibbald SSC Guru Points: 104210 More actions · Answer 3

@ Gail.

Grrrrr. even with an edit you beat me to the punch. 🙂 I thought you were taking a break?

---------------------------------------------------------------------

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 4

I am. Took a complete break for 2 weeks, posting just a little now. Down to 100 posts a month (from 700)

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

george sibbald SSC Guru Points: 104210 More actions · Answer 5

good for you, relax and enjoy!

the above mistake reminds me of my early days in cobol batch programming and the structured programming techniques we were taught. Above structure was called read-ahead.

still comes in useful occasionally!

---------------------------------------------------------------------

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 6

Unfortunately , working on thesis != relaxing. Still break from forums was good and needed.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Jeff Moden SSC Guru Points: 1004436 More actions · Answer 7

anshu84onnet (5/2/2010)
Hi i am new to new cursor...

As others have already stated, you should stay "new to new cursor". Unless you're studying for an exam on SQL Server, you should stay away from cursors altogether until you have learned how to do things in a high speed, set based fashion. Only after that should you learn about how to do things using a cursor because only then will you know enough as to when to actually use a cursor.

As a side bar, cursors should only be used to control sets of information... they should never be used to do things one row at a time.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)