May 25, 2014 at 3:56 pm
Hi,
I am working in performance team. I need some help in improving query performance.
In my query they wrote several left outer joins. I want to improve the performance by using outer apply which is similar to left outer join
Suppose in left outer join
select colm1,colum2
from TableA A
left outer join TableB B
on A.colm1=B.col2.
For this above query how to write by using outer apply.
In this query, I don't have any functions but I want test the performance by changing into outer apply instead of left outer join.
May 25, 2014 at 4:19 pm
Why? That's not the kind of query you'd use apply for. Apply isn't a magic performance enhancement.
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
May 26, 2014 at 1:48 am
Have you looked at the execution plans to determine how the optimizer is resolving the existing queries. As Gail said, APPLY doesn't seem like the right tool here.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
May 26, 2014 at 2:31 am
Look at the excecution plan and IO Results to check for any missing indexes and heavy CPU and memory joins,
Changing joins will not impact the speed of query.
May 26, 2014 at 2:33 am
Bhaskar.Shetty (5/26/2014)
Changing joins will not impact the speed of query.
It can. Apply used inappropriately can cause massive performance degradation.
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
May 26, 2014 at 2:40 am
It can. Apply used inappropriately can cause massive performance degradation.
Oh... I messed it up... Yes you are right Apply will have a impact on performance.. Thaks for correcting me...
May 26, 2014 at 2:37 pm
I thought compare to left outer join, outer Apply is better!
Also when you are looking for performance improvement which one is important?
I mean total elapsed time or CPU time. If we find total elapsed time is less and CPU time is higher after you did some modification to the code, shall we give that recommendation to the client?
Which one we have to consider in query tuning CPU time or total elapsed time?
May 26, 2014 at 2:53 pm
Apples and watermelons
Joins are for when you want to relate two tables or subqueries. Apply is for when you want to run a function or subquery once for every row of the outer table.
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
May 26, 2014 at 4:33 pm
Thanks Gila.
Please let me know,
Which one we have to consider in query tuning CPU time or total elapsed time? If our modification raise one time and reduce another shall we consider that modification?
May 27, 2014 at 2:17 am
Please post unrelated questions in a new thread
And forgive me if this sounds insulting, but do you do any research at all before asking here? If the answer to that is 'no', may I suggest that you acquaint yourself with a good search engine and do your own research on any topic that bothers you and ask here when you can't find the answer.
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
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply