Outer Apply.

  • ramana3327

    SSCoach

    Points: 19358

    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.

  • Gail Shaw

    SSC Guru

    Points: 1004446

    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

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass
  • Grant Fritchey

    SSC Guru

    Points: 395510

    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

    The Scary DBA
    Author of: SQL Server 2017 Query Performance Tuning, 5th Edition and SQL Server Execution Plans, 3rd Edition
    Product Evangelist for Red Gate Software

  • Bhaskar.Shetty

    Hall of Fame

    Points: 3054

    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.

  • Gail Shaw

    SSC Guru

    Points: 1004446

    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

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass
  • Bhaskar.Shetty

    Hall of Fame

    Points: 3054

    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...

  • ramana3327

    SSCoach

    Points: 19358

    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?

  • Gail Shaw

    SSC Guru

    Points: 1004446

    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

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass
  • ramana3327

    SSCoach

    Points: 19358

    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?

  • Gail Shaw

    SSC Guru

    Points: 1004446

    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

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass

Viewing 10 posts - 1 through 10 (of 10 total)

You must be logged in to reply to this topic. Login to reply