none
INNER LOOP JOIN

    Question

  • I have just discovered the INNER LOOP JOIN. I have no idea when to use this feature instead of the regular INNER JOIN. Are there any rules to its use?

    If I incorrectly use the INNER LOOP JOIN can it cause me to bring back incorrect data? I am worried about presenting wrong data/ row counts etc.

     


    Mr Shaw
    Tuesday, October 12, 2010 10:41 AM

Answers

  • INNER LOOP JOIN will suits more for smaller set of records...

    As the no. of records, grows hash join will perform more better than INNER LOOP jOIN.

    Nested Loops joins time complexity will be around O(n2) , as your records incr. the time also increases by order of n2.  

    Where as in hash join, initially it builds a hash table. so, for smaller set of records, u can see hash join is slower as building hash table takes more time.......

    This below article will show you how nested loop joins will execute... and in this blog, there is explanation about other joins also..check it...

    http://blogs.msdn.com/b/craigfr/archive/2009/03/18/optimized-nested-loops-joins.aspx

    • Marked as answer by Mr Shaw Tuesday, October 12, 2010 12:15 PM
    Tuesday, October 12, 2010 11:08 AM

All replies

  • I would not suggest  you using that HINT (it is join hint) to force the optimizer using HASH/LOOP/MERGE join operation unless you fully understand how it does work

    Read Ami's articles

    http://www.sql-server-performance.com/articles/dba/pysical_join_operators_p1.aspx


    Best Regards, Uri Dimant SQL Server MVP http://dimantdatabasesolutions.blogspot.com/ http://sqlblog.com/blogs/uri_dimant/
    Tuesday, October 12, 2010 10:50 AM
  • It took my query down from 11 sec to 2 secs.


    Mr Shaw
    Tuesday, October 12, 2010 10:56 AM
  • Inner loop join specifies that enforcing query optimizer to use loop join.

    Thanks and regards, Rishabh
    Tuesday, October 12, 2010 10:56 AM
  • Well, it could but perhaps adding simple index could have increased the performance too


    Best Regards, Uri Dimant SQL Server MVP http://dimantdatabasesolutions.blogspot.com/ http://sqlblog.com/blogs/uri_dimant/
    Tuesday, October 12, 2010 10:57 AM
  • I added an extra index which removed all my Scans.

    It was only once I used the LOOP hint the execution time came down.

    The orignal join was a HASH.


    Mr Shaw
    Tuesday, October 12, 2010 11:01 AM
  •  My big concern is do I run the risk of returning incorrect data?


    Mr Shaw
    Tuesday, October 12, 2010 11:06 AM
  • INNER LOOP JOIN will suits more for smaller set of records...

    As the no. of records, grows hash join will perform more better than INNER LOOP jOIN.

    Nested Loops joins time complexity will be around O(n2) , as your records incr. the time also increases by order of n2.  

    Where as in hash join, initially it builds a hash table. so, for smaller set of records, u can see hash join is slower as building hash table takes more time.......

    This below article will show you how nested loop joins will execute... and in this blog, there is explanation about other joins also..check it...

    http://blogs.msdn.com/b/craigfr/archive/2009/03/18/optimized-nested-loops-joins.aspx

    • Marked as answer by Mr Shaw Tuesday, October 12, 2010 12:15 PM
    Tuesday, October 12, 2010 11:08 AM