Hash Join Showing up on Full Text Query

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Hash Join Showing up on Full Text Query

Author

Topic

TRaymond
Starting Member

2 Posts

Posted - 2009-03-25 : 14:27:56

Hello,

I have the following Query:


SELECT PRODUCT_ID
FROM PRODUCTS P
WHERE SUPPLIER_ORGANIZATION_ID = 13225
and ACTIVE_FLAG = 'Y'
and CONTAINS(*, 'FORMSOF(Inflectional, "%clip%") ')

What's interesting is that using this generates a Hash Match whereas if I use a different SUPPLIER_ORGANIZATION_ID (older supplier), it uses a Merge Join. Obviously the Hash is much slower than the Merge Join. What I don't get is why there is a difference, and what's needed to make it run faster?

FYI, there are about 5 million records in the PRODUCTS table.....when supplier organization id is selected (13225), there are about 25000 products for that supplier.

Thanks in advance.

TRaymond

RickD
Slow But Sure Yak Herding Master

3608 Posts

Posted - 2009-03-26 : 05:17:16

At a guess, I would say that you may have fragmentation in your Clustered index or you haven't updated the stats. Have a look at the link below for more things you can look at to troubleshoot this issue.

http://technet.microsoft.com/en-us/library/ms142560.aspx

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources