Why is the selection faster than the conditions in the query in a dynamic list, and is this true at all?

Question

1C

Good afternoon.
There is processing with many selection fields for 100 thousand items of the nomenclature.
There are two ideas :
1. Constant change of the dynamic list query (add conditions or remove them, depending on the data in these fields);
2. Use type selection.
Familiar with all hands for the first point, but its implementation slows down the wildest. But the typical 1C-ovsky list selection with the same filters works very well.
Why is this happening and what is the difference between the two methods? Bad request implementation?? Is there information about this somewhere?
Thank you for the clarification!

Answer 1

The solution rests on the text of the request. Options why the condition is slow:
1) Subqueries are used for the condition
2) OR is used in the query
3) Query conditions for non-indexed fields, which requires a scan in the DBMS query plan.
The first option (with changing the dynamic list query text) is stupid, don't do it. Changing the source of a dynamic request (apart from the cost of this change with re-initiating the layout settings) causes the cache of the dynamic list to be flushed on the server side. In my practice there was such an experience, but then I really had different sources. If you can solve everything with standard tackles, then just do it!

Answer 2

There is another option if Wirth is used. register tables eg: instead of setting parameters for virtual tables, "WHERE" conditions are used. Which are superimposed after all data is selected from the virtual table