SQL Server 2008 Full-Text Search

The following is a very good article which covers the improvements to Full-Text Search in SQL Server 2008.


SQL Server 2008 Full-Text Search

Although there is little in the way of new functionality, Microsoft has fully integrated Full-Text Search with the SQL Server Database Engine. In previous versions of SQL Server Full-Test Search was a separate Service (MSFTESQL). This change significantly improves the performance of Full-Text Searches. The following example from the article clearly explains why.

SELECT candidate_ID FROM Candidates
WHERE CONTAINS (Resume,”SQL Server”)
AND candidate_division=534

Assume that in this example, the table named Candidates contains a large number of candidates (rows) containing the phrase ‘SQL Server’, but only a few of this belong to the division 534A. You would expect that the query plan for this query would first find the candidates belonging to that specific division, and then use those results to find who has SQL Server experience. This would be far more effective than using the reverse order.

Full-text search in SQL Server 2000 and 2005 cannot operate this way because it is not integrated with the SQL Server query processor. When this type of query ran, the query processor had no statistics from full-text search to determine the optimal approach, nor did it have a way to push predicates/IDs to the external Full-Text Engine. Thus, previous releases of full-text search had no other solution than to send the full-text predicate to the Full-Text Engine (outside), and then wait for the results to come in order to filter them by a more selective predicate. This is very inefficient.

Advertisements

Tags: , , ,

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s


%d bloggers like this: