ASA SQL User's Guide
Monitoring and Improving Performance
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 a query has an ORDER BY, GROUP BY, OR DISTINCT clause and Adaptive Server Anywhere does not use an index for sorting the rows. If a suitable index exists and the OPTIMIZATION_GOAL setting is first-row, Adaptive Server Anywhere avoids using a work table. However, when OPTIMIZATION_GOAL is set to all-rows, it may be more expensive to fetch all the rows of a query using an index than it is to build a work table and sort the rows. Adaptive Server Anywhere chooses the cheaper strategy if the optimization goal is set to all-rows. For GROUP BY and DISTINCT, the hash-based algorithms use work tables, but are generally more efficient when fetching all the rows out of a query.
When a hash join algorithm is chosen, work tables are used to store interim results (if the input doesn't fit into memory) and a work table is used to store the results of the join.
When a cursor is opened with sensitive values, a work table is created to hold the row identifiers and primary keys of the base tables. This work table is filled in as rows are fetched from the query in the forward direction. However, if you fetch the last row from the cursor, the entire table is filled in.
When a cursor is opened with insensitive semantics, a work table is populated with the results of the query when the query is opened.
When a multiple-row UPDATE is being performed and the column being updated appears in the WHERE clause of the update or in an index being used for the update.
When a multiple-row UPDATE or DELETE has a subquery in the WHERE clause that references the table being modified.
When performing an INSERT from a SELECT statement and the SELECT statement references the insert table.
When performing a multiple row INSERT, UPDATE, or DELETE, and a corresponding trigger is defined on the table that may fire during the operation.
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.
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.