Avoiding Cursors

Question

Avoiding Cursors

Viewing 11 posts - 16 through 25 (of 25 total)

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

Steve Jones - SSC Editor SSC Guru Points: 734459 More actions · Answer 1

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.

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

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 : )

wbellman SSC Enthusiast Points: 163 More actions · Answer 3

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.

Sean Lange SSC Guru Points: 286573 More actions · Answer 4

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 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/

WayneS SSC Guru Points: 95409 More actions · Answer 5

Still looking for getting-rid-of-cursor articles?

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

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

Yes, preferably changing real world cursor solutions to non-cursor code.

P Jones SSChampion Points: 12353 More actions · Answer 7

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

WayneS SSC Guru Points: 95409 More actions · Answer 8

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
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

Steve Jones - SSC Editor SSC Guru Points: 734459 More actions · Answer 9

Steve Jones - SSC Editor

SSC Guru

Points: 734459

January 22, 2015 at 11:21 am

#1772344

bump

Jeff Moden SSC Guru Points: 1003869 More actions · Answer 10

Steve Jones - SSC Editor (1/22/2015)
bump

The difficult part for me is that I've already written about most of the generic solutions that I've used in the real world and the rest are covered by NDAs and ethics. 🙂 This is a tough one.

--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)

Steve Jones - SSC Editor SSC Guru Points: 734459 More actions · Answer 11

Jeff Moden (2/15/2015)
The difficult part for me is that I've already written about most of the generic solutions that I've used in the real world and the rest are covered by NDAs and ethics. 🙂 This is a tough one.

Slacker