Full-Text Search Performance
SQL Server FTS performance is most sensitive to the number of rows in the result set and number of search terms in the query. You should limit your result set to a practical number; most searchers are conditioned to look only at the first page of results for what they are looking for, and if they don’t see what they need there, they refine the search and search again. A good practical limit for the number of rows to return is 200. You should try, if at all possible, to use simple queries because they perform better than more complex ones. As a rule, you should use CONTAINS rather than FREETEXT because it offers better performance, and you should use CONTAINSTABLE rather than FREETEXTTABLE for the same reason.
Get Microsoft® SQL Server 2008 R2 Unleashed now with the O’Reilly learning platform.
O’Reilly members experience books, live events, courses curated by job role, and more from O’Reilly and nearly 200 top publishers.
