Avoiding Cursors

Question

Avoiding Cursors

Steve Jones - SSC Editor

SSC Guru

Points: 736270
More actions
February 28, 2014 at 9:04 am

#290367

After seeing this: http://www.sqlservercentral.com/Forums/Topic1546354-391-1.aspx
I'd like to see some articles that look at real situations where you've replaced a cursor (or While loop) with non RBAR based code.

Viewing 15 posts - 1 through 15 (of 25 total)

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

Sean Lange SSC Guru Points: 286590 More actions · Answer 1

I was thinking the same thing Steve. Not sure I can commit to this one at the moment since I still owe you an article on another topic.

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

Steve Jones - SSC Editor SSC Guru Points: 736270 More actions · Answer 2

Don't commit, but if you think of ideas, make notes, or post them here. It would be good to have a nice set of 5-10 situations where cursors are replaced.

WayneS SSC Guru Points: 95460 More actions · Answer 3

Suggestions:

1. Include running totals utilizing the SQL 2012+ enhancements to the OVER clause (yes, the QU is still faster... but this is documented in BOL, and still beats everything else, including a cursor).

2. Include the ROW_NUMBER trick of having two ROW_NUMBER calculations (differing by the PARTITION BY clause) and subtracting these from each other to assign the same value to sequential rows.

If necessary, I can provide samples for both of these.

Also - check out the article from R. Barry Young from a few years ago on re-writing cursor code to be set-based.

If some other examples are provided, I could take this one on.

Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes

If you can't explain to another person how the code that you're copying from the internet works, then DON'T USE IT on a production system! After all, you will be the one supporting it!

Links:
For better assistance in answering your questions
Performance Problems
Common date/time routines
Understanding and Using APPLY Part 1 & Part 2

Chris Wooding SSCarpal Tunnel Points: 4556 More actions · Answer 4

We need to copy some columns for new and amended clients from an in-house database to a 3rd-party one. The original process involved copying the data to an intermediate database (with some massaging) and then processing new clients via a cursor before using a similar cursor to process the changes. Unsurprisingly, this took some time. The replacement does away with the intermediate database and doesn't use cursors.

Old and new stored procedures in the attached document.

Cody Konior SSCarpal Tunnel Points: 4827 More actions · Answer 5

I've seen tables internationalised with:

- Rows that contain keys where text should be

- A key table

- A translation table which has the key, a language, and the text

Inserts to those tables are often done within a cursor. Why? Because first keys need to be reserved in the key table, then the rows inserted into the main table with those identity values, and finally the translation table filled out.

I replaced those cursors with three inserts; one into the key table but with an OUTPUT clause so I could retain all of the identities now reserved, then one set of inserts into the main table joined to the key table, and finally a set of inserts into the translation table.

This was the most common requirement.

Steve Jones - SSC Editor SSC Guru Points: 736270 More actions · Answer 6

Thanks for the suggestions and notes. Still looking for some pieces that will showcase to people how and why the code is better than a cursor.

One piece per example.

Ed Pollack Hall of Fame Points: 3199 More actions · Answer 7

Steve,

I'm a huge fan of cursor-killing and would be happy to take this one. I can address the link you posted as well as some more general problems as well.

-Ed

Steve Jones - SSC Editor SSC Guru Points: 736270 More actions · Answer 8

Ed, you can tackle one or two of the items. We are really looking for focus here on solving a problem, not a general piece that tries to cover too much.

Ed Pollack Hall of Fame Points: 3199 More actions · Answer 9

No prob,

I'll focus briefly on the problems that cursors pose, and present 2 use cases for them---one being a CTE-based solution to the link provided in this post, and another for getting back identities after an insert/update. That's a fairly common use case---especially for DBAs that are maintaining older code with less of the newer features from 2008/2012.

This could easily turn into a series of articles, as avoiding cursors is directly tied to the topic of using set-based SQL vs. iterative SQL. There could easily be a dozen topics here, or more! Your call on how far you'd like this to go.

Thanks,

-Ed

The Dixie Flatline SSC Guru Points: 53354 More actions · Answer 10

I agree this is best as a series of small articles.

Some of my favorite techniques:

1) Use a tally table and SUBSTRING() to represent character string as a table of individual characters. Great for filtering out unwanted characters.

2) Finding gaps/islands in series of numbers.

3) Determining whether two strings are alike except for a typo (1 character different, omitted, added or transposed).

I'll volunteer to do any of the above.

__________________________________________________

Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills

wbellman SSC Enthusiast Points: 163 More actions · Answer 11

There's also a "background" element to consider. I tend to see cursors crop up when it's language programmers (C#,VB,etc) trying to think in SQL. They tend to think imperatively because that's how they've been trained. However SQL tends to be functional in philosophy.

Perhaps a bit into the theory behind *why* cursors are bad would be helpful. The biggest hurdles I tend to run into are not the what to do instead of cursors, it's the breaking devs of the comfortable imperative practices. Once they understand they are trying to fit square pegs in round holes the discussions tend to go a little smoother.

So... an article like "Transformative Thinking", perhaps?

Ed Pollack Hall of Fame Points: 3199 More actions · Answer 12

wbellman (5/22/2014)
There's also a "background" element to consider. I tend to see cursors crop up when it's language programmers (C#,VB,etc) trying to think in SQL. They tend to think imperatively because that's how they've been trained. However SQL tends to be functional in philosophy.
Perhaps a bit into the theory behind *why* cursors are bad would be helpful. The biggest hurdles I tend to run into is not the what to do instead of cursors, it's the breaking devs of the comfortable imperative practices. Once they understand they are trying to fit square pegs in round holes the discussions tend to go a little smoother.
So... an article like "Transformative Thinking", perhaps?

hmmmm...What do you think Steve? I've put in a few cursor-killing articles, but they were very focused on specific scenarios. There is a treasure-trove of cursor-related articles on the site, but I couldn't find one (in my brief searching) that was theoretical---all took specific problems & tackled them with little background as to why a cursor is bad.

Steve Jones - SSC Editor SSC Guru Points: 736270 More actions · Answer 13

Ed Pollack (5/22/2014)
wbellman (5/22/2014)
There's also a "background" element to consider. I tend to see cursors crop up when it's language programmers (C#,VB,etc) trying to think in SQL. They tend to think imperatively because that's how they've been trained. However SQL tends to be functional in philosophy.
Perhaps a bit into the theory behind *why* cursors are bad would be helpful. The biggest hurdles I tend to run into is not the what to do instead of cursors, it's the breaking devs of the comfortable imperative practices. Once they understand they are trying to fit square pegs in round holes the discussions tend to go a little smoother.
So... an article like "Transformative Thinking", perhaps?
hmmmm...What do you think Steve? I've put in a few cursor-killing articles, but they were very focused on specific scenarios. There is a treasure-trove of cursor-related articles on the site, but I couldn't find one (in my brief searching) that was theoretical---all took specific problems & tackled them with little background as to why a cursor is bad.

I want specifics. Many readers will learn better when they find specific instances of trying to accomplish xx, and there's an article that shows how to avoid the common "I'll use a cursor here" solution.

wbellman SSC Enthusiast Points: 163 More actions · Answer 14

So... something more like this. Here's a real world example from my job.

Goal: To score a set of companies based on set business rules that vary from platform to platform. The score determines priority for a selection process. Each platform has a different set of rules.

The original solution:

1. Open cursor

2. Determine platform

3. Long if block to determine score based on company information

4. Write result

5. Rinse repeat

I could go over the what we used to break apart the cursor... Actually for brevity sake I simplified the "Original solution." The actual original solution was a proc that had a cursor that called itself. We called it the recursive cursor. It was lots of fun.