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
Tom Brown
Tom  Brown
Hall of Fame
Hall of Fame (3.4K reputation)Hall of Fame (3.4K reputation)Hall of Fame (3.4K reputation)Hall of Fame (3.4K reputation)Hall of Fame (3.4K reputation)Hall of Fame (3.4K reputation)Hall of Fame (3.4K reputation)Hall of Fame (3.4K reputation)

Group: General Forum Members
Points: 3404 Visits: 1491
This is very good Paul.

Concise and Clear, not too long and with easily understandable examples.

Thanks for taking the time to create this article. I appreciate your efforts.

10 minutes learning something new in the morning is a great way to start my day.
Paul White
Paul White
SSC-Dedicated
SSC-Dedicated (36K reputation)SSC-Dedicated (36K reputation)SSC-Dedicated (36K reputation)SSC-Dedicated (36K reputation)SSC-Dedicated (36K reputation)SSC-Dedicated (36K reputation)SSC-Dedicated (36K reputation)SSC-Dedicated (36K reputation)

Group: General Forum Members
Points: 36188 Visits: 11361
Thanks, Tom.



Paul White
SQLPerformance.com
SQLblog.com
@SQL_Kiwi
reinpost
reinpost
Forum Newbie
Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)

Group: General Forum Members
Points: 6 Visits: 6
Just a trivial remark: right at the start it says
APPLY is named after the process of applying a set of input rows to a table-valued function.
Surely you mean to say applying a table-valued function to a set of input rows.
logicinside22
logicinside22
SSCrazy
SSCrazy (2K reputation)SSCrazy (2K reputation)SSCrazy (2K reputation)SSCrazy (2K reputation)SSCrazy (2K reputation)SSCrazy (2K reputation)SSCrazy (2K reputation)SSCrazy (2K reputation)

Group: General Forum Members
Points: 2048 Visits: 1420
Really Nice article ..looking forward for more...
good luck..

Aim to inspire rather than to teach.
SQL Server DBA
Paul White
Paul White
SSC-Dedicated
SSC-Dedicated (36K reputation)SSC-Dedicated (36K reputation)SSC-Dedicated (36K reputation)SSC-Dedicated (36K reputation)SSC-Dedicated (36K reputation)SSC-Dedicated (36K reputation)SSC-Dedicated (36K reputation)SSC-Dedicated (36K reputation)

Group: General Forum Members
Points: 36188 Visits: 11361
reinpost (1/6/2012)
Just a trivial remark: right at the start it says
APPLY is named after the process of applying a set of input rows to a table-valued function.
Surely you mean to say applying a table-valued function to a set of input rows.

I think it makes sense either way! Cool



Paul White
SQLPerformance.com
SQLblog.com
@SQL_Kiwi
reinpost
reinpost
Forum Newbie
Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)Forum Newbie (6 reputation)

Group: General Forum Members
Points: 6 Visits: 6
SQL Kiwi (1/6/2012)
reinpost (1/6/2012)
Just a trivial remark: right at the start it says
APPLY is named after the process of applying a set of input rows to a table-valued function.
Surely you mean to say applying a table-valued function to a set of input rows.

I think it makes sense either way! Cool

So do I, but mathematicians and programmers tend to say that functions are applied to things. In fact this new operator was probably named after Lisp's 'apply' , which does the same thing.
Jared Karney
Jared Karney
SSChampion
SSChampion (13K reputation)SSChampion (13K reputation)SSChampion (13K reputation)SSChampion (13K reputation)SSChampion (13K reputation)SSChampion (13K reputation)SSChampion (13K reputation)SSChampion (13K reputation)

Group: General Forum Members
Points: 13916 Visits: 3697
reinpost (1/6/2012)
SQL Kiwi (1/6/2012)
reinpost (1/6/2012)
Just a trivial remark: right at the start it says
APPLY is named after the process of applying a set of input rows to a table-valued function.
Surely you mean to say applying a table-valued function to a set of input rows.

I think it makes sense either way! Cool

So do I, but mathematicians and programmers tend to say that functions are applied to things. In fact this new operator was probably named after Lisp's 'apply' , which does the same thing.


Even though it is irrelevant... I'll agree that the proper is that the function applies to the rows. I thought about it like a car and paint. When you apply the paint to the car, the properties of the paint do not change the car does. So in this case the function does not change at all, it is applied to the rows of which the properties have changed.

Now that I said that... Who cares, it still accomplishes the same thing Tongue

Thanks,
Jared
PFE - Microsoft
SQL Know-It-All
How to post data/code on a forum to get the best help - Jeff Moden
SAinCA
SAinCA
SSC-Addicted
SSC-Addicted (460 reputation)SSC-Addicted (460 reputation)SSC-Addicted (460 reputation)SSC-Addicted (460 reputation)SSC-Addicted (460 reputation)SSC-Addicted (460 reputation)SSC-Addicted (460 reputation)SSC-Addicted (460 reputation)

Group: General Forum Members
Points: 460 Visits: 701
A word of caution

I took the tip about SCHEMABINDING and the optimizer's expanding iTVFs and converted a Multi-statement UDF (referred to from here on as the TF) that splits up to 12MB of delimited data using a Numbers table. Perhaps this is SS2005EE-specific and later versions are smarter, but...

BAD MOVE!

I have this (inherited) snippet from an SP that uses CROSS APPLY in the FROM clause and has a WHERE clause that contains DBA.dbo.udf_Split(), which is the TF I converted to an iTVF. The SP happens to be the most important SP in the system - the one that gets a User's permissions to do anything. It runs in under 1 second for over 500 Users using the original TF. However, it takes 15+ minutes to run the same SP using the iTVF for a single User, I consider this a very bad move and will pull the plug on changing this TF to an iTVF:-D

FROM dbo.t_user_ref ur
CROSS APPLY dbo.fn_GetPermissionByUser(user_id) AS p
WHERE GETDATE() BETWEEN eff_date AND term_date
AND ( @CompanyID IS NULL
OR ( @CompanyID IS NOT NULL
AND @CompanyID > 0
AND company_id = @CompanyID
)
)
AND ( @SiteIDs IS NULL
OR ( @SiteIDs IS NOT NULL
AND EXISTS ( SELECT 1
FROM DBA.dbo.udf_Split( @SiteIDs + ', ',',')
WHERE ',' + r_site_ids + ',' <> REPLACE(',' + r_site_ids + ',', ',' + [value] + ',', '')
)
)
)
AND ( @UserID IS NULL
OR ( @UserID IS NOT NULL
AND @UserID > 0
AND user_id = @UserID
)
)
AND ( @UserRole IS NULL
OR ( @UserRole IS NOT NULL
AND p.r_user_role LIKE '%' + @UserRole
)
)



The optimizer did indeed expand the iTVF form of the UDF into the main SELECT, but then goodness knows why it decides that 12789618 rows must now be scanned for each User row.

Be very, very careful which TFs you convert to iTVFs... Ermm
Paul White
Paul White
SSC-Dedicated
SSC-Dedicated (36K reputation)SSC-Dedicated (36K reputation)SSC-Dedicated (36K reputation)SSC-Dedicated (36K reputation)SSC-Dedicated (36K reputation)SSC-Dedicated (36K reputation)SSC-Dedicated (36K reputation)SSC-Dedicated (36K reputation)

Group: General Forum Members
Points: 36188 Visits: 11361
SAinCA (1/6/2012)
A word of caution

It is certainly still possible to write highly dubious code using in-line table-valued functions if you really try. There are a few cases where a multi-statement function makes sense; in this case it's because the results of the split are materialized into a table variable once and reused many times that way. Of course, this is something that could easily be done without the msTVF - just split the site IDs into a temporary table first. This can be better than using an msTVF since the 'real' temporary table can have statistics and can be indexed - the hidden msTVF table variable cannot do either of those things!

So my word of caution would be: if you find a multi-statement TVF performing better, check that you shouldn't be materializing something yourself. Do it properly with an iTVF and a temporary table and you'll get statistics, indexes, and probably better performance.

Fellow MVP Brad Shultz went into some details about why the multi-statement hidden materialization works here:

http://bradsruminations.blogspot.com/2010/08/integer-list-splitting-sql-fable.html



Paul White
SQLPerformance.com
SQLblog.com
@SQL_Kiwi
Jeff Moden
Jeff Moden
SSC Guru
SSC Guru (218K reputation)SSC Guru (218K reputation)SSC Guru (218K reputation)SSC Guru (218K reputation)SSC Guru (218K reputation)SSC Guru (218K reputation)SSC Guru (218K reputation)SSC Guru (218K reputation)

Group: General Forum Members
Points: 218961 Visits: 42000
SAinCA (1/6/2012)
A word of caution

I took the tip about SCHEMABINDING and the optimizer's expanding iTVFs and converted a Multi-statement UDF (referred to from here on as the TF) that splits up to 12MB of delimited data using a Numbers table. Perhaps this is SS2005EE-specific and later versions are smarter, but...


It could be the way the split function is using the Numbers table. I've seen it where the entire table gets scanned because of "reflection" (not an official term but that's what I think of it as) back to the table from the "outside" world cause the table to be scanned many times.

Would you mind posting your split function so I can take a look, please?

Thanks.

--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.
If you think its expensive to hire a professional to do the job, wait until you hire an amateur. -- Red Adair

Helpful Links:
How to post code problems
How to post performance problems
Forum FAQs
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