Why SQL Server use Nested Loop

Question

I have query for example:

SELECT TOP 10 
  User.id,
  User.Name,
  Country.Country
  FROM User  
  Inner Join Country 
  ON Country.Id = User.CountryId
  where User.PlanId = 1

In this case SQL manager show in execution plan that use Hash-match and it is pretty fast.

But if I use where User.PlanId = 2 SQL manager use Nested loop for my query and it is very slow... Why with different search criteria it use different algorithmic? How can I fix it?

Also how many rows in the table, how many have PlanId = 1, and how many have PlanId = 2? — Aaron Bertrand
– Aaron Bertrand, Commented Apr 2, 2012 at 17:21
Country.Id is a primary key or at least unique? hmm.. sometimes workaround can be to use left join + add condition and user.countryid is not null.. — Aprillion
– Aprillion, Commented Apr 2, 2012 at 17:21
I have planId=1 about 2500 rows and planId= about 280 rows. And Country.Id = User.CountryId has one to one relationship — Reno
– Reno, Commented Apr 2, 2012 at 20:20

JNK · Accepted Answer · 2012-04-02 18:20:22Z

3

I'm going to guess that you have a much higher number of users with a PlanID of 2 than with 1.

This will explain both the change in exec plan and runtime. A HASH MATCH is the most versatile (and generally least efficient) join. Basically the engine builds a table manually pairing up all the values.

A NESTED LOOP checks each value on the left against each value on the right, and works well when one data set is a lot larger than the other and both sides are indexed.

A HASH MATCH can be quick if you have a really small data set, though. I suspect the speed difference is because of the differing size of the datasets. You can check this pretty easily by:

SELECT PlanId, COUNT(*) as CT
FROM User
GROUP BY PlanID

...which will give you your distribution.

answered Apr 2, 2012 at 18:20

JNK

65.5k15 gold badges127 silver badges139 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Why SQL Server use Nested Loop

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related