Scope: managed applications, mobile applications, and ordinary applications.
1. In general, it is recommended that you use temporary tables to improve query performance and consistency. You can also use them for other purposes, for example, to improve the code architecture. Note that it can reduce performance in some cases.
2. In some cases, it is recommended that you do not create temporary tables or minimize their creation.
2.1. Do not create temporary tables with a large data volume (hundreds of thousands of entries). Otherwise, this leads to reduced performance upon writing and insufficient free space on the hard drive. If the algorithm must process a large data volume, make sure that this data is processed in batches.
2.2. You need to minimize the amount of data being included in a temporary table. In the temporary table, do not include more data than it is required for further queries.
2.3. Do not include fields that are not used in further queries in a temporary table as you will just waste time and space.
2.4. Do not create or delete temporary tables in a cycle if you can create one temporary table before executing the cycle.
2.5. Do not copy one temporary table to another one only to rename the first table to the second one. Pass the table name instead.
3. Always index temporary tables if it allows you to improve performance.
3.1. Indexing is reasonable if:
3.1.1. A large temporary table is involved in a join (regardless of on which side). Add fields involved in the BY condition to the index.
3.1.2. A temporary table is called in a subquery of the construct of the logical IN (...) operator. To the index, add fields of the temporary table from a selection list that match fields listed on the left side of the logical IN(...) operator.
See also: Standard 652 ("Inconsistency of indexes and query conditions").
3.2. You do not need to index small temporary tables consisting of less than 1,000 records.
3.3. If there is more than one selection condition or join with a temporary table and only one of them is frequently checked, create the index for the most frequently checked condition.