Hidden RBAR: Counting with Recursive CTE's

Question

Hidden RBAR: Counting with Recursive CTE's

Viewing 15 posts - 46 through 60 (of 73 total)

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

Jeff Moden SSC Guru Points: 1004339 More actions · Answer 1

Mattrick (8/9/2011)
Jeff,
Thanks for the article and the performance metrics. After having read it, I will be sending a link to all of the developers in my department as a means to more succinctly explain why I have advocated avoiding recursive CTEs used for this purpose.
Thanks again,
Matt

You a Man after my own heart, Matt! 🙂 rCTE's that count (and some that do other things) look like some pretty clever code and people just don't understand what goes on under the hood of the blasted things. Glad you're spreading the word and thanks for the feedback.

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

Jeff Moden SSC Guru Points: 1004339 More actions · Answer 2

Dugi (8/9/2011)
Jeff,
Thanks for the very nice stuff! Simple to say that I need to read it again and doing more and more practicing and practicing ...!
Have a nice day,
Dugi

Hi Dugi,

Long time no see. Thanks for stopping by. I've been pretty busy and haven't had a chance to look in at your new users group. How's that going so far?

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

Kenney Hill SSC Eights! Points: 923 More actions · Answer 3

Excellent article, as always, Jeff! And as all of your articles that I mange to see and read, this one has been added to my briefcase. I only wish I had the opportunity to work with you for even a few months, my thought processes and SQL code would be so much better for it!

Thank you for the time and effort you put into these types of things (articles, forums, etc.).

Dugi SSCoach Points: 17998 More actions · Answer 4

Jeff Moden (8/9/2011)
Dugi (8/9/2011)
Jeff,
Thanks for the very nice stuff! Simple to say that I need to read it again and doing more and more practicing and practicing ...!
Have a nice day,
Dugi
Hi Dugi,
Long time no see. Thanks for stopping by. I've been pretty busy and haven't had a chance to look in at your new users group. How's that going so far?

I'm busy little bit while I'm trying to organize the first meeting in the September, so things are going good, but still more efforts to make things properly! Thanks for asking we will be in touch for the progress...!

Have a nice day!

😉

============================================================
SELECT YOUR PROBLEM FROM SSC.com WHERE PROBLEM DESCRIPTION =
http://www.sqlservercentral.com/articles/Best+Practices/61537/[/url]

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 5

Jeff Moden (8/9/2011)
ChrisM@Work (8/9/2011)
Crackin' good read as always Jeff.
Thanks, Chris. Always good to hear from you.
I never noticed it before. Your signature line says you're a defender of moggies. We have 7 that we rescued from the streets. At one time, we had 15 of them, 4 dogs, and a rabbit and that was a little much. Even the dust bunnies got uncomfortable.
Nah... didn't get rid of any of them. They all died of old age. I think we've given good lives to something like 40 or 50 of them.

Father Christmas and the petting farm 🙂 I've rescued one or two in my time. Seven places yourself and your missus somewhere between charitable and saintly!

On first reading your article I was a little concerned that folks might write off rCTE's altogether, despite your very clear wording. However, it does appear that folks have picked up clearly that rCTE's are a profoundly poor choice for counting, not necessarily for other tasks.

Incidentally, did you notice during your tests how rubbish the stats are for rCTE's?

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

Ken SSCoach Points: 17191 More actions · Answer 6

Jeff,

Where would I vote for you on the PASS site or is that already gone and past?

Kenny

John Dempsey Hall of Fame Points: 3225 More actions · Answer 7

Kenneth Wymore (8/10/2011)
Jeff,
Where would I vote for you on the PASS site or is that already gone and past?
Kenny

For those interested, Jeff is currently nominated for the award of Exceptional DBA and here is the link to vote: RedGate Exceptional DBA voting link[/url]

Ken SSCoach Points: 17191 More actions · Answer 8

John Dempsey (8/10/2011)
Kenneth Wymore (8/10/2011)
Jeff,
Where would I vote for you on the PASS site or is that already gone and past?
Kenny
For those interested, Jeff is currently nominated for the award of Exceptional DBA and here is the link to vote: RedGate Exceptional DBA voting link[/url]

Thanks John!

Jeff Moden SSC Guru Points: 1004339 More actions · Answer 9

Kenney Hill (8/10/2011)
Excellent article, as always, Jeff! And as all of your articles that I mange to see and read, this one has been added to my briefcase. I only wish I had the opportunity to work with you for even a few months, my thought processes and SQL code would be so much better for it!
Thank you for the time and effort you put into these types of things (articles, forums, etc.).

Gosh, Kenney... that's one heck of a nice compliment. Thank you, Sir.

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

lstrashnoy Valued Member Points: 61 More actions · Answer 10

Thanks Jeff.

I actually agree. I used this code in mySQL and it works pretty efficient. Unfortunately SQL Server not optimizing the way mySQL does. And because it has to do sorting as the last step it kills performance 🙁

In my mySQL environment I have numbers in millions and it does everything in memory without reads 🙂

There is no analytical functions yet in mySQL anyways...

Thanks,

-Leonard

Jon Russell Hall of Fame Points: 3048 More actions · Answer 11

Jon Russell

Hall of Fame

Points: 3048

August 10, 2011 at 12:08 pm

#1366798

As usual, excellent! Thanks, Jeff.

Paulo Valued Member Points: 71 More actions · Answer 12

As usual a good article Moden.

A minor correction if you don't mind. If you are going to quote my 'Really?!", I think it needs both a question mark & an exclamation point. That way it reads like I say it ... a touch of amusement & a ton of WTF. 🙂

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
"Those who can make you believe absurdities,
can make you commit atrocities." ~Voltaire
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

Jeff Moden SSC Guru Points: 1004339 More actions · Answer 13

Paul Herbert (8/10/2011)
As usual a good article Moden.
A minor correction if you don't mind. If you are going to quote my 'Really?!", I think it needs both a question mark & an exclamation point. That way it reads like I say it ... a touch of amusement & a ton of WTF. 🙂

Everyone... please meet and greet Mr. Paul Herbert... the Systems DBA where I work at. We've slain many dragons together in the short year or so that we've had the opportunity to work with each other. 🙂 I've been trying to convince him to write an article about some of the things he's done on the Administrative side of the world. Maybe you good folks can help me convince him. 😉

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

Jeff Moden SSC Guru Points: 1004339 More actions · Answer 14

lstrashnoy (8/10/2011)
Thanks Jeff.
I actually agree. I used this code in mySQL and it works pretty efficient. Unfortunately SQL Server not optimizing the way mySQL does. And because it has to do sorting as the last step it kills performance 🙁
In my mySQL environment I have numbers in millions and it does everything in memory without reads 🙂
There is no analytical functions yet in mySQL anyways...
Thanks,
-Leonard

IIRC, doesn't mySQL have a builtin function that will generate numerical sequences?

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

Jeff Moden SSC Guru Points: 1004339 More actions · Answer 15

Jon Russell (8/10/2011)
As usual, excellent! Thanks, Jeff.

You bet, Jon. Thanks for stopping by and for the feedback.

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