How to optimize a SQL Server full text search

Question

I want to use fulltextsearch for an autocomplete service, which means I need it to work fast! Up to two seconds max.

The search results are drawn from different tables and so I created a view that joins them together. The SQL function that I'm using is FREETEXTTABLE().

The query runs very slowly, sometimes up to 40 seconds.

To optimize the query execution time, I made sure the base table has a clustered index column that's an integer data type (and not a GUID)

I have two questions: First, any additional ideas about how to make the full text search faster? (not including upgrading the hardware...) Second, How come each time after I rebuild the full text catalog, the search query works very fast (less then one second), but only for the first run. The second time I run the query it takes a few more seconds and it's all down hill from there.... any idea why this happens?

Answer 1

The reason why your query is very fast the first time after rebuilding the catalog might be very simple:

When you delete the catalog and rebuild it, the indexes have to be rebuilt, which takes some time. If you make a query before the rebuilding is finished, they query is faster, simply because there is less data. You should also notice, that your query-result contains less rows.

So testing the query speed only makes sense after rebuilding of the indexes is finished.

The following select might come handy to check the size (and also fragmentation) of the indexes. When the size stops growing, rebuilding of the indexes is finished ;)

-- Compute fragmentation information for all full-text indexes on the database
SELECT c.fulltext_catalog_id, c.name AS fulltext_catalog_name, i.change_tracking_state,
    i.object_id, OBJECT_SCHEMA_NAME(i.object_id) + '.' + OBJECT_NAME(i.object_id) AS object_name,
    f.num_fragments, f.fulltext_mb, f.largest_fragment_mb,
    100.0 * (f.fulltext_mb - f.largest_fragment_mb) / NULLIF(f.fulltext_mb, 0)  AS fulltext_fragmentation_in_percent
FROM sys.fulltext_catalogs c
JOIN sys.fulltext_indexes i
    ON i.fulltext_catalog_id = c.fulltext_catalog_id
JOIN (
    -- Compute fragment data for each table with a full-text index
    SELECT table_id,
        COUNT(*) AS num_fragments,
        CONVERT(DECIMAL(9,2), SUM(data_size/(1024.*1024.))) AS fulltext_mb,
        CONVERT(DECIMAL(9,2), MAX(data_size/(1024.*1024.))) AS largest_fragment_mb
    FROM sys.fulltext_index_fragments
    GROUP BY table_id
) f
    ON f.table_id = i.object_id

Answer 2

Here's a good resource to check out. However if you really want to improve performance you'll have to think about upgrading your hardware. (I saw a significant performance increase by moving my data and full text index files to separate read-optimized disks and by moving logs and tempdb to separate write-optimized disks -- a total of 4 extra disks plus 1 more for the OS and SQL Server binaries.)

Some other non-hardware solutions I recommend:

1. Customize the built-in stop word list to define more stop words, thereby reducing the size of your full text index.
2. Change the file structure of tempdb. See here and here.
3. If your view performs more than 1 call to FREETEXTTABLE then consider changing your data structure so that the view only has to make 1 call.

However none of these by themselves are likely to be the silver bullet solution you're looking for to speed things up. I suspect there may be other factors here (maybe a poor performing server, network latency, resource contention on the server..) especially since you said the full text searches get slower with each execution which is the opposite of what I've seen in my experience.