Results 1 to 9 of 9
  1. #1
    Join Date
    Mar 2006
    Posts
    4

    Question Unanswered: Nested Loop Join - need help :)

    i'm running the following code on Ms SQL Server 2000, Query Analyzer to analyze the result of Nested Loop Join.

    SET STATISTICS PROFILE ON
    GO
    SELECT pdN.ProductID, pdN.ProductName,
    spN.CompanyName, spN.ContactName
    FROM dbo.ProductsNew pdN
    INNER JOIN dbo.SuppliersNew spN
    ON pdN.SupplierId = spN.SupplierId
    GO

    but the execution plan give me the following result :-

    http://i31.photobucket.com/albums/c3...cutionplan.jpg

    instead of using nested, why does it using hash join? is there anything wrong with my code?

  2. #2
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Hi

    Welcome to the forum

    Nowt wrong with your query. I didn't enlarge your image however the optimiser will select the best plan it can (within certain provisos e.g. it selects the best plan within a time limit, it bases it's plan on available statistics etc.). As such - it is probable for this query that a hash join is better than a nested loop join. BOL illustrates such cases:

    Quote Originally Posted by SQL Server Bilble
    A nested loops join is particularly effective if the outer input is quite small and the inner input is preindexed and quite large. In many small transactions, such as those affecting only a small set of rows, index nested loops joins are far superior to both merge joins and hash joins. In large queries, however, nested loops joins are often not the optimal choice.
    Short of using hints (which is a bad idea unless you really know what you are doing and, arguably, not even then) all you can do is make sure that you write good, efficient SQL (as you have), ensure statistics are up to date and indexes are optimal. SQL Server does the rest.

    HTH
    Last edited by pootle flump; 03-31-06 at 18:22.
    Testimonial:
    pootle flump
    ur codings are working excelent.

  3. #3
    Join Date
    Mar 2006
    Posts
    4
    Thank you for replying

    Is it possible for me to still get the result of using nested loop join because i need to make a comparison between the execution time of using nested loop, hash & merge join. Will i get the result that i want if i use FORCE option?

  4. #4
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    You can force these using Join Hints - check the BOL entries for "FROM" and "Hints".
    Testimonial:
    pootle flump
    ur codings are working excelent.

  5. #5
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    BTW - out of curiosity - how come you want to compare the three rather than leave it up to the optimiser?
    Testimonial:
    pootle flump
    ur codings are working excelent.

  6. #6
    Join Date
    Mar 2006
    Posts
    4
    hehe, thanks.
    I'm doing my FYP, and i need this results to be included in the report. Plus, i need to come up with a better algorithm, to retrieve data in distributed database. Thanks again for ur help.

  7. #7
    Join Date
    Mar 2006
    Posts
    4
    where is it the BOL entries , sorry newbie here i couldn't find it.

  8. #8
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Quote Originally Posted by i3lu3fun
    I'm doing my FYP, and i need this results to be included in the report.
    Aw - an RFH.

    Ah well - I noticed a little gotcha that I didn't know re hints (unsurprising as you can probably tell I don't tend to use them) - see if you can spot it. Having reread BOL I think that it is referred to but it is rather under stated....
    Last edited by pootle flump; 04-03-06 at 10:36.
    Testimonial:
    pootle flump
    ur codings are working excelent.

  9. #9
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Quote Originally Posted by i3lu3fun
    where is it the BOL entries , sorry newbie here i couldn't find it.
    Well - you are looking for Join Hints so I guess you need to search for...
    Testimonial:
    pootle flump
    ur codings are working excelent.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •