August 16, 2013 at 4:32 am
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
August 18, 2013 at 9:47 am
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.
August 18, 2013 at 10:43 pm
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.
August 19, 2013 at 6:27 am
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.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply