Adaptive Server optimizes joins by considering permutations of two to four tables at a time, as described in “Costing and optimizing joins”. If you suspect that an inefficient join order is being chosen for a join query, you can 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 optimizer 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.
