The Cascading (CROSS) APPLY

Question

Post reply

The Cascading (CROSS) APPLY

ChrisM@home

SSC-Insane

Points: 24260
More actions
April 15, 2013 at 9:58 pm

#276500

Comments posted to this topic are about the item The Cascading (CROSS) APPLY
[font="Arial"]^{Low-hanging fruit picker and defender of the moggies}[/font]
For better assistance in answering your questions, please read this[/url].
Understanding and using APPLY, (I)[/url] and (II)[/url] Paul White[/url]
Hidden RBAR: Triangular Joins[/url] / The "Numbers" or "Tally" Table: What it is and how it replaces a loop[/url] Jeff Moden[/url]

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

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

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

Awesome job, Chris. Well done!

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

Tim Walker. SSCertifiable Points: 5173 More actions · Answer 2

Interesting, entertaining and educational with worked examples and performance statistics make this a great read.

The fact that it is also genuinely very useful and will help me to accurately solve a problem I have right now makes it invaluable.

Great job Chris. Thanks for sharing.

.

balde SSCommitted Points: 1594 More actions · Answer 3

Ever since discovering a use for APPLY for the first time approximately 2 years ago, I've found I use it almost everywhere these days.

To the extent where I start looking for candidates for re-writing using APPLY! --> thankfully I'm about to move onto pastures new so the 7 years worth of stored procedures and queries I have here will no longer be candidates for such an exercise!

APPLY truly is a remarkable technique and this article adds another strong argument to the reasons why you should have this in your arsenal - if you don't yet feel confident working with APPLY work through the examples here and you'll see how powerful it is.

thanks for another great article and keep up the good work

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

Jeff Moden (4/16/2013)
Awesome job, Chris. Well done!

Thanks Jeff! And thank you for the encouragement to put pen to paper. DC's been nagging me for ages. Once you stepped in there was no getting out of it 😀

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

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

Tim Walker. (4/16/2013)
Interesting, entertaining and educational with worked examples and performance statistics make this a great read.
The fact that it is also genuinely very useful and will help me to accurately solve a problem I have right now makes it invaluable.
Great job Chris. Thanks for sharing.

Thank you very much, Tim. Almost every day, something comes up on ssc which requires a cCA or some other manipulation of APPLY. I don't know what we did without it.

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

ChrisM@home SSC-Insane Points: 24260 More actions · Answer 6

balde (4/16/2013)
Ever since discovering a use for APPLY for the first time approximately 2 years ago, I've found I use it almost everywhere these days.
To the extent where I start looking for candidates for re-writing using APPLY! --> thankfully I'm about to move onto pastures new so the 7 years worth of stored procedures and queries I have here will no longer be candidates for such an exercise!
APPLY truly is a remarkable technique and this article adds another strong argument to the reasons why you should have this in your arsenal - if you don't yet feel confident working with APPLY work through the examples here and you'll see how powerful it is.
thanks for another great article and keep up the good work

Thank you balde, and best wishes with your new venture.

[font="Arial"]^{Low-hanging fruit picker and defender of the moggies}[/font]

For better assistance in answering your questions, please read this[/url].

Understanding and using APPLY, (I)[/url] and (II)[/url] Paul White[/url]

Hidden RBAR: Triangular Joins[/url] / The "Numbers" or "Tally" Table: What it is and how it replaces a loop[/url] Jeff Moden[/url]

Gary Nease SSC-Addicted Points: 424 More actions · Answer 7

In the initial CREATE TABLE example, [RowNum] should be [EmployeeId].

Thanks for the great article.

Regards,

Gary

ChrisM@home SSC-Insane Points: 24260 More actions · Answer 8

Gary Nease (4/16/2013)
In the initial CREATE TABLE example, [RowNum] should be [EmployeeId].
Thanks for the great article.

Thank you very much Gary - duly updated.

[font="Arial"]^{Low-hanging fruit picker and defender of the moggies}[/font]

For better assistance in answering your questions, please read this[/url].

Understanding and using APPLY, (I)[/url] and (II)[/url] Paul White[/url]

Hidden RBAR: Triangular Joins[/url] / The "Numbers" or "Tally" Table: What it is and how it replaces a loop[/url] Jeff Moden[/url]

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

ChrisM@Work (4/16/2013)
Tim Walker. (4/16/2013)
Interesting, entertaining and educational with worked examples and performance statistics make this a great read.
The fact that it is also genuinely very useful and will help me to accurately solve a problem I have right now makes it invaluable.
Great job Chris. Thanks for sharing.
Thank you very much, Tim. Almost every day, something comes up on ssc which requires a cCA or some other manipulation of APPLY. I don't know what we did without it.

For single row things, we use to use "Correlated Sub-Queries" in the SELECT list. Of course, those didn't have the flexibility of returning multiple rows like Cross Apply does.

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

ChrisM@Work (4/16/2013)
Jeff Moden (4/16/2013)
Awesome job, Chris. Well done!
Thanks Jeff! And thank you for the encouragement to put pen to paper. DC's been nagging me for ages. Once you stepped in there was no getting out of it 😀

Heh... I just knew what you were capable of but, ultimately, my opinion doesn't matter. The proof of what you're capable of is now officially documented. More than 7K reads in the first 2 days and 5 stars on the very first try. I say again, well done, ol' friend!

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

Dwain Camps SSC Guru Points: 86948 More actions · Answer 11

Jeff Moden (4/16/2013)
ChrisM@Work (4/16/2013)
Jeff Moden (4/16/2013)
Awesome job, Chris. Well done!
Thanks Jeff! And thank you for the encouragement to put pen to paper. DC's been nagging me for ages. Once you stepped in there was no getting out of it 😀
Heh... I just knew what you were capable of but, ultimately, my opinion doesn't matter. The proof of what you're capable of is now officially documented. More than 7K reads in the first 2 days and 5 stars on the very first try. I say again, well done, ol' friend!

I concur on Jeff's congratulations and on a fine article. Yours is a talent that should be shared so I'm looking forward to egging you on to even more... 🙂

My mantra: No loops! No CURSORs! No RBAR! Hoo-uh![/I]
My thought question: Have you ever been told that your query runs too fast?

My advice:
INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.

Need to UNPIVOT? Why not CROSS APPLY VALUES instead?[/url]
Since random numbers are too important to be left to chance, let's generate some![/url]
Learn to understand recursive CTEs by example.[/url]
[url url=http://www.sqlservercentral.com/articles/St

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

You're not doing so bad, yourself, Dwain. Ten mostly 5 star articles in a year is freakin' awesome!

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

Dwain Camps SSC Guru Points: 86948 More actions · Answer 13

Jeff Moden (4/17/2013)
You're not doing so bad, yourself, Dwain. Ten mostly 5 star articles in a year is freakin' awesome!

Thanks Jeff.

Actually my latest only got 4 stars but on the other hand it passed 10K views over the weekend and is now my #1 most read article! So I'm a bit happy about that.

Now if I could just get my current effort out of its stall.

My mantra: No loops! No CURSORs! No RBAR! Hoo-uh![/I]
My thought question: Have you ever been told that your query runs too fast?

My advice:
INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.

Need to UNPIVOT? Why not CROSS APPLY VALUES instead?[/url]
Since random numbers are too important to be left to chance, let's generate some![/url]
Learn to understand recursive CTEs by example.[/url]
[url url=http://www.sqlservercentral.com/articles/St

Divya Agrawal Hall of Fame Points: 3144 More actions · Answer 14

Great post!! thanks for sharing..it is really very useful

--Divya