Forum

Hi again！ I have one more question： they say to use temporary tables instead of subqueries。。。。。。 Why？ What is the difference？

Hello, Yi Gang!

When you use temporary tables the 1C:Enterprise selects data and keeps it on the hard drive. When it is needed data is loaded and query conditions are applied to this limited amount of data. Thus you optimize your query execution time.

In the addition when you use complicated queries which utilize lots of subqueries and at the same time your roles apply complicated conditions as RLS, the resulting query, which is assembled by 1C:Enterprise, will be too complicated for DBMS, so you will receive an error that the maximum number of tables in query is exceeded.

In the addition you can simplify your query by splitting into several more readable queries by using temporary tables.

Best regards!

> Why？

To understand this, I use this article: <forum rules violation>
I'll try to translate it

Why it's not recommended to use subqueries?

Database Optimizer (regardless of what database you are using) can not always properly optimize such query (with subqueries). In this case, the problem to the optimizer is to choose the right way to join result sets. There are several algorithms for joining two result sets. The choice of the algorithm depends on the number of records which are contained in one and in the other result set. In case if you join two physical tables (subquery is not a physical table), the database can easily determine the amount of data in two result sets by the available statistics. If one of result sets is a subquery then to understand how many records it returns is very difficult. In this case the database can choose wrong query plan of join, so that will lead to a dramatic reduction in the performance of the query.

Rewriting the query with using temporary tables is intended to simplify the database optimizer. In the rewritten query all result sets participating in joins will be physical tables and the database will easily determine the length of each result set. This will allow the database to choose the guaranteed fastest of all possible query plans. Moreover, the database will make the right choice no matter what are the conditions. The rewritten query with temporary tables would work well on any database, this is especially important in the development of portable solutions. In addition, the rewritten query is easier to read, easier to understand and to debug.

It is understood that rewriting the query with temporary tables can lead to some slowdown due to additional expenses: creation of temporary tables. If the database will not be mistaken with the choice of the query plan, it will perform the old query faster than a new one. However, this slowdown will always be negligible. Typically the creation of a temporary table takes a few milliseconds. That is, the delay can not have a significant impact on system performance, and usually can be ignored.

Important! Do not forget to create indexes for temporary tables. The index fields should include all fields that are used in join conditions.

Very useful information！ Thank you very much！