Hash Join Showing on Full Text Query - SQL Server 2005

Question

I have the following query in SQL Server 2005:

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

Interestingly, using this generates a Hash Match, whereas if I use another SUPPLIER_ORGANIZATION_ID (an older provider), it uses Merge Join. Obviously, Hash is much slower than Merge Join. What I am not getting is why there is a difference and what is needed to speed things up?

FYI, there are about 5 million records in the PRODUCT table. When a vendor identifier (13225) is selected, about 25,000 products are available for that vendor.

Thanks in advance.

Answer 1

I would try using OPTIMIZE FOR Query Hint to force it anyway.

 SELECT PRODUCT_ID FROM PRODUCTS P WHERE SUPPLIER_ORGANIZATION_ID = @Supplier_Organisation_Id AND ACTIVE_FLAG = 'Y' AND CONTAINS(*, 'FORMSOF(Inflectional, @Keywords) ') OPTION (OPTIMIZE FOR (@Supplier_Organisation_Id = 1000 )) 

One more thing: your STATISTICS may be out of date, the polling point for automatic updates is often not low enough, which means that the query you select may not be ideal for your data. I suggest trying to update STATISTICS in the Products table, perhaps by creating a task to do this on a regular basis, if that is part of the problem.