How to speed up heavy queries in 1C: analysis of typical errors

Working with large amounts of data in 1C is a test that every developer faces sooner or later. A query that is executed instantly on a test database can run for minutes in production. Let's analyze typical mistakes and learn how to avoid them.

Mistake #1: Using LIKE instead of exact conditions.

Many novice developers abuse the LIKE operator, especially when they need to find something in a string.

Bad:

Номенклатура.Наименование ПОДОБНО "%Bolt%"

Good:

Номенклатура.Артикул = "12345"

The LIKE operator forces the DBMS to scan the entire table, ignoring the indexes. If you can use an exact match or the beginning of a string, do it. If a substring search is unavoidable, at least avoid a mask with a percent at the beginning.

Error #2: Receiving unnecessary fields.

A classic beginner's mistake is to select all the fields in a row, even if you only need two or three.

Bad:

ВЫБРАТЬ
    *
ИЗ
    Справочник.Контрагенты

Good:

ВЫБРАТЬ
    Контрагенты.Ссылка,
    Контрагенты.Наименование,
    Контрагенты.ИНН
ИЗ
    Справочник.Контрагенты

Sampling unnecessary data increases the load on the network and memory. This is especially critical for fields of the StorageValues type — they can contain megabytes of data.

Error #3: No indexes in the selection criteria.

If you often filter data by a certain field, but forgot to create an index for it, get ready for slow work.

Check which fields you are using in the WHERE and JOIN conditions. Indexes must be created for these fields. In the configurator, this is done through the properties of the attribute by setting the "Index" flag.

Important: index only what is actually used in queries. Excessive indexes slow down write operations.

Mistake #4: Nested queries instead of connections.

Sometimes developers try to get related data through nested queries in SELECT.

Bad:

ВЫБРАТЬ
    Документы.Номер,
    (ВЫБРАТЬ Контрагенты.Наименование 
     ИЗ Справочник.Контрагенты КАК Контрагенты
     ГДЕ Контрагенты.Ссылка = Документы.Контрагент)

Good:

ВЫБРАТЬ
    Документы.Номер,
    Контрагенты.Наименование
ИЗ
    Документ.РеализацияТоваров КАК Документы
    ЛЕВОЕ СОЕДИНЕНИЕ Справочник.Контрагенты КАК Контрагенты
    ПО Документы.Контрагент = Контрагенты.Ссылка

Nested queries are executed for each row of the result. Connections are processed by the DBMS optimizer much more efficiently.

Mistake #5: Ignoring virtual tables.

1C provides powerful virtual tables for working with balances, turnovers and other data slices. Many beginners try to calculate this manually.

Bad: independent calculation of balances through document turnover

Good:

ВЫБРАТЬ
    Остатки.Номенклатура,
    Остатки.КоличествоОстаток
ИЗ
    РегистрНакопления.ТоварыНаСкладах.Остатки КАК Остатки

Virtual tables are already optimized by platform developers and use special caching mechanisms.

Mistake #6: No limits on the number of entries.

When you display data in a form, always limit the number of records.

ВЫБРАТЬ ПЕРВЫЕ 1000
    // query fields

This is especially important for reports and lists. The user rarely needs to see 100 thousand lines at the same time, but the performance suffers greatly from this.

Mistake #7: Complex expressions in conditions.

If you apply functions to fields in WHERE conditions, indexes will not be used.

Bad:

ГДЕ ГОД(Документ.Дата) = 2024

Good:

ГДЕ Документ.Дата МЕЖДУ &НачалоПериода И &КонецПериода

Period parameters are calculated once on the client side, and the query itself can use an index by date.

Error #8: Multiple database calls in a loop.

This is a classic N+1 problem, when you first get a list of items and then make an additional query in a loop for each one.

Bad:

// Getting a list of documents
Для Каждого Документ Из Выборка Цикл
    // For each document - a new request to the database
    Контрагент = ПолучитьКонтрагента(Документ);
КонецЦикла;

Good: combine everything into one query with connections or use temporary tables.

Practical optimization tips.

Use temporary tables.

If the query is complex and intermediate data is used several times, temporary tables can significantly speed up execution.

Analyze query plans.

In enterprise mode, turn on the query console and learn which indexes are used and which are not.

Remember about caching.

Some data can be cached on the client side so as not to pull the database every time.

Use batch operations.

Instead of many small records, make one large transaction.

How to measure performance?

Measure the execution time of queries in combat conditions, on real volumes of data. What works quickly on 100 records can slow down on 100,000.

Use the 1C technology log to search for slow queries. Configure it to record queries that take longer than a certain time (for example, 1 second).

Conclusion.

Optimizing queries in 1C is not a one-time task, but a constant practice. Start with the simple: explicitly specify the required fields, use indexes, avoid LIKE and nested queries. These basic rules will already give a noticeable increase in productivity.

Remember that there is no universal recipe. Each case requires analysis and understanding of how exactly your query works and what can be improved.

Do you want to level up your programming skills?

In Codice you will find courses in Python, JavaScript, HTML, CSS and other popular technologies for beginner developers. Learn at a comfortable pace and create real projects!

And we also have a cool Telegram channel with a friendly community where we discuss programming, share experiences and help each other grow! Join us! 🚀