User's Guide

Part III. Using SQL Anywhere

Chapter 21. Monitoring and Improving Performance

Sometimes SQL Anywhere needs to make a temporary table for a query. This occurs in the following cases:

When temporary tables occur

• When a query has an ORDER BY or a GROUP BY and SQL Anywhere does not use an index for sorting the rows. Either no index was found or the optimizer chose a strategy that did not use the appropriate index for sorting.
• 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 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, SQL 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 case 1 above will improve the performance of these queries since it will not be necessary to build a temporary table.

Notes

The insert, update and delete cases above are usually not a performance problem since it is usually a one-time operation. However, if it does cause problems, 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 ISQL, the statistics window displays "TEMPORARY TABLE" before the optimization strategy is listed if a temporary table is created by SQL Anywhere in carrying out the search.