Post reply

Nested Loops Joins

  • August 23, 2012 at 4:56 am

    #260333

    Hi

    I have been lokoing at Nested Loop Joins and i probably dont fully understand them but how can this benefit as opposed to just using a regular join?

    Thanks in advance

  • August 23, 2012 at 5:06 am

    #1529172

    well flippin ek

    i have been messing with loop join and hash join options and these are making massive differences to my queries

    smaller tables (in terms of numbers of rows) loop joins seem to be massively quicker

    i have put a hash join on a complex query using several tables and it has halfed the speed

    merge joins dont seem to help either tho :/

    either way i am happier now than i was a few minutes ago 🙂

  • August 23, 2012 at 7:08 am

    #1529225

    Having just seen this and your other post re merge joins.

    The vast majority of the time the query optimiser will choose the strategy for the physical way of implementing the join and get it right. Very occasionally overriding that by means of the join hints can have benefits, but the danger is it gets left embedded in the code.

    I would make sure you undestand the impact up to date statistics can have before relying on join hints too much

    Mike

  • August 23, 2012 at 7:22 am

    #1529234

    ahh i understand now

    so if i do a join in a query then it wont neccessarily create a cartesian join, it will look at the options and perhaps use a merge or loop join?

  • August 23, 2012 at 7:23 am

    #1529236

    having said that i have just run the code below in teh query plan and the second query is much more costly

    select *

    from Sales S inner join Customers C

    on S.Cust_Id = C.Cust_Id

    option(loop join)

    select *

    from Sales S inner join Customers C

    on S.Cust_Id = C.Cust_Id

  • August 24, 2012 at 10:39 am

    #1529860

    Make sure the statistics on all tables are up to date -- obsolete statistics could cause SQL to choose the wrong type of join.

    Of course SQL sometimes chooses the wrong type of join even with current stats, but you want it to have the best chance possible to get it right :-).

    SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".

  • August 24, 2012 at 12:19 pm

    #1529903

    And no amount of testing can ensure that using join hints will be correct. What is better on the data today may very well be different tomorrow when the amount of data changes. Join hints should be used VERY rarely. Basically when using join hints you are saying that you know better how to retrieve the data than the optimization engine. Unless you can explain in great detail why you need a particular join hint on a query you probably shouldn't be using it.

    _______________________________________________________________

    Need help? Help us help you.

    Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

    Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

    Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
    Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
    Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
    Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

  • August 24, 2012 at 12:29 pm

    #1529910

    erics44 (8/23/2012)

    I have been lokoing at Nested Loop Joins and i probably dont fully understand them but how can this benefit as opposed to just using a regular join?

    Nested loops, merge and hash are the three physical join methods that SQL has. When you just do inner join/left outer/right outer you're allowing the optimiser to pick the best join for the number of rows involved, indexes that are present and several other factors. When you specify a physical join type, you're telling the optimiser that you know better than it does which join is optimal for this query (and will always be optimal no matter how the data changes)

    Generally unless you know exactly why a specific join operator is better, exactly why the optimiser is picking the 'wrong' join and know that those circumstances won't ever change, leave out the join hints.

    To put things in perspective, in around 8 years of doing SQL performance tuning I've used join hints twice in production code. (one the optimiser was under-estimating the rows due to it's incorrect assumption about how many rows a predicate would eliminate, other case a linked server was involved and hence the row estimation was completely incorrect.)

    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

  • August 25, 2012 at 5:36 am

    #1530047

    thanks a lot for the replies, very informative

    i'll leave the join types alone now but very good to know how it all works

    thanks again

Viewing 9 posts - 1 through 8 (of 8 total)

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