SQL Full Text Query Across Multiple Tables

Part of my job is updating and maintaining some legacy web applications. Most of the applications were written back when our network consisted of 6 laptops, and we never imagined we would grow so big. For this reason most of the applications were not designed with scalability in mind. This is an example of just such a oversight, where an SQL query seemingly worked fine for 200 records, but now that we have 10,000 we are seeing some serious issues.

Lately, people have been complaining that one of our web applications was timing out during searches. Since we are disconnected from the internet, we thought that setting the full text service property that forces signature verification to off (according to http://support.microsoft.com/kb/915850) might help. When this produced no improvement, we rebuilt the full text catalogs, which produced slightly better results, but we were still timing out for a fair number of the queries.

Upon further investigation I found that the query itself was running fine, it was just taking over 30 seconds, which is the default timeout for the SqlCommand.CommandTimeout property. In other words SQL was still running the query, but since it took longer than 30 seconds it automatically throws the error that the server has timed out or is unavailable, but really it’s the webpage itself that has caused the timeout.

An SQL query that takes over 30 seconds is indicative of inefficiency, especially considering the hardware we are running. Digging into the code, I found that a query was being constructed that searched the full text catalogs by using the sql CONTAINS clause sewn together by OR statements. So for each column in the fulltext catalog there was a separate CONTAINS statement, adding up to a grand total of 5. I narrowed this down to 3 CONTAINS statements by grouping columns from the same table into individual CONTAINS. It was still extremely slow. Then I broke out the query for each table, thinking I might give the user the ability to expand the search manually. When I did this I noticed that each query ran almost instantly (~0.01 sec).

So the solution was to run the query on each table individually, then use a UNION to combine the results! On the production database this took the query from 66.956 sec down to 0.116 sec. What a difference!

If you ever happen upon a forum where you get the advice to use an WHERE CONTAINS() OR CONTAINS() statement to query for the fulltext catalogs across multiple tables, make sure you set them straight!