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

Outer Apply and Left Join differance Expand / Collapse
Author
Message
Posted Friday, August 16, 2013 4:32 AM
SSC-Enthusiastic

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

Group: General Forum Members
Last Login: Tuesday, August 12, 2014 12:11 AM
Points: 138, Visits: 46
Want to know the difference between Outer Apply and Left Join.
For the same query if i use Outer Apply, query output will be faster but in some DBs Outer Apply takes log period for returning details.

i am not seeing consistency in the data output time.
Is there any restriction due to the SQL Server 2005/2008/2008 R2 for using Cross Apply in queries.

Thanks & Regards,
Balaji
Post #1485085
Posted Sunday, August 18, 2013 9:47 AM
SSC-Enthusiastic

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

Group: General Forum Members
Last Login: Wednesday, August 13, 2014 6:41 AM
Points: 197, Visits: 729
The main difference between the join and the apply operator is that the apply operator can be used to "join" the result from a function.

If you use the apply-operator using a subselect it might be the same than using the join-operator.
In my experience the apply-operator is more likely to use the subselect as a sort of loop and executes the subselect for each outer row. But also this: not always.

If you expierence differences in your performance you have to look at the execution plans.
If you are not familiar with reading execution plans save them and post them here (save it as am XML, NOT as a screenshot.
You can also try to create an example and post it here so we can have a detailled look at it.
Post #1485594
Posted Sunday, August 18, 2013 10:43 PM
SSC-Enthusiastic

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

Group: General Forum Members
Last Login: Tuesday, August 12, 2014 12:11 AM
Points: 138, Visits: 46
Thanks for reply....

the main difference i have seen in these are

Left Join: i need to prepare the query as table (Temporary Table) and actual join will be provided after filtering of data using "ON"

Cross Apply: i can prepare the Temporary Table in that i can make use of my parent table reference.

Example:

Left Join:
Select r.Name, r.EmailID, ISNULL(j.Employer) as PresentEmployer From CandidateInfo r WITH (NOLOCK)
LEFT JOIN (Select CandID, Employer From JOB_EMPLOYER WITH (NOLOCK)) as j ON j.CandID = r.ID

OUTER APPLY:
Select r.Name, r.EmailID, ISNULL(j.Employer) as PresentEmployer From CandidateInfo r WITH (NOLOCK)
OUTER APPLY (Select Employer From JOB_EMPLOYER WITH (NOLOCK) Where CandID = r.ID) as j

These two sample queries are examples in which i actually use Pivot output as table and that will be joining with parent table for getting details.

As per my observation in SQL Server 2005, i was not seen any greater performance when i user CROSS APPLY but query output was faster and worker table load was also pretty low when i user CROSS APPLY in SQL Server 2008.

But same query was not performing properly if use this logic for other combination.
So i want to know that is there any dependency or specific formation where and when we can use CROSS APPLY.
Post #1485639
Posted Monday, August 19, 2013 6:27 AM
SSC-Enthusiastic

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

Group: General Forum Members
Last Login: Wednesday, August 13, 2014 6:41 AM
Points: 197, Visits: 729
The apply-operator was introduced to "join" the result of a function to a resultset. That was not possible before the apply-operator.
When using a table expression for joining there is no general answer whether you should use the one or the other.

The only thing you can do is: analyse the execution plans!

If you need some more information about your example queries, give us your execution plans or provide some example data.
Post #1485757
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse