SQL Server Full Text Search Optimization

To be honest, there is no elegant way to improve the performance. However, in certain situations you can dramatically improve the performance of a full text query by embedding textual codes within the text column. This will allow you to search both on your embedded textual code and the required search condition. This can reduce the number of rows returned to SQL greatly and change the performance of the full-text query by an order of magnitude.

For example if your data is:

update it to:

now you can rewrite your query to the following:

SELECT top 10 *
FROM properties p
INNER JOIN containstable(properties,'"TYPEFLAT" and "bath"') t
ON p.PropertyID = t.[key]
WHERE p.type = 'flat'

For this example, only 2,000 results will be returned to the Query Optimizer, and from this the top 10 rows will be returned to the user. This may be acceptable performance, however, if we run the following query:

SELECT top 10 *
FROM properties p
INNER JOIN containstable(properties,'"TYPEHOUSE" and "bath"') 
ON p.PropertyID = t.[key]
WHERE p.type = 'HOUSE'

In this example, 330,000 rows will be returned to the Query Optimizer, which means performance will still be poor. However, now that we are filtering the results in the Search Service before they are returned to the Query Optimizer, we can specify the number of results we need by using the top_n_by_rank parameter of the containstable function. So rewritten, this query would look like:

SELECT *
FROM properties p
INNER JOIN containstable(properties,'"TYPEFLAT" and "bath"',10) t
ON p.PropertyID = t.[key]
WHERE p.type = 'flat'

Obviously, you don't want to return the "TYPEFLAT" text in the description column to your database applications, so the final query should look like:

SELECT PropertyID, Type, SubString(description, 9, 9 - LEN(description)) AS description
FROM properties p
INNER JOIN containstable(properties,'"TYPEFLAT" and "bath"',10) t
ON p.PropertyID = t.[key]
WHERE p.type = 'flat'

In this example only 10 rows will be returned the Query Optimizer, so performance should ROCK!  

Obviously you will need to maintain the text code in the full-text column by using triggers, however the overhead in doing so should be minimal when compared to the speed performance gained.




Published with the express written permission of the author. Copyright 2000 Tony Bain.