Understanding and Using APPLY (Part 1)

Question

Post reply

Understanding and Using APPLY (Part 1)

Paul White

SSC Guru

Points: 150468
More actions
April 10, 2010 at 12:03 pm

#223144

Comments posted to this topic are about the item Understanding and Using APPLY (Part 1)
Paul White
All articles available on SQL.kiwi
@SQL_Kiwi

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

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

Paul White SSC Guru Points: 150468 More actions · Answer 1

I would like to express my sincere thanks to the following people, for their help in producing this article:

Amanda Lawrence

Jeff Moden MVP

Chris Morris

Lutz Müller

Jason Brimhall[/url]

Thank you everyone!

Paul

Paul White
All articles available on SQL.kiwi
@SQL_Kiwi

Matt Whitfield SSCrazy Eights Points: 8107 More actions · Answer 2

Great article Paul - look forward to Part 2. 😀

Atlantis Interactive - SQL Server Tools
My blog[/url]
Why I wrote a sql query analyzer clone

Christian Buettner-167247 SSChampion Points: 13729 More actions · Answer 3

Thank you, Paul. This is great work. Not only the code & explanation, but equally important: the design of the article. Very easy to read with good illustrations & examples. More of that please! This is the meat of SQL Server Central!

Best Regards,

Chris Büttner

mohd.nizamuddin Ten Centuries Points: 1328 More actions · Answer 4

Nice Article Paul. It is very well expalined with example.

I have a doubt, since the UDTF would get executed as many times as the row count of primary resultset (Student's Count). So, would it not affect the performamance. Because this same can be achived by a simple join and that get executed in a set. (I know, you have used this example for illustration purpose. :-)).

For maintenability and readibility, this concept is fine enough.

Well, Please suggest that which is better approach in terms of performance:

Use of UDTF with CROSS APPLY (to a certain extent, which cannot be achived by simple SQL)

or

Putting all tables / view in the final SQL

Paul White SSC Guru Points: 150468 More actions · Answer 5

mohd.nizamuddin (4/12/2010)
Nice Article Paul. It is very well expalined with example.
I have a doubt, since the UDTF would get executed as many times as the row count of primary resultset (Student's Count). So, would it not affect the performamance. Because this same can be achived by a simple join and that get executed in a set. (I know, you have used this example for illustration purpose. :-)).
For maintenability and readibility, this concept is fine enough.
Well, Please suggest that which is better approach in terms of performance:
Use of UDTF with CROSS APPLY (to a certain extent, which cannot be achived by simple SQL)
or Putting all tables / view in the final SQL

Thanks - you raise some interesting questions, which will be fully addressed in part 2, next week 😎

Paul White
All articles available on SQL.kiwi
@SQL_Kiwi

mohd.nizamuddin Ten Centuries Points: 1328 More actions · Answer 6

Paul White NZ (4/12/2010)
mohd.nizamuddin (4/12/2010)
Nice Article Paul. It is very well expalined with example.
I have a doubt, since the UDTF would get executed as many times as the row count of primary resultset (Student's Count). So, would it not affect the performamance. Because this same can be achived by a simple join and that get executed in a set. (I know, you have used this example for illustration purpose. :-)).
For maintenability and readibility, this concept is fine enough.
Well, Please suggest that which is better approach in terms of performance:
Use of UDTF with CROSS APPLY (to a certain extent, which cannot be achived by simple SQL)
or Putting all tables / view in the final SQL
Thanks - you raise some interesting questions, which will be fully addressed in part 2, next week 😎

That would be great. Highly appriciated your quick response.

Tom Garth SSCertifiable Points: 6193 More actions · Answer 7

Thanks Paul. Nice work, and I'm looking forward to part 2.

Tom Garth
Vertical Solutions[/url]

"There are three kinds of men. The one that learns by reading. The few who learn by observation. The rest of them have to pee on the electric fence for themselves." -- Will Rogers

Jack Corbett SSC Guru Points: 184394 More actions · Answer 8

Good post Paul, as expected. Clear, concise, and good examples. Looking forward to part 2.

Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question

WayneS SSC Guru Points: 95460 More actions · Answer 9

Paul,

Most excellent! This has been bookmarked, and I'm also looking forward to Part II

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: 736479 More actions · Answer 10

Excellent article, and definitely I learned a few things from this one.

Luke L SSC-Dedicated Points: 35547 More actions · Answer 11

As the others have said, Good Work Paul, looking forward to part 2.

-Luke.

To help us help you read this[/url]For better help with performance problems please read this[/url]

Paul White SSC Guru Points: 150468 More actions · Answer 12

Thank you all for the kind reviews (so far): Matt, Christian, Tom, Jack, Wayne, Steve, and Luke. I really appreciate it. :Wow:

Paul White
All articles available on SQL.kiwi
@SQL_Kiwi

Arjun Sivadasan SSCertifiable Points: 5902 More actions · Answer 13

Paul White NZ (4/12/2010)
mohd.nizamuddin (4/12/2010)
Nice Article Paul. It is very well expalined with example.
I have a doubt, since the UDTF would get executed as many times as the row count of primary resultset (Student's Count). So, would it not affect the performamance. Because this same can be achived by a simple join and that get executed in a set. (I know, you have used this example for illustration purpose. :-)).
For maintenability and readibility, this concept is fine enough.
Well, Please suggest that which is better approach in terms of performance:
Use of UDTF with CROSS APPLY (to a certain extent, which cannot be achived by simple SQL)
or Putting all tables / view in the final SQL
Thanks - you raise some interesting questions, which will be fully addressed in part 2, next week 😎

My questions are on the same lines. Is it always better to use APPLY than a JOIN?

However I think the 'UDTF would get executed as many times as the row count of primary resultset' and 'simple join' comments are pretty superficial. JOIN is not as simple as it looks is what execution plans tell me. Thanks to some good articles here.

Paul, my main question is why use APPLY instead of a JOIN?

I tried out Query 04, Query 05 and the following query (the one in the article).

SELECT S.student_id,S.[Name],

J.[Name],GS.grade_score

FROM dbo.GradeScore GS

JOIN dbo.Subject J

ON J.subject_id = GS.subject_id

JOIN dbo.Student S

ON S.student_id = GS.student_id

WHERE S.gender = N'M'

ORDER BY

S.student_id ASC,

J.[Name] ASC;

They all showed me the same execution plan. Am I not 'seeing' something?

PS: A little knowledge is very dangerous 🙂

I got an error (sniff sniff) when i tried a preview and had to type the whole thing again. I will do a Ctrl + A, Ctrl + C next time before I hit the button.

- arjun

https://sqlroadie.com/

Arjun Sivadasan SSCertifiable Points: 5902 More actions · Answer 14

I would like to add that I am more worried about performance than best practice and maintenance.

- arjun

https://sqlroadie.com/