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 «««56789»»

Understanding and Using APPLY (Part 1) Expand / Collapse
Author
Message
Posted Thursday, December 1, 2011 2:02 AM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: 2 days ago @ 3:34 PM
Points: 1,293, Visits: 1,428
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.
Post #1214413
Posted Thursday, December 1, 2011 5:01 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 3:56 PM
Points: 11,192, Visits: 11,096
Thanks, Tom.



Paul White
SQL Server MVP
SQLblog.com
@SQL_Kiwi
Post #1214520
Posted Friday, January 6, 2012 4:11 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Monday, January 9, 2012 1:36 AM
Points: 2, 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.
Post #1231358
Posted Friday, January 6, 2012 7:27 AM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Today @ 2:23 PM
Points: 321, Visits: 1,273
Really Nice article ..looking forward for more...
good luck..


Aim to inspire rather than to teach.
SQL Server DBA
Post #1231453
Posted Friday, January 6, 2012 11:03 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 3:56 PM
Points: 11,192, Visits: 11,096
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!




Paul White
SQL Server MVP
SQLblog.com
@SQL_Kiwi
Post #1231619
Posted Friday, January 6, 2012 11:33 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Monday, January 9, 2012 1:36 AM
Points: 2, 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!

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.
Post #1231638
Posted Friday, January 6, 2012 12:11 PM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Today @ 12:42 PM
Points: 2,691, Visits: 3,375
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!

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 :P


Thanks,

Jared
SQL Know-It-All

How to post data/code on a forum to get the best help - Jeff Moden
Post #1231661
Posted Friday, January 6, 2012 4:52 PM


SSC-Enthusiastic

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

Group: General Forum Members
Last Login: Wednesday, July 23, 2014 11:16 AM
Points: 117, Visits: 567
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
     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...
Post #1231783
Posted Friday, January 6, 2012 5:02 PM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 3:56 PM
Points: 11,192, Visits: 11,096
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
SQL Server MVP
SQLblog.com
@SQL_Kiwi
Post #1231784
Posted Friday, January 6, 2012 6:50 PM


SSC-Dedicated

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

Group: General Forum Members
Last Login: Today @ 7:30 PM
Points: 36,766, Visits: 31,222
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."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #1231796
« Prev Topic | Next Topic »

Add to briefcase «««56789»»

Permissions Expand / Collapse