Click here to view and discuss this page in DocCommentXchange. In the future, you will be sent there automatically.

SQL Anywhere 12.0.1 » UltraLite - Database Management and Reference » UltraLite performance tips » Query performance tips » Execution plans in UltraLite » Determining the access method used by the optimizer


Manage temporary tables

In general, the optimizer always tries to avoid creating temporary tables to return query results because the entire temporary table must be populated before the first row can be returned. If an index exists, the optimizer tries to use the index first and only creates a temporary table as a last resort.

A temporary table is used by an access plan to store data during its execution in a transient or temporary work table. This table only exists while the access plan is being executed. Generally, temporary tables are used when intermediate results do not fit in the available memory, such as:

  • When subqueries need to be evaluated early in the access plan.

  • When data in a temporary table is held for a single connection only.

  • When a query contains an ORDER BY on a column other than an index.

  • When a query contains a GROUP BY on a column other than an index.

It is difficult to anticipate whether an index you have created avoids the necessity for a temporary table. Therefore, you should always check the plans for a query to ensure the indexes you have created are actually being used by the UltraLite query optimizer.

You can avoid using temporary tables by using an index for the columns used in the ORDER BY or GROUP BY clauses.

 See also

Direct page scans