January 15, 2009 at 11:12 am
Are there any performance benefits of using CROSS APPLY OVER INNER JOIN? or vice versa? Or are they both good for specific combinations of the participating tables (large left table v/s small right table????)?
Thanks for any guidance one could offer!
Leon
January 15, 2009 at 11:21 am
CROSS APPLY is only used with functions that return resultsets based on pamareters. It's been created to allow the use of those function in an inner join when the parameters are dynamic.
Inner join are just that inner joins. So there's no real comparaison to do here since they are not doing the same thing at all, or more to the point, a similar task in a different context.
January 15, 2009 at 11:21 am
The APPLY operator is designed for use against table valued functions while JOIN is for tables. They are not interchangeable.
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
January 15, 2009 at 11:24 am
Thanks much!
Leon
January 15, 2009 at 11:35 am
Jack Corbett (1/15/2009)
The APPLY operator is designed for use against table valued functions
Or (I believe) correlated subqueries in the FROM clause
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
January 15, 2009 at 11:38 am
GilaMonster (1/15/2009)
Jack Corbett (1/15/2009)
The APPLY operator is designed for use against table valued functionsOr (I believe) correlated subqueries in the FROM clause
Yep.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
January 15, 2009 at 11:38 am
Jack Corbett (1/15/2009)
The APPLY operator is designed for use against table valued functions while JOIN is for tables. They are not interchangeable.
not SUPPOSED to be interchangeable.
Not to niggle excessively, but CROSS APPLY can be used in either scenario (tables or functions) with no downside I can see other than the glaring "that's not what it was written for" downside.
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
January 15, 2009 at 11:39 am
Wow - she's fast!!!! Beat me to it!
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
January 15, 2009 at 11:39 am
ok gila now that confuses me ... let me ask this another way - would you even consider Cross apply as a performance alternative to inner join
January 15, 2009 at 11:42 am
There's always and only 1 answer to that question... it depends.
Make your own scenarios and test them.
January 15, 2009 at 11:47 am
leonp (1/15/2009)
ok gila now that confuses me
What confuses you? There's no other way to correlate a subquery in the from clause than to use one of the APPLY operators.
... let me ask this another way - would you even consider Cross apply as a performance alternative to inner join
They have different purposes. Therefore one is not an alternative to the other.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
January 15, 2009 at 11:51 am
Not having used CROSS APLLY myself, I'd have to say that it depends on what you are trying to accomplish.
January 15, 2009 at 11:52 am
Just to check something on this, I put together this test:
create table #T1 (
ID int identity primary key,
Col1 int);
insert into #T1 (Col1)
select number
from dbo.numbers;
select *
from dbo.Numbers
cross apply
(select col1
from #T1
where col1 = numbers.number) T1;
select *
from dbo.Numbers
inner join #T1
on col1 = number;
Both selects ended up with exactly the same execution plans.
Yeah, it's not what cross apply was meant for, but it does work.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
January 15, 2009 at 11:57 am
-- normal query
select o.name, c.name
FROM sys.objects o
inner join sys.columns c on o.object_id = c.object_id
-- noncorrelated subquery with inner join
select o.name, c.name
FROM sys.objects o
inner join (select object_id, name from sys.columns) c on o.object_id = c.object_id
-- noncorrelated subquery with cross apply
select o.name, c.name
FROM sys.objects
cross apply (select object_id, name from sys.columns) c on o.object_id = c.object_id
/*Msg 156, Level 15, State 1, Line 3
Incorrect syntax near the keyword 'on'.*/
-- correlated subquery with inner join
select o.name, c.name
FROM sys.objects o
INNER JOIN (select object_id, name from sys.columns col where col.object_id = o.object_id) c on o.object_id = c.object_id
/*Msg 4104, Level 16, State 1, Line 3
The multi-part identifier "o.object_id" could not be bound.*/
-- correlated subquery with cross apply
select o.name, c.name
FROM sys.objects o
CROSS APPLY (select object_id, name from sys.columns col where col.object_id = o.object_id) c
All the ones that work perform identically (not that using the system tables is much of a test). From previous experience with correlated subqueries in other clauses, providing it's an equality comparison between the subquery and the outer table, it should perform identically to the equivalent query with a straightforward join and with the same exec plan. When the comparison becomes any form of inequality, the correlated subquery is likely to perform appallingly.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
January 15, 2009 at 12:00 pm
GSquared (1/15/2009)
Just to check something on this, I put together this test:
create table #T1 (
ID int identity primary key,
Col1 int);
insert into #T1 (Col1)
select number
from dbo.numbers;
select *
from dbo.Numbers
cross apply
(select col1
from #T1
where col1 = numbers.number) T1;
select *
from dbo.Numbers
inner join #T1
on col1 = number;
Both selects ended up with exactly the same execution plans.
Yeah, it's not what cross apply was meant for, but it does work.
Not surprising, but, silly me, I went to BOL.
The APPLY operator allows you to invoke a table-valued function for each row returned by an outer table expression of a query. The table-valued function acts as the right input and the outer table expression acts as the left input. The right input is evaluated for each row from the left input and the rows produced are combined for the final output. The list of columns produced by the APPLY operator is the set of columns in the left input followed by the list of columns returned by the right input.
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
Viewing 15 posts - 1 through 15 (of 15 total)
You must be logged in to reply to this topic. Login to reply