SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Understanding and Using APPLY (Part 1)


Understanding and Using APPLY (Part 1)

Author
Message
Steve Jones
Steve Jones
SSC Guru
SSC Guru (81K reputation)SSC Guru (81K reputation)SSC Guru (81K reputation)SSC Guru (81K reputation)SSC Guru (81K reputation)SSC Guru (81K reputation)SSC Guru (81K reputation)SSC Guru (81K reputation)

Group: Administrators
Points: 81405 Visits: 19207
Excellent article, and definitely I learned a few things from this one.

Follow me on Twitter: @way0utwest
Forum Etiquette: How to post data/code on a forum to get the best help
My Blog: www.voiceofthedba.com
Luke L
Luke L
SSCarpal Tunnel
SSCarpal Tunnel (4.8K reputation)SSCarpal Tunnel (4.8K reputation)SSCarpal Tunnel (4.8K reputation)SSCarpal Tunnel (4.8K reputation)SSCarpal Tunnel (4.8K reputation)SSCarpal Tunnel (4.8K reputation)SSCarpal Tunnel (4.8K reputation)SSCarpal Tunnel (4.8K reputation)

Group: General Forum Members
Points: 4803 Visits: 6135
As the others have said, Good Work Paul, looking forward to part 2.

-Luke.

To help us help you read this

For better help with performance problems please read this
Paul White
Paul White
SSCoach
SSCoach (19K reputation)SSCoach (19K reputation)SSCoach (19K reputation)SSCoach (19K reputation)SSCoach (19K reputation)SSCoach (19K reputation)SSCoach (19K reputation)SSCoach (19K reputation)

Group: General Forum Members
Points: 19980 Visits: 11359
Thank you all for the kind reviews (so far): Matt, Christian, Tom, Jack, Wayne, Steve, and Luke. I really appreciate it. Wow



Paul White
SQLPerformance.com
SQLblog.com
@SQL_Kiwi
Arjun Sivadasan
Arjun Sivadasan
Right there with Babe
Right there with Babe (774 reputation)Right there with Babe (774 reputation)Right there with Babe (774 reputation)Right there with Babe (774 reputation)Right there with Babe (774 reputation)Right there with Babe (774 reputation)Right there with Babe (774 reputation)Right there with Babe (774 reputation)

Group: General Forum Members
Points: 774 Visits: 976
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 Cool


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
Arjun Sivadasan
Arjun Sivadasan
Right there with Babe
Right there with Babe (774 reputation)Right there with Babe (774 reputation)Right there with Babe (774 reputation)Right there with Babe (774 reputation)Right there with Babe (774 reputation)Right there with Babe (774 reputation)Right there with Babe (774 reputation)Right there with Babe (774 reputation)

Group: General Forum Members
Points: 774 Visits: 976
I would like to add that I am more worried about performance than best practice and maintenance.

- arjun
Paul White
Paul White
SSCoach
SSCoach (19K reputation)SSCoach (19K reputation)SSCoach (19K reputation)SSCoach (19K reputation)SSCoach (19K reputation)SSCoach (19K reputation)SSCoach (19K reputation)SSCoach (19K reputation)

Group: General Forum Members
Points: 19980 Visits: 11359
Arjun Sivadasan (4/12/2010)
...They all showed me the same execution plan. Am I not 'seeing' something?

Hey Arjun, no you are not missing anything - but I do cover all these questions in Part 2 in some detail. These are all great questions, but please bear with me until next week's concluding part.



Paul White
SQLPerformance.com
SQLblog.com
@SQL_Kiwi
RJ-476420
RJ-476420
SSC Eights!
SSC Eights! (930 reputation)SSC Eights! (930 reputation)SSC Eights! (930 reputation)SSC Eights! (930 reputation)SSC Eights! (930 reputation)SSC Eights! (930 reputation)SSC Eights! (930 reputation)SSC Eights! (930 reputation)

Group: General Forum Members
Points: 930 Visits: 769
this article gave me an idea about the problem I'm working on....Most of all, I like the way the article is presented.
Daniel Bowlin
Daniel Bowlin
SSCarpal Tunnel
SSCarpal Tunnel (4.7K reputation)SSCarpal Tunnel (4.7K reputation)SSCarpal Tunnel (4.7K reputation)SSCarpal Tunnel (4.7K reputation)SSCarpal Tunnel (4.7K reputation)SSCarpal Tunnel (4.7K reputation)SSCarpal Tunnel (4.7K reputation)SSCarpal Tunnel (4.7K reputation)

Group: General Forum Members
Points: 4730 Visits: 2629
I will join the praise of the article. Very thought provoking. I am looking forward to Part II and I am wondering if I am going to have to rethink my standard logic approach to many problems. Challenging standard thought processes and finding new and improved ways to tackle problems is one of the primary benefits to SSC.

Thanks.
Bill Kline-270970
Bill Kline-270970
SSC Veteran
SSC Veteran (299 reputation)SSC Veteran (299 reputation)SSC Veteran (299 reputation)SSC Veteran (299 reputation)SSC Veteran (299 reputation)SSC Veteran (299 reputation)SSC Veteran (299 reputation)SSC Veteran (299 reputation)

Group: General Forum Members
Points: 299 Visits: 730
Good stuff. Looking forward to part 2.
WayneS
WayneS
SSChampion
SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)SSChampion (12K reputation)

Group: General Forum Members
Points: 12149 Visits: 10601
BTW Paul, I really like the way you did the data diagram with grids under the tables (so much that I'll probably utilize that technique in any future articles that I might write :-D).

I see that the function utilizes "WITH SCHEMABINDING". Is this a necessary step?

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, How to ask a question, Performance Problems, Common date/time routines,
CROSS-TABS and PIVOT tables Part 1 & Part 2, Using APPLY Part 1 & Part 2, Splitting Delimited Strings

Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search