Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Avoiding Cursors Expand / Collapse
Author
Message
Posted Friday, February 28, 2014 9:04 AM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: Administrators
Last Login: Today @ 3:11 PM
Points: 31,368, Visits: 15,837
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.







Follow me on Twitter: @way0utwest

Forum Etiquette: How to post data/code on a forum to get the best help
Post #1546404
Posted Friday, February 28, 2014 9:48 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Yesterday @ 2:05 PM
Points: 13,327, Visits: 12,820
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 Moden's splitter.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs
Understanding and Using APPLY (Part 1)
Understanding and Using APPLY (Part 2)
Post #1546441
Posted Friday, February 28, 2014 11:16 AM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: Administrators
Last Login: Today @ 3:11 PM
Points: 31,368, Visits: 15,837
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.






Follow me on Twitter: @way0utwest

Forum Etiquette: How to post data/code on a forum to get the best help
Post #1546496
Posted Monday, March 3, 2014 10:08 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 7:58 AM
Points: 5,370, Visits: 9,010
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
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, How to ask a question, Performance Problems, Common date/time routines,
CROSS-TABS and PIVOT tables Part 1 & Part 2, Using APPLY Part 1 & Part 2, Splitting Delimited Strings
Post #1547017
Posted Tuesday, March 4, 2014 5:30 AM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Yesterday @ 8:35 AM
Points: 87, Visits: 297
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.


  Post Attachments 
RBAR Replacement.docx (24 views, 31.78 KB)
Post #1547263
Posted Sunday, April 6, 2014 8:21 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Today @ 7:58 AM
Points: 217, Visits: 891
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.

Post #1558838
Posted Tuesday, April 8, 2014 1:52 PM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: Administrators
Last Login: Today @ 3:11 PM
Points: 31,368, Visits: 15,837
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.







Follow me on Twitter: @way0utwest

Forum Etiquette: How to post data/code on a forum to get the best help
Post #1559692
Posted Thursday, April 10, 2014 6:27 AM


Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Wednesday, December 10, 2014 6:19 AM
Points: 371, Visits: 383
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
Post #1560389
Posted Friday, April 11, 2014 8:47 AM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: Administrators
Last Login: Today @ 3:11 PM
Points: 31,368, Visits: 15,837
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.






Follow me on Twitter: @way0utwest

Forum Etiquette: How to post data/code on a forum to get the best help
Post #1560977
Posted Friday, April 11, 2014 11:11 AM


Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Wednesday, December 10, 2014 6:19 AM
Points: 371, Visits: 383
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
Post #1561070
Posted Friday, April 11, 2014 4:38 PM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Sunday, August 17, 2014 3:10 PM
Points: 2,787, Visits: 6,098
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? -- Stephen Stills
Post #1561149
Posted Thursday, May 22, 2014 8:13 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Thursday, May 22, 2014 2:21 PM
Points: 3, Visits: 7
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?
Post #1573597
Posted Thursday, May 22, 2014 8:19 AM


Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Wednesday, December 10, 2014 6:19 AM
Points: 371, Visits: 383
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.
Post #1573603
Posted Thursday, May 22, 2014 11:55 AM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: Administrators
Last Login: Today @ 3:11 PM
Points: 31,368, Visits: 15,837
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.







Follow me on Twitter: @way0utwest

Forum Etiquette: How to post data/code on a forum to get the best help
Post #1573718
Posted Thursday, May 22, 2014 12:34 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Thursday, May 22, 2014 2:21 PM
Points: 3, Visits: 7
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.
Post #1573741
Posted Thursday, May 22, 2014 1:22 PM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: Administrators
Last Login: Today @ 3:11 PM
Points: 31,368, Visits: 15,837
Yes, something like that.

General articles about how to think are good, and there are plenty out there (we may have one), but too often readers go "yeah, but my problems requires a cursor" because they haven't learned how to avoid them.

Specific articles that address common situations you find are better to teach people to replace RBAR code with set based items.







Follow me on Twitter: @way0utwest

Forum Etiquette: How to post data/code on a forum to get the best help
Post #1573754
Posted Thursday, May 22, 2014 1:28 PM


Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Wednesday, December 10, 2014 6:19 AM
Points: 371, Visits: 383
wbellman (5/22/2014)
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.


If you could provide some more specifics (ie example schema, sample data, etc...), I'd be happy to put together an article with a brief cursor intro, and then some specifics to tackle your problem, along with all of the reasons why a set-based approach beats the iterative approach every time : )
Post #1573757
Posted Thursday, May 22, 2014 1:54 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Thursday, May 22, 2014 2:21 PM
Points: 3, Visits: 7
I appreciate that. I was offering to write, though. I saw the "Write for SQLServerCentral" in the newsletter yesterday so I was poking around the requested topics to see if there was any way to help. The elimination of cursors is kind of a passion for me, and thus here I am.

Back on topic, though, I solved the problem by using table valued parameters into named procs to break things into pieces and CTEs in said procs to handle the rules.
Post #1573772
Posted Thursday, May 22, 2014 2:20 PM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Yesterday @ 2:05 PM
Points: 13,327, Visits: 12,820
wbellman (5/22/2014)
The elimination of cursors is kind of a passion for me, and thus here I am.


You are going to fit in quite nicely around here at SSC. Welcome.


_______________________________________________________________

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 Moden's splitter.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs
Understanding and Using APPLY (Part 1)
Understanding and Using APPLY (Part 2)
Post #1573782
Posted Wednesday, September 17, 2014 3:09 PM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 7:58 AM
Points: 5,370, Visits: 9,010
Still looking for getting-rid-of-cursor articles?

Wayne
Microsoft Certified Master: SQL Server 2008
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, How to ask a question, Performance Problems, Common date/time routines,
CROSS-TABS and PIVOT tables Part 1 & Part 2, Using APPLY Part 1 & Part 2, Splitting Delimited Strings
Post #1614634
Posted Thursday, September 18, 2014 11:02 AM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: Administrators
Last Login: Today @ 3:11 PM
Points: 31,368, Visits: 15,837
Yes, preferably changing real world cursor solutions to non-cursor code.






Follow me on Twitter: @way0utwest

Forum Etiquette: How to post data/code on a forum to get the best help
Post #1615012
Posted Friday, September 19, 2014 1:22 AM
Mr or Mrs. 500

Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500

Group: General Forum Members
Last Login: Tuesday, December 16, 2014 1:51 AM
Points: 579, Visits: 1,255
I've rewritten some of our report writers' cursor queries lately, mainly thanks to going on Itzik Ben-Gan's Advanced T-SQL course last year and reading his Inside SQL Server 2008 T-SQL Querying book.

Using common table expressions and joining a table back to itself to get running totals has been the most common cursor replacement technique and it is covered in the book.

A very simplified version of one for deliveries in year on a purchase order is below. My original CTE query was much more complex.

with p as
(
SELECT po.PurchaseOrder
,p.TransDate
,YEAR(p.TransDate) as DeliveryYear
,ISNULL(sum(p.Quantity),0.0) as QtyDelivered
FROM dbo.PurchaseOrders
)
--OK Now calculate a running total as we go.
select p.PurchaseOrder,
p.TransDate,p.DeliveryYear,p.QtyDelivered,SUM(p1.QtyDelivered) as CumDelivered
from p join p as p1
on p.PurchaseOrder = p1.PurchaseOrder and p.DeliveryYear = p1.DeliveryYear
and p1.TransDate <= p.TransDate
group by p.PurchaseOrder,p.DeliveryYear,p.TransDate,p.QtyDelivered

order by p.PurchaseOrder,p.DeliveryYear,p.TransDate
Post #1615172
Posted Saturday, September 20, 2014 5:22 PM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 7:58 AM
Points: 5,370, Visits: 9,010
Steve Jones - SSC Editor (9/18/2014)
Yes, preferably changing real world cursor solutions to non-cursor code.


Okay, I'll take this one on then. I envision a series of articles... "Is this cursor necessary?". I'll take one cursor apart, look at the business requirements, and if it's not necessary, then re-write it into a set-based solution.

Since this will be a series, if anyone wants to through a cursor my way, it will be helpful.


Wayne
Microsoft Certified Master: SQL Server 2008
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, How to ask a question, Performance Problems, Common date/time routines,
CROSS-TABS and PIVOT tables Part 1 & Part 2, Using APPLY Part 1 & Part 2, Splitting Delimited Strings
Post #1617415
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse