Before version 15.0, Adaptive Server optimized joins by considering permutations of two to four times at a time. In version 15.0, the query processor is not limited in this way when considering permutations. Instead, the new search engine introduces a timeout mechanism to avoid excessive optimizing time. The table count setting discussed later in this section still has an effect on the initial join order looked at by the search engine, and thus affects the final join order when timeout does occur. If you suspect that an inefficient join order is being chosen when the search engine times out, you can still use the set table count option to increase the number of tables that are considered, which will affect the initial join order considered by the search engine in starting the permutation.
Adaptive Server optimizes joins by considering permutations of two to four tables at a time. If you suspect that an inefficient join order is being chosen for a join query, use the set table count option to increase the number of tables that are considered at the same time. The syntax is:
set table count int_value
Valid values are 0 though 8; 0 restores the default behavior.
For example, to specify 4-at-a-time optimization, use:
set table count 4
dbcc traceon(310) reports the number of tables considered at a time. See “dbcc traceon(310) and final query plan costs” on page 189 in the Performance and Tuning: Monitoring and Analyzing for Performance book for more information.
As you decrease the value, you reduce the chance that the query processor will consider all the possible join orders. Increasing the number of tables considered at one time during join ordering can greatly increase the time it takes to optimize a query.
Since the time it takes to optimize the query is increased with each additional table, the set table count option is most useful when the execution savings from improved join order outweighs the extra optimizing time. Some examples are:
If you think that a more optimal join order can shorten total query optimization and execution time, especially for stored procedures that you expect to be executed many times once a plan is in the procedure cache
When saving abstract plans for later use
Use statistics time to check parse and compile time and statistics io to verify that the improved join order is reducing physical and logical I/O.
If increasing the table count produces an improvement in join optimization, but increases the CPU time unacceptably, rewrite the from clause in the query, specifying the tables in the join order indicated by showplan output, and use forceplan to run the query. Your routine performance maintenance checks should include verifying that the join order you are forcing still improves performance.
