Cursor performance... - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

Cursor performance...

Author

Topic

Lumbago
Norsk Yak Master

3271 Posts

Posted - 2004-07-18 : 12:24:02

In my database I have a few procedures that after a few selects and table-variables and some other stuff has to update 5 or 6 different tables on a row-by-row basis. This is just the way it has to be. I have the option of using a cursor for this or to "fake" the cursor using a temp-table and an identity-field.

I have read just about a million times that I should stay away from cursors, but I have never really read an explanation to *why* I should avoid them except for the fact that "there are performance issues". To me, using a cursor or faking it like I described doesn't make any difference but why is one better than the other? What goes on "under the hood" that is so horrible? Or is it just that I should strive until my brain blows to make everything set-based because this in most cases is faster?

--
Lumbago
"Real programmers don't document, if it was hard to write it should be hard to understand"

derrickleggett
Pointy Haired Yak DBA

4184 Posts

Posted - 2004-07-18 : 13:18:08

You should always strive to have everything set-based, not because it is fater in most cases, but because it's faster in EVERY SINGLE case. :) SQL is a set-based language. That's what it was designed for. I find it hard to believe you have to update 5 or 6 tables on a row-by-row basis, but that's not for me to believe or nor. :)

The reason a cursor costs more is because of the cursor operation itself. The cursor is maintained as a seperate process in SQL Server and is extremely resource intensive. If you can use a WHILE loop and table variable to accomplish the same thing, you don't have the overhead of maintaining the cursor.

MeanOldDBA
derrickleggett@hotmail.com

When life gives you a lemon, fire the DBA.

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2004-07-18 : 14:49:41

Suppose you are working in a programming language that allows you to output text to the screen.

there are two commands you can use to do this: "Print" and "PrintCharacter".

"Print" will print an entire text string to the screen; "PrintCharacter" can print just 1 character at a time.

Your task: write a program to output the phrase "Hello World.".

There appears to be 2 ways to write this program:

Option #1:
Print "Hello World."

Option #2:
PrintCharacter "H"
PrintCharacter "e"
PrintCharacter "l"
PrintCharacter "l"
PrintCharacter "o"
PrintCharacter " "
PrintCharacter "W"
PrintCharacter "o"
PrintCharacter "r"
PrintCharacter "l"
PrintCharacter "d"
PrintCharacter "."

If you are using cursors instead of setbased statements in SQL, it is the equivalent of using option #2 -- your programs are needlessly longer and slower, and more complex. It is a function of the programming language you are programming in -- if this imaginary language didn't allow for a "print" command and you had to print 1 character at a time, by all means that's the way to do it. But if it allows you to do it all at once, then isn't that probably something you should learn to take advantage of?

- Jeff

Kristen
Test

22859 Posts

Posted - 2004-07-18 : 16:58:38

For me the thing that turned me off cursors is that most loop-based scripts offered here by the GURUs do something like:


SELECT MyPKColumn
INTO   #TEMPTABLE
FROM   MyTable
SELECT @intRowCount = @@ROWCOUNT

WHILE @intRowCount > 0
BEGIN
    SELECT TOP 1 @Working = MyPKColumn
    FROM #TEMPTABLE

    ... do stuff ...

    DELETE FROM #TEMPTABLE
    WHERE MyPKColumn = @Working

    SELECT @intRowCount = @intRowCount - 1
END

Clearly this type of problem is a clear candidate for a Cursor, and given that this is the prefered solution you can guess Just How Bad Cursors Are<gg>

Kristen

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2004-07-18 : 23:04:44

Actually, those examples are pretty bad "cursor-killing" overkill. if you need to loop through a recordset and *do* something for each row, such as calling a lengthy stored procedure or external program or generating output, by all means use a cursor.

I personally feel it's still debatable if that type of solution is preferable to a cursor. Avoding a cursor in that situation requires longer, harder to read and understand code rather than a more straightforward cursor.

- Jeff

derrickleggett
Pointy Haired Yak DBA

4184 Posts

Posted - 2004-07-19 : 00:40:00

??? How is that syntax harder to read then a cursor? You've just been using cursors too long. :) Anyone who has ever programmed in their life would understand that.

MeanOldDBA
derrickleggett@hotmail.com

When life gives you a lemon, fire the DBA.

Kristen
Test

22859 Posts

Posted - 2004-07-19 : 04:33:40

"Anyone who has ever programmed in their life would understand that."

You're holding back again Derrick ... I can tell ...

Kristen

Lumbago
Norsk Yak Master

3271 Posts

Posted - 2004-07-19 : 05:27:03

The example Kristen showed here was what I was referring to when I was talking about a faked cursor. There is no problem for me whatsoever to use this method and I don't find it very complex to understand, but what I'm really concerned with is the performance. There is naturally no doubt in my mind that Jeffs "print" runs helluva lot faster than "printcharacter", but thats not really my point.

Is a cursor better than Kristens example and if thats the case then why or why not?

Kristen
Test

22859 Posts

Posted - 2004-07-19 : 07:02:45

The learned opinions I read on SQLTeam suggest that my example is faster (provided that's what you want to do - if you want an UPDATE cursor, or repeatable reads, or going backwards and forwards, and so on, then a cursor is probably Just The Ticket.)

However, I think I've miscreated my example. I'm pretty sure the way this is done doesn't use a DELETE, but instead something like


SELECT MyPKColumn
INTO   #TEMPTABLE
FROM   MyTable
SELECT @intRowCount = @@ROWCOUNT

WHILE @intRowCount > 0
BEGIN
    SELECT TOP 1 @Working = MyPKColumn
    FROM #TEMPTABLE
    WHERE MyPKColumn > @Working
    ORDER BY MyPKColumn
    ... do stuff ...

    DELETE FROM #TEMPTABLE
    WHERE MyPKColumn = @Working

    SELECT @intRowCount = @intRowCount - 1
END

which looks to me like it needs something to ensure that the first row gets picked - no use if @Working IS NULL

But I reckon you need to benchmark both methods if PERFORMANCE is a big part of what you want to achieve.

Kristen

derrickleggett
Pointy Haired Yak DBA

4184 Posts

Posted - 2004-07-19 : 08:09:38

threeguysfromrolla have written a white paper on cursors if you want to read about it. It can be found on their website.

MeanOldDBA
derrickleggett@hotmail.com

When life gives you a lemon, fire the DBA.

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2004-07-19 : 08:56:34

quote:
Originally posted by derrickleggett

??? How is that syntax harder to read then a cursor? You've just been using cursors too long. :) Anyone who has ever programmed in their life would understand that.

Derrik -- I'm going to assume you are toally joking around here and not get offended.

If you *need* to fetch 1 row at a time from a table to *do* something, you can use a cursor and not feel guilty about it. ever program an ASP page? guess what happens when you open the recordset and display it? a cursor! how does QA display the results on the screen for you? a cursor! how does ADO.NET fill up a datagrid? a cursor! they exist, guys.

Now, hopefully, those who read this understand the specific sistuations I am referring too -- and hopefully, guys like Derrik have taken the time to look at my contributions to this site to get an understanding of where I stand towards cursors in general and the types of solutions I post. I'd suspect I've "busted" more cusors here than anyone -- except those crazy posters with 10,000 posts !! :)

(FYI -- i don't even have cursor syntax memorized and I've never written one in T-SQL, and definitely have never posted a solution here using one. But when i'm in VB/ASP, and I need to loop through some rows, guess what: I open a recordset and cursor-through that bad boy)

Opening a cursor, fetching 1 row at a time until you are done makes perfect sense when you need to do it, as opposed to continually querying a table and keeping a running counter of which row you are on (and if you have a composite PK, you of course need to keep several) .

Lumbago -- I salute you for "taking a chance" by questioning the use of setbased versus cursors; there is no harm in asking and there is never a 100% rule that applies in all cases.

- Jeff

Kristen
Test

22859 Posts

Posted - 2004-07-19 : 09:04:56

Has one of the FOURguysfromrolla died then?!

Kristen

Lumbago
Norsk Yak Master

3271 Posts

Posted - 2004-07-19 : 09:07:38

I think you understand me wrong here...I'm not questioning setbased here, there is no doubt whatsoever that setbased is by far the best way to go. BUT: when you come to the point when doing it row-by-row is the only acceptable (timewise) solution, would using a real cursor or faking the cursor using Kristens method be the best? Hasn't it ever ocurred that you had "no other choice" but to do something row-by-row whether you liked it or not? I see no other option as it is right now...

derrickleggett
Pointy Haired Yak DBA

4184 Posts

Posted - 2004-07-19 : 09:10:36

lol....threeguys sorry.

Jeff, I'm not offended. I've helped bust a couple myself. It's always good to questions the "unquestionable" anyway. I agree that most times you don't need a cursor or loop-based logic anyway. The only time I ever use a cursor is when I need the transactional consistency they can provide. At that time, it seems to be a fair tradeoff.

<i>Opening a cursor, fetching 1 row at a time until you are done makes perfect sense when you need to do it, as opposed to continually querying a table and keeping a running counter of which row you are on (and if you have a composite PK, you of course need to keep several) . </i>

I disagree with this part. You're not continually querying a table. You just load it into a table variable with an identity column and loop from min to max. There's no continual querying involved.

MeanOldDBA
derrickleggett@hotmail.com

When life gives you a lemon, fire the DBA.

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2004-07-19 : 09:21:44

The main danger with the statements I've been making is this scenerio:

You have a stored procedure that does several things to several tables, and takes a bunch of arguments. You need to run this procedure once for each row in a certain table. Therefore, based on what I've said, you can probably just cursor through your table of the arguments, and for each row call the stored procedure, passing them in.

Sounds good, but often the solution is to rewrite your stored procedure so that it argument(s) it accepts dictate which rows from the argument table to us; and then that procedure joins to that table directly and then does everything at once. Not always possible, but probably 75% of the time it is.

so if you need to say:


for each row in (SELECT * FROM Table WHERE Cust = '123')
   set @arg1, @arg2, @arg3, ...
   exec DoSomething @Arg1, @Arg2, @Arg3, ...
next

A better what might be to re-write "DoSomething" to accept 1 argument -- the "Customer ID" -- and to do everything at once in a set-based manner for that customer, instead of having to call it over and over for each row in a table:


exec DoSomething '123'

Certainly calling it once and doing everything in a few set-based steps would be more efficient that calling it 100 times.

I probably just confused everyone even more with that example, but hopefully it made some sense ... the point is: even in cases where you THINK you need a cursor, you might not. But every now and then, you do need one, and don't be afraid to use it if it makes your code more readable and has no performance penality.

Derrick -- (sorry I spelled your name wrong a few times!!), you are loading stuff into a table variable, querying that table variable, keeping tack of where you were last, doing something, and then querying that table variable over and over. I'm not sure if that's any more or less efficient than using a cursor, but I personally feel it's a little more "round about" of a solution so therefore to me I usually don't prefer it; just a preference.

- Jeff

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources