The more tables and text indexes that are listed in a join, the greater the chance that the query will run slowly because of incorrect join order. Queries run fastest when the text index is queried first during a join between the text index and one or more tables.
To ensure correct join order:
Make sure that a unique clustered or nonclustered index is created on the IDENTITY column of the table being indexed
Limit joins to one base table and one text index
If a query is running slowly, use showplan or enable trace flag 11205, and examine the join order. Trace flag 11205 dumps remote queries to the Adaptive Server error log file. The fastest queries contain an index_any search condition in the where clause and query the text index first.
The slowest queries contain the id column in the text index where clause and query the indexed table first. In this case, rewrite the query or use forceplan to force the join order that is listed in your query. For more information about forceplan, see Chapter 3, “Advanced Optimizing Techniques,” in the Performance and Tuning Guide: Optimizer and Abstract Plans.
