Ensuring the Correct Join Order for Queries

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:

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 10, “Advanced Optimizing Techniques,” in the Performance and Tuning Guide.