Post reply

Left Join Perfomance

  • November 20, 2014 at 4:10 am

    #301312

    Quick one..

    Which generally performs best.

    Left join BIG TABLE to Smaller table

    or

    Left Join Smaller Table to BIG TABLE

    I would guess Left join BIG TABLE to Smaller table

  • November 20, 2014 at 5:03 am

    #1759783

    To be honest, performance is irrelevant in this case.

    If you need all the rows from a large table and matching rows from a small table, then LargeTable LEFT OUTER JOIN SmallTable

    If you need all the rows from a small table and matching rows from a large table, then SmallTable LEFT OUTER JOIN LargeTable

    They produce different results (assuming that there are non-matching rows, if there aren't use INNER JOIN) and hence cannot be switched one to the other for performance reasons (unless the users like getting incorrect results fast)

    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

  • November 20, 2014 at 6:19 am

    #1759810

    Thanks

    What I will be questioning is if the LEft joins in the code are actually valid or simply a lack of understanding of which joins to use.

    I can see some code with excessive left joins amongst many other things.

    Correct to assume if you want to find where there is no match IF NOT EXIST will perform quicker.

  • November 20, 2014 at 6:37 am

    #1759817

    WHERE EXISTS, not IF NOT EXISTS

    Very, very, very slightly more efficient.

    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

  • November 20, 2014 at 7:05 am

    #1759826

    Thanks,

    Saved me a lot of time googling.

  • November 20, 2014 at 7:17 am

    #1759828

    There's 5 posts on my blog on the subject if you want the full detail.

    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

  • November 21, 2014 at 1:23 am

    #1760017

    I'll take a look now.

    Thanks

Viewing 7 posts - 1 through 6 (of 6 total)

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