In versions earlier than 15.0, Adaptive Server optimized joins by considering two to four permutations at a time. Versions 15.0 and later do not limit the query processor to two or four permutations. Instead, the new search engine introduces a timeout mechanism to avoid excessive time spent optimizing a query. The set table count setting discussed later in this section still affects 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, use set table count to increase the number of tables that are considered, which affects the initial join order considered by the search engine in starting the permutation.
Adaptive Server still optimizes joins by considering permutations of two to four tables at a time, but if you suspect that an inefficient join order is being chosen for a join query, use set table count to increase the number of tables that are considered at the same time:
set table count int_value
Valid values are 0 though 8; 0 restores the default behavior.
For example, to specify four-at-a-time optimization, use:
set table count 4
As you decrease the value, you reduce the chance that the query processor considers all 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, set table count 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 unacceptably increases CPU time, 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. Be sure that your routine performance maintenance checks include verifying that the join order you are forcing still improves performance.
