The 15.0 query processor may automatically activate a timeout mechanism to reduce the time spent in the search engine. You can also configure a more aggressive timeout to reduce the amount of procedure cache used by the query processor. The valid values are from 0 to 1000.
You cannot configure Adaptive Server to have no timeout period.
A timeout is common when you have a query with a large number of tables (that is, greater than 3). The disadvantage of a timeout is that it may miss the best plan because it exits early from the optimizer. However, an advantage of a timeout is that it reduces the time spent optimizing the query when query optimization might exceed execution time. For example, in a test using a 64-bit 11.9.3 Adaptive Server, a 12-way join with tablecount set to 12 took 10 minutes to optimize but only 30 seconds to execute. This was was made worse because the optimizer found the optimal plan within the first minute, but spent most of the time searching the 12 factorial (479 million) possible combinations.
Use the set option show on parameter to detect when timeouts occur. If a timeout occurs, the following appears in the diagnostics output:
!! Optimizer has timed out in this opt block !!
Raising the value of the timeout period at the server or session level can hurt other queries, and may consume more resources such as procedure cache, and may increase your compilation time. In general, if you know that a specific stored procedure or query is creating a problem, you may want to force recompilation of it with a higher value for the optimization timeout.
The syntax for setting the timeout period at the server level is:
sp_configure "optimization timeout limit", time_out_period
The syntax for setting the timeout period at the session level is:
set plan opttimeoutlimit time_out_period
At the query level, you can force the timeout by issuing:
select * from table_name plan "(use opttimeoutlimit time_out_period)"
