Contents Index Sorting query results Monitoring database performance

ASA SQL User's Guide
  Monitoring and Improving Performance

Use of work tables in query processing


Work tables are materialized temporary result sets that are created during the execution of a query. Work tables are used when Adaptive Server Anywhere determines that the cost of using one is less than alternative strategies. Generally, the time to fetch the first few rows is higher when a work table is used, but the cost of retrieving all rows may be substantially lower in some cases if a work table can be used. Because of this difference, Adaptive Server Anywhere chooses different strategies based on the OPTIMIZATION_GOAL setting. The default is all-rows. When it is set to first-row, Adaptive Server Anywhere tries to avoid work tables. When it is set to all-rows, Adaptive Server Anywhere uses work tables when they reduce the total execution cost of a query.

Work tables are used in the following cases:

When work tables occur 

In these cases, the records affected by the operation go into the work table. In certain circumstances, such as keyset-driven cursors, a temporary index is built on the work table. The operation of extracting the required records into a work table can take a significant amount of time before the query results appear. Creating indexes that can be used to do the sorting in the first case, above, improves the time to retrieve the first few rows. However, the total time to fetch all rows may be lower if work tables are used, since these permit query algorithms based on hashing and merge sort. These algorithms use sequential I/O, which is faster than the random I/O used with an index scan.

The query optimizer in the database server analyzes each query to determine whether a work table will give the best performance. Enhancements to the optimizer in new releases of Adaptive Server Anywhere may improve the access plan for queries. No user action is required to take advantage of these optimizations.

Notes 

The INSERT, UPDATE and DELETE cases above are usually not a performance problem since they are usually one-time operations. However, if problems occur, you may be able to rephrase the command to avoid the conflict and avoid building a work table. This is not always possible.


Contents Index Sorting query results Monitoring database performance