Hidden RBAR: Counting with Recursive CTE's

Question

Hidden RBAR: Counting with Recursive CTE's

Viewing 15 posts - 31 through 45 (of 73 total)

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

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

John Dempsey (8/8/2011)
Great article Jeff with great explanations & examples.

Long time no see, John. Thanks for the feedback. Heh... I'd forgotten about the "Pork Chop Logo". I wonder how T-Shirts with it would sell at PASS. 😛

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

majorbloodnock SSCrazy Eights Points: 9389 More actions · Answer 2

Andy DBA (8/8/2011)
majorbloodnock (8/8/2011) ... No-one with any taste buds would take a perfectly acceptable alcoholic drink and cool it so much that it freezes...
A perfectly acceptable alcoholic drink? Have you ever tasted Canadian "beer"? I'm guessing the whole idea behind the popsicles is to numb the taste buds...

He, he. Yes I have, and that brings me back to point one of my original post. It isn't really beer, except, perhaps, after someone's processed it. 😉

Sorry, don't mean to pick on Canadian beer. Most American "beer"s aren't palatable either unless they're too cold to taste. That's why we have a brand where graphics on the can change color when it's cold enough for consumption!

To be honest, it isn't just an issue on the North American continent. Fosters, for instance, may be a different brand, but it's still basically the same gassy chemical brew produced over here.

Semper in excretia, suus solum profundum variat

Dugi SSCoach Points: 17998 More actions · Answer 3

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

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

Mattrick Ten Centuries Points: 1176 More actions · Answer 4

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

Revenant SSC-Forever Points: 42467 More actions · Answer 5

Jeff

I have printed this article and put it into my 'keep for frequent reference' folder.

Much thanks!

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

Crackin' good read as always Jeff.

^{“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

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

Long time no see, John. Thanks for the feedback. Heh... I'd forgotten about the "Pork Chop Logo". I wonder how T-Shirts with it would sell at PASS. 😛

Yeah, it has been tough getting time on the forums :(. Makes me sad, but I was definitely looking forward to when this article was coming out.

I might have to get a prototype t-shirt going and submit it to Steve Jones for approval. I will be at PASS this year 😉 and hopefully meeting you there after you win, so it could work.

tfifield SSCrazy Eights Points: 9655 More actions · Answer 8

Excellent article Jeff!

It really puts the kabosh on the rCTE method. It also shows something that I found a while back. That the old fashioned tally table in some cases can slightly out perform the Itzik style cross join.

Todd Fifield

lstrashnoy Valued Member Points: 61 More actions · Answer 9

I believe that if you define E1 in Itzik stile join as:

E1(N) AS (

SELECT 0 UNION SELECT 1 UNION SELECT 0 UNION

SELECT 3 UNION SELECT 4 UNION SELECT 5 UNION

SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9

),

then you don't need an analytic function.

That will make your code little cleaner and may be faster.

I've been using this technique for years...

Thanks,

-Leonard

lstrashnoy Valued Member Points: 61 More actions · Answer 10

Of cause your calculations little different:

WITH --===== Itzik-Style CROSS JOIN counts from 1 to the number of days needed

E1(N) AS (

SELECT 0 UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL

SELECT 3 UNION ALL SELECT 4 UNION ALL SELECT 5 UNION ALL

SELECT 6 UNION ALL SELECT 7 UNION ALL SELECT 8 UNION ALL SELECT 9

),

E2(N) AS (SELECT 1000*thousands.N + 100*hunderds.N + 10*tens.N + ones.N

FROM E1 thousands, E1 hunderds, E1 tens, E1 ones),

cteTally(N) AS (SELECT TOP 4000 N FROM E2 order by N)

SELECT *

FROM cteTally

- Leonard

Jeff Moden SSC Guru Points: 1003851 More actions · Answer 11

lstrashnoy (8/9/2011)
Of cause your calculations little different:
WITH --===== Itzik-Style CROSS JOIN counts from 1 to the number of days needed
E1(N) AS (
SELECT 0 UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL
SELECT 3 UNION ALL SELECT 4 UNION ALL SELECT 5 UNION ALL
SELECT 6 UNION ALL SELECT 7 UNION ALL SELECT 8 UNION ALL SELECT 9
),
E2(N) AS (SELECT 1000*thousands.N + 100*hunderds.N + 10*tens.N + ones.N
FROM E1 thousands, E1 hunderds, E1 tens, E1 ones),
cteTally(N) AS (SELECT TOP 4000 N FROM E2 order by N)
SELECT *
FROM cteTally
- Leonard

Hi Leonard,

Thanks for the feedback and the code.

You might want to reconsider and use something else, though. On my machine, your code does pretty well at a count of 8k... between 17 and 23 milliseconds (heh... yeah... old machine but I still love it) which is still slower than all the other methods except the rCTE but still pretty fast. The problem is that it also takes almost that long to generate a count of 1. This is because the code generates all 10000 numbers no matter what the requested number is. Take a look at the Execution Plan and then run it in the test code that I attached at the bottom of the article to see what I mean.

--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: 1003851 More actions · Answer 12

tfifield (8/9/2011)
Excellent article Jeff!
It really puts the kabosh on the rCTE method. It also shows something that I found a while back. That the old fashioned tally table in some cases can slightly out perform the Itzik style cross join.
Todd Fifield

Hi Todd,

Thanks for stopping by.

Gosh... I can only hope that the article puts the kabosh on counting with rCTE's in people's minds because, like I said in the article, it's becoming an almost viral problem on posts on many forums. I suspect the "draw" to using the rCTE is that it looks setbased, is easy to write (although not nearly as easy as the two Cross-Joined table methods), and it shows that the person at least knows about a relatively advanced feature. It just doesn't work well for counting problems and writing this article is just my first step to trying to get the word out. I'll also take any help I can get with folks spreading the word.

And, I absolutely agree... the Tally Table is right up there with the big boys and it's a whole lot easier to write code with than a lot of the other methods.

Again, thanks for stopping by, Todd. Always a pleasure.

--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: 1003851 More actions · Answer 13

John Dempsey (8/9/2011)
Long time no see, John. Thanks for the feedback. Heh... I'd forgotten about the "Pork Chop Logo". I wonder how T-Shirts with it would sell at PASS. 😛
Yeah, it has been tough getting time on the forums :(. Makes me sad, but I was definitely looking forward to when this article was coming out.
I might have to get a prototype t-shirt going and submit it to Steve Jones for approval. I will be at PASS this year 😉 and hopefully meeting you there after you win, so it could work.

I can only hope to win. There's 4 other good folks I'm competing against. If you'll allow me a self indulgent minute, help spread the word... I can use all the votes I can get. 🙂

--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: 1003851 More actions · Answer 14

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.

--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: 1003851 More actions · Answer 15

Revenant (8/9/2011)
Jeff
I have printed this article and put it into my 'keep for frequent reference' folder.
Much thanks!

You bet, Revenant. Thank you for the read and the feedback. 🙂 A person just can't ask for a better compliment than to make it to someone's FAQ folder. Thanks.

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