FreeText COUNT Query On Multiple Tables Is Super Slow

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 a

Solution 1:

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.

Solution 2:

Have you tried something like:

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

Solution 3:

I ran into a similar problem but i fixed it with union, something like:

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

UNION

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