Big data in 1C: how to speed up work 100 times without crutches

Working with large amounts of data is one of the most common problems faced by 1C developers. Slow reports, processing freezes, and dissatisfied users are all the result of an incorrect approach to working with data. Let's figure out how to do it right.

Why do performance problems occur?

When there are several thousand documents in the database, everything works quickly. But as soon as the data grows to hundreds of thousands or millions of records, the code that used to run instantly starts to work in minutes. The reason is simple: each access to the database takes time, and non-optimized queries can process much more data than necessary.

Basic principles of working with big data

1. Use queries instead of loops

The most common mistake of novice developers is data processing in loops. For example, if you need to get the remaining goods in the warehouse, you should not sort through all the goods in the cycle and make a separate request for each.

❌ Bad:

Для Каждого Товар Из СписокТоваров Цикл
    Запрос = Новый Запрос;
    Запрос.Текст = "SELECT Remainder WHERE Nomenclature = &Nomenclature";
    Запрос.УстановитьПараметр("Nomenclature", Товар);
    // Done hundreds of times!
КонецЦикла;

✅ Good:

Запрос = Новый Запрос;
Запрос.Текст = 
"SELECT
|    Nomenclature,
|    Balance
|FROM RegisterAccumulation.BalanceGoods.Balance
|WHERE Nomenclature IN (&List of Goods)";
Запрос.УстановитьПараметр("List of goods", СписокТоваров);
// Performed once!

Important: One request instead of hundreds is the difference between a second and a minute of work.

2. Limit the data selection

Do not load all fields if they are not needed. Do not select all records if you only need a part.

// Select only the necessary fields
Запрос.Текст = 
"SELECT
|    Document.Number,
|    Document.Date,
| Document.Amount
 | FROM Document.SaleOfGoodsServices AS Document
|WHERE Document.Date BETWEEN &StartDate AND &EndDate
|    AND Document.Posted = TRUE";

Use WHERE conditions to filter data at the database level, not in the code after getting all records.

3. Apply indices

Indexes are what makes searching a database fast. In 1C, indexes are created automatically for attributes marked as indexable. If you often filter data by a specific attribute, make sure that an index is created for it.

4. Use temporary tables

When you need to make several queries that are related to each other, temporary tables help you save intermediate results and not refer to the main tables again.

Запрос = Новый Запрос;
Запрос.Текст = 
"SELECT 
| Counterparty, 
| AMOUNT (Amount) AS TotalAmount
| PLACE InAmountsByCounterparties | FROM Document.SaleOfGoodsServices
| GROUP BY Counterparty |; | | SELECT
|    InAmountsByCounterparties.Counterparty,
|    InAmountsByCounterparties.TotalAmount
| FROM VtAmountsByCounterparties | WHERE VtAmountsByCounterparties.TotalAmount> 100000";

5. Work in packages

If you need to process a million records, do not load them all at once. Process data in batches of 1000-10000 records.

СчётчикПорции = 0;
РазмерПорции = 5000;

Пока Истина Цикл
    Запрос.Текст = 
    "SELECT FIRST " + РазмерПорции + "
    |    Link
    |FROM Reference Book.Nomenclature
    | WHERE Link> & LastLink | SORT BY Link";
    
    Результат = Запрос.Выполнить();
    Если Результат.Пустой() Тогда
        Прервать;
    КонецЕсли;
    
    // Processing the portion
    Выборка = Результат.Выбрать();
    Пока Выборка.Следующий() Цикл
        // Element processing
        ПоследняяСсылка = Выборка.Ссылка;
    КонецЦикла;
КонецЦикла;

6. Use accumulation registers correctly

Accumulation registers are a powerful tool for working with quantitative data. They allow you to quickly get balances and turnovers without having to recalculate the entire history of movements.

// Receipt of balances as of the date
Запрос.Текст = 
"SELECT
|    Remainders.Nomenclature,
|    Remainders.QuantityRemainder
| FROM AccumulationRegister. GoodsInWarehouses. Remains (& Date) AS Remains";

7. Optimize table connections

When JOINs are used in a query, the order and type of the JOIN matter. First, join the tables with the fewest records.

Practical advice

• Analyze query plans. In the 1C query console, you can see exactly how the query is executed and where bottlenecks occur.

• Use performance measurements. Built-in 1C tools allow you to find slow operations. Don't guess, measure!

• Avoid nested queries. Most often, they can be replaced with better performing connections or temporary tables.

• Cache immutable data. If the data changes rarely, save it in the module variables or use the information registers with the possibility of caching.

Common mistakes

• Getting all fields via "SELECT *" instead of explicitly listing the required ones

• Using complex calculations in WHERE conditions instead of preliminary calculation

• Lack of filtering conditions when you can limit the selection

• Multiple calls to the database instead of one batch request

Conclusion

Working with big data in 1C requires an understanding of the principles of database and query operation. The basic rules are simple: minimize the number of database accesses, use queries instead of loops, select only the necessary data and apply the right tools for the right tasks.

Remember: optimization is not a premature complication of the code, but a competent design from the very beginning. The habit of writing effective queries will save you hours of debugging and weeks of optimization in the future.

🚀 Want to know more?

You can explore this and many other topics in Codice — our platform for beginner developers! We have created structured courses that will help you master development from scratch to a confident level.

And we also have cool telegram channel with a friendly community, where experienced developers help beginners, share their experience and discuss current programming issues.

Go to Kodik Join the community