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/c366/i3lu3fun/executionplan.jpg

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

Welcome to the forum :D

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:

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|||Thank you for replying :D

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?|||You can force these using Join Hints - check the BOL entries for "FROM" and "Hints".|||BTW - out of curiosity - how come you want to compare the three rather than leave it up to the optimiser?|||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.|||where is it the BOL entries :p, sorry newbie here i couldn't find it.|||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...|||where is it the BOL entries :p, sorry newbie here i couldn't find it.Well - you are looking for Join Hints so I guess you need to search for... :rolleyes: