How do I improve full-text searching

Question

Hello,

I have a SQL Server 2008 database. This database has 2 tables:

Manufacturer
------------
ID,
Name nvarchar(256)

Product
-------
ID
ManufacturerID
Name nvarchar(256)

My application has a search box. I do not know if the user is going to provide a manufacturer name or a product name. In addition, I'm trying to be a little bit graceful and handle mis-spellings. In an effort to meet this criteria, I'm using the CONTAINSTABLE function. With this function, I have created the following query:

SELECT
  *
FROM
  [Manufacturer] m 
    INNER JOIN [Product] p ON m.[ID]=p.[ManufacturerID]
    INNER JOIN CONTAINSTABLE(Manufacturer, Name, @searchQuery) r ON m.[ID]=r.[Key] 
ORDER BY 
  r.[Rank]

My query is performing VERY SLOW with the CONTAINSTABLE function. Without the second INNER JOIN, the query runs in less than 1 second. With the second INNER JOIN included the query runs in a little over 30 seconds (too long).

Can anyone provide some performance recommendations? I have no clue how to overcome this hurdle.

Thank you,

Answer 1

• Select just the fields you need, and not SELECT *.
• Indexes on m and p are set?