Pages

Sep 17, 2015

Unwanted MANY TO MANY join execution plan SQL Server

Sometimes we need MANY TO
MANY join in SQL Server. Sometime we don’t but event that sometimes SQL Server
query optimizer chooses MANY TO MANY join. That was not our actual need. Let me
explain it by an example:

Let’s assume we have two
tables named UserPhoto and tblUser:

CREATETABLE [dbo].[UserPhoto](

[UserPhotoID]
[bigint] PRIMARYKEYIDENTITY(1, 1)NOTNULL

,[PhotoID] [int] NULL

,[PhotoPath] [varchar](100)NULL

)

CREATETABLE [dbo].[tblUser](

[UserID]
[bigint] PRIMARYKEYIDENTITY(1, 1)NOTNULL

,[UserName] [varchar](100)NULL

,[PhotoID] [int] NULL

)

Creating non-clustered
indexes to improve performance:

CREATENONCLUSTEREDINDEX NCI_PhotoID

ON [dbo].[tblUser]([PhotoID])

INCLUDE (

[UserID]

,[UserName]

)

CREATENONCLUSTEREDINDEX NCI_PhotoID

ON [dbo].[UserPhoto]([PhotoID])

INCLUDE ([PhotoPath])

Let’s assume a business
constrains is an user can have only one photo. So we are populating data in
both tables so that an cannot have more than one photoid:

Now we are going to execute
below script which join above two tables on the basis of PhotoID:

SETSTATISTICSIOON

SELECTTOP (100000) U.UserID

,U.UserName

,UP.PhotoPath

FROM tblUser U

INNERJOIN UserPhoto UP ON U.PhotoID = UP.PhotoID

Let’s analyze its execution
plan:

If we will notice on merge
join we will get MANY TO MANY is true. While our business constraint says an
user cannot have more than one photo.
Then while SQL Server query optimizer chosen MANY TO MANY join which is
costlier?

Reason behind this is SQL
Server query optimizer doesn’t know about our business constraint “An User can
have one and only one photo”. That is why it has generated bad execution plan
that is MANY TO MANY. Hence it is our task to tell SQL server query optimizer
about our business constraints. In this case we can say about our business
constraint (An User can have one and only one photo) by creating below two
UNIQUE NONCLUSTERED indexes: