Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 


 
     
Recent Posts
   
Popular Topics
Home   
Search
    Members    Calendar    Who's On

Home » Article Discussions » Article Discussions by Author » Discuss content posted by Paul White » Understanding and Using APPLY (Part 1)
Add to briefcase
90 posts, Page 2 of 9
««12345»»»

Understanding and Using APPLY (Part 1) Expand / Collapse
Rate Topic
Display Mode
Topic Options
Author
Message
Steve Jones - SSC Editor
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: Yesterday @ 5:09 AM
Points: 31,526, Visits: 13,864
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
Luke L
Posted Monday, April 12, 2010 7:09 AM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Thursday, June 13, 2013 12:24 PM
Points: 2,892, Visits: 5,871
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
Paul White
Posted Monday, April 12, 2010 7:29 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Yesterday @ 10:48 AM
Points: 10,990, Visits: 10,578
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
Arjun Sivadasan
Posted Monday, April 12, 2010 7:51 AM


Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Yesterday @ 3:56 AM
Points: 365, Visits: 704
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
Arjun Sivadasan
Posted Monday, April 12, 2010 7:53 AM


Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Yesterday @ 3:56 AM
Points: 365, Visits: 704
I would like to add that I am more worried about performance than best practice and maintenance.

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


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Yesterday @ 10:48 AM
Points: 10,990, Visits: 10,578
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
RJ-476420
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: Yesterday @ 12:55 PM
Points: 602, Visits: 672
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
Daniel Bowlin
Posted Monday, April 12, 2010 8:39 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: 2 days ago @ 8:36 AM
Points: 2,681, Visits: 2,423
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
Bill Kline-270970
Posted Monday, April 12, 2010 8:42 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Tuesday, June 11, 2013 9:51 AM
Points: 168, Visits: 673
Good stuff. Looking forward to part 2.
Post #901669
WayneS
Posted Monday, April 12, 2010 8:59 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 9:21 PM
Points: 6,388, Visits: 8,290
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
90 posts, Page 2 of 9
««12345»»»

Permissions Expand / Collapse

 
 
Copyright © 2002-2013 Simple Talk Publishing. All Rights Reserved. Privacy Policy. Terms of Use. Report Abuse.