Hash Join Showing up 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%") ')

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.

Answer 1

I'd try using the OPTIMIZE FOR Query Hint to force it one way or the other.

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 other thing is your STATISTICS might out of date, the tipping point for automatic updates is often not low enough meaning that the query plan chosen may not be ideal for your data. I'd suggest trying updating the STATISTICS on your Products table, perhaps creating a job to do this on a regular basis if this is part of the problem.