User's Guide
PART 4. Database Administration and Advanced Use
CHAPTER 24. Monitoring and Improving Performance
Sometimes Adaptive Server Anywhere needs to make a temporary table for a query. This occurs in the following cases:
When a query has an ORDER BY or a GROUP BY clause and Adaptive Server Anywhere does not use an index for sorting the rows, no suitable index exists.
When a multiple-row UPDATE is being performed and the column being updated is used in the WHERE clause of the update or in an index that is being used for the update.
When a multiple-row UPDATE or DELETE has a subquery in the WHERE clause that references the table that is being modified.
When an INSERT from a SELECT statement is being performed and the SELECT statement references the insert table.
When a multiple row INSERT, UPDATE, or DELETE is performed, and there are triggers defined on the table that the operation causes to fire.
In these cases, Adaptive Server Anywhere makes a temporary table before the operation begins. The records affected by the operation are put into the temporary table and a temporary index is built on the temporary table. This operation of extracting the required records into a temporary table can take a significant amount of time before any rows at all are retrieved from the query. Thus, creating indexes that can be used to do the sorting in first case, above, will improve the performance of these queries since it will not be necessary to build a temporary table.
The INSERT, UPDATE and DELETE cases above are usually not a performance problem since they are usually one-time operation. However, if problems occur, the only thing that can be done to avoid building a temporary table is to rephrase the command to avoid the conflict. This is not always possible.
In Interactive SQL, the Statistics window displays "TEMPORARY TABLE" before the optimization strategy is listed if a temporary table is created by Adaptive Server Anywhere in carrying out the search.