July 18, 2021 at 4:06 pm
Hi,
I have 2 tables which join on ID column.
select name from Product P inner join LineItem L on P.ID = L.ID
Product table has clustered index on ID while LineItem has non clustered index on ID.
Initially, both the ID columns were nvarchar and query was doing an index scan and running pretty fast.
I changed both the columns to varchar and the query started making a hash join and is taking a long time to return the resultset.
I have tried Index rebuild, reorganise but nothing seems to work,
Any suggestion as to how and why it is doing a hash join?
July 18, 2021 at 4:35 pm
You'll probably get more help with this problem if you attach the actual execution plan.
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin
July 18, 2021 at 7:40 pm
Are you really only returning a single column from the query?
If so, change to SELECT just from the table with that column, and use a WHERE EXISTS() on the other table rather than doing a join. In theory SQL should probably use the same query plan for either one, but it can't hurt to try.
As to "why" SQL used a hash join, or did some other thing, the answer is always the same: SQL determined that was the most efficient way to do the query that it could determine in a reasonable amount of time to determine the plan. Of course SQL is sometimes wrong about what is really most efficient, but that is still "why" it did it.
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply