FreeText COUNT query on multiple tables is super slow

Question

I have two tables:

**Product**
ID
Name
SKU

**Brand**
ID
Name

Product table has about 120K records Brand table has 30K records

I need to find count of all the products with name and brand matching a specific keyword.

I use freetext 'contains' like this:

SELECT count(*) 
FROM   Product
       inner join Brand 
         on Product.BrandID = Brand.ID
WHERE  (contains(Product.Name, 'pants') 
   or 
            contains(Brand.Name, 'pants'))

This query takes about 17 secs. I rebuilt the FreeText index before running this query.

If I only check for Product.Name. They query is less then 1 sec. Same, if I only check the Brand.Name. The issue occurs if I use OR condition.

If I switch query to use LIKE:

SELECT count(*) 
FROM   Product
       inner join Brand 
         on Product.BrandID = Brand.ID
WHERE   Product.Name LIKE '%pants%'
   or 
            Brand.Name LIKE '%pants%'

It takes 1 secs.

I read on MSDN that: http://msdn.microsoft.com/en-us/library/ms187787.aspx

To search on multiple tables, use a joined table in your FROM clause to search on a result set that is the product of two or more tables.

So I added an INNER JOINED table to FROM:

SELECT count(*) 
FROM   (select Product.Name ProductName, Product.SKU ProductSKU, Brand.Name as BrandName FROM Product
       inner join Brand 
         on product.BrandID = Brand.ID) as TempTable
WHERE  

     contains(TempTable.ProductName, 'pants') 
     or 
            contains(TempTable.BrandName, 'pants')

This results in error: Cannot use a CONTAINS or FREETEXT predicate on column 'ProductName' because it is not full-text indexed.

So the question is - why OR condition could be causing such as slow query?

Answer 1

Have you tried something like:

SELECT count(*) 
    FROM Product
    INNER JOIN Brand ON Product.BrandID = Brand.ID
    WHERE CONTAINS((Product.Name, Brand.Name), 'pants')

Answer 2

After a bit of trial an error I found a solution that seems to work. It involves creating an indexed view:

CREATE VIEW [dbo].[vw_ProductBrand]
WITH SCHEMABINDING
AS
SELECT     dbo.Product.ID, dbo.Product.Name, dbo.Product.SKU, dbo.Brand.Name AS BrandName
FROM         dbo.Product INNER JOIN
                      dbo.Brand ON dbo.Product.BrandID = dbo.Brand.ID

GO

CREATE UNIQUE CLUSTERED INDEX IX_VW_PRODUCTBRAND_ID 
    ON vw_ProductBrand (ID);
GO

If I run the following query:

DBCC DROPCLEANBUFFERS
DBCC FREEPROCCACHE
GO

SELECT count(*) 
FROM   Product
       inner join vw_ProductBrand
         on Product.BrandID =  vw_ProductBrand.ID
WHERE  (contains(vw_ProductBrand.Name, 'pants') 
   or 
            contains( vw_ProductBrand.BrandName, 'pants'))

It now takes 1 sec again.