Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase ««12345»»»

Understanding and Using APPLY (Part 1) Expand / Collapse
Author
Message
Posted Monday, April 12, 2010 7:08 AM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: Administrators
Last Login: Today @ 3:05 PM
Points: 31,284, Visits: 15,750
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
Post #901581
Posted Monday, April 12, 2010 7:09 AM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: 2 days ago @ 9:29 AM
Points: 2,655, Visits: 6,016
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
Post #901582
Posted Monday, April 12, 2010 7:29 AM


SSCrazy Eights

SSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy Eights

Group: General Forum Members
Last Login: Yesterday @ 6:23 AM
Points: 9,928, Visits: 11,196
Thank you all for the kind reviews (so far): Matt, Christian, Tom, Jack, Wayne, Steve, and Luke. I really appreciate it.



Paul White
SQL Server MVP
SQLblog.com
@SQL_Kiwi
Post #901598
Posted Monday, April 12, 2010 7:51 AM


SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Today @ 6:56 AM
Points: 406, Visits: 779
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
Post #901612
Posted Monday, April 12, 2010 7:53 AM


SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Today @ 6:56 AM
Points: 406, Visits: 779
I would like to add that I am more worried about performance than best practice and maintenance.

- arjun
Post #901618
Posted Monday, April 12, 2010 8:04 AM


SSCrazy Eights

SSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy Eights

Group: General Forum Members
Last Login: Yesterday @ 6:23 AM
Points: 9,928, Visits: 11,196
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
SQL Server MVP
SQLblog.com
@SQL_Kiwi
Post #901630
Posted Monday, April 12, 2010 8:30 AM
SSChasing Mays

SSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing Mays

Group: General Forum Members
Last Login: Tuesday, October 7, 2014 1:56 PM
Points: 610, Visits: 720
this article gave me an idea about the problem I'm working on....Most of all, I like the way the article is presented.

Post #901655
Posted Monday, April 12, 2010 8:39 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Tuesday, November 4, 2014 12:48 PM
Points: 2,818, Visits: 2,566
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.
Post #901664
Posted Monday, April 12, 2010 8:42 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Wednesday, November 5, 2014 1:36 PM
Points: 235, Visits: 730
Good stuff. Looking forward to part 2.
Post #901669
Posted Monday, April 12, 2010 8:59 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 7:20 AM
Points: 5,367, Visits: 8,986
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 ).

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


Wayne
Microsoft Certified Master: SQL Server 2008
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
Post #901693
« Prev Topic | Next Topic »

Add to briefcase ««12345»»»

Permissions Expand / Collapse