Как оптимизировать медленный запрос к регистру бухгалтерии "Хозрасчетный" для получения себестоимости и цены реализации?

← На главную

При работе с базами данных 1С, особенно в крупных конфигурациях или при накоплении значительного объема данных, разработчики часто сталкиваются с проблемой медленного выполнения запросов. Одной из наиболее распространённых причин такой ситуации является неоптимальное обращение к регистрам бухгалтерии, таким как РегистрБухгалтерии.Хозрасчетный, который содержит огромное количество движений. В этой статье мы подробно рассмотрим, почему запросы к этому регистру могут выполняться долго, и как их эффективно оптимизировать на примере получения себестоимости и цены реализованных товаров.

Анализируем причину медленного выполнения запроса

Предположим, у нас есть запрос, который выполняется около минуты в файловой базе, и есть опасения, что на SQL-сервере он может занять ещё больше времени. Изначальный запрос выглядит следующим образом:


ВЫБРАТЬ 
    ХозрасчетныйДвиженияССубконто.Регистратор, 
    ХозрасчетныйДвиженияССубконто.СчетДт, 
    ХозрасчетныйДвиженияССубконто.СубконтоКт1 КАК СубконтоКт1, 
    ХозрасчетныйДвиженияССубконто.СубконтоКт2 КАК СубконтоКт2, 
    СУММА(ХозрасчетныйДвиженияССубконто.Сумма) КАК Сумма, 
    ХозрасчетныйДвиженияССубконто.КоличествоКт 
ИЗ 
    РегистрБухгалтерии.Хозрасчетный.ДвиженияССубконто КАК ХозрасчетныйДвиженияССубконто 
ГДЕ 
    ХозрасчетныйДвиженияССубконто.СубконтоКт1 = &СубконтоКт1 
    И ХозрасчетныйДвиженияССубконто.Регистратор.Контрагент = &Контрагент 
    И ХозрасчетныйДвиженияССубконто.Содержание = &Содержание 
СГРУППИРОВАТЬ ПО 
    ХозрасчетныйДвиженияССубконто.КоличествоКт, 
    ХозрасчетныйДвиженияССубконто.Регистратор, 
    ХозрасчетныйДвиженияССубконто.СубконтоКт2, 
    ХозрасчетныйДвиженияССубконто.СубконтоКт1, 
    ХозрасчетныйДвиженияССубконто.СчетДт

Давайте выделим ключевые проблемы этого запроса, которые приводят к его медленной работе:

Отсутствие ограничения по периоду: Виртуальная таблица ДвиженияССубконто по умолчанию будет выбирать все движения с начала существования базы, если не указать параметры периода. Это огромный объем данных.
Фильтрация в секции ГДЕ основного запроса: Условия отбора ХозрасчетныйДвиженияССубконто.СубконтоКт1 = &СубконтоКт1, ХозрасчетныйДвиженияССубконто.Регистратор.Контрагент = &Контрагент и ХозрасчетныйДвиженияССубконто.Содержание = &Содержание применяются уже после того, как платформа 1С или СУБД извлекла большой объем данных из регистра. Это значительно замедляет работу.
Обращение к реквизиту регистратора через точку (Регистратор.Контрагент) вне параметров виртуальной таблицы: Конструкция ХозрасчетныйДвиженияССубконто.Регистратор.Контрагент = &Контрагент заставляет СУБД для каждой записи регистра обращаться к соответствующему документу-регистратору, чтобы получить его контрагента. Это приводит к множеству отдельных операций чтения и не позволяет эффективно использовать индексы, "кладя" при этом базу данных.

Оптимизируем запрос: переносим условия в параметры виртуальной таблицы

Ключевым принципом оптимизации запросов к регистрам 1С является максимально ранняя фильтрация данных. Это достигается путём указания условий отбора непосредственно в параметрах виртуальных таблиц. Таким образом, СУБД ещё на этапе формирования данных для виртуальной таблицы будет использовать эти условия, значительно сокращая объём обрабатываемой информации.

Рассмотрим, как это сделать по шагам:

Шаг 1: Добавляем период и переносим простые условия

Прежде всего, крайне важно всегда указывать период для виртуальных таблиц регистров. Даже если вам нужны данные "с начала базы", подумайте, действительно ли это так. Чаще всего требуется определённый диапазон дат. В нашем случае, мы покажем, как это делается, даже если в итоге вы оставите "с начала базы" для своих уникальных требований. Также перенесём условие по СубконтоКт1.


ВЫБРАТЬ 
    ХозрасчетныйДвиженияССубконто.Регистратор, 
    ХозрасчетныйДвиженияССубконто.СчетДт, 
    ХозрасчетныйДвиженияССубконто.СубконтоКт1 КАК СубконтоКт1, 
    ХозрасчетныйДвиженияССубконто.СубконтоКт2 КАК СубконтоКт2, 
    СУММА(ХозрасчетныйДвиженияССубконто.Сумма) КАК Сумма, 
    ХозрасчетныйДвиженияССубконто.КоличествоКт 
ИЗ 
    РегистрБухгалтерии.Хозрасчетный.ДвиженияССубконто(
        &НачалоПериода, // Первый параметр: Начало периода
        &КонецПериода,  // Второй параметр: Конец периода
        ,               // Третий параметр: Счет (пропускаем, если не нужен)
        ,               // Четвертый параметр: Вид субконто Дт
        СубконтоКт1 = &СубконтоКт1 // Пятый параметр: Условия на субконто Кт
    ) КАК ХозрасчетныйДвиженияССубконто 
СГРУППИРОВАТЬ ПО 
    ХозрасчетныйДвиженияССубконто.КоличествоКт, 
    ХозрасчетныйДвиженияССубконто.Регистратор, 
    ХозрасчетныйДвиженияССубконто.СубконтоКт2, 
    ХозрасчетныйДвиженияССубконто.СубконтоКт1, 
    ХозрасчетныйДвиженияССубконто.СчетДт

Параметры &НачалоПериода и &КонецПериода должны быть заданы программно. Если действительно нужны все движения с начала базы, то можно передать минимальную дату (например, Дата(1,1,1)) в &НачалоПериода и максимальную дату (например, Дата(9999,12,31)) в &КонецПериода, но это всё равно будет менее эффективно, чем указание конкретного периода.

Шаг 2: Фильтрация по реквизитам регистратора и типу документа

Наиболее критичная часть первоначального запроса – это ХозрасчетныйДвиженияССубконто.Регистратор.Контрагент = &Контрагент. Мы не можем напрямую использовать эту конструкцию в параметрах виртуальной таблицы. Однако есть несколько способов обойти эту проблему, в зависимости от ситуации:

Если Контрагент является одним из субконто кредита:
Если в вашем плане счетов настроено, что контрагент является, например, субконто для определённого счёта, то вы можете передать его как параметр субконто. В исходном запросе СубконтоКт1 — это номенклатура, а СубконтоКт2 — партия. Если контрагент — это, допустим, СубконтоКт3, то условие будет таким:
```
...
СубконтоКт1 = &СубконтоКт1 И СубконтоКт3 = &Контрагент
...
```
Это самый эффективный способ, если такая настройка существует.

Фильтрация по типу регистратора и его контрагенту с использованием ВЫРАЗИТЬ:

Если контрагент не является субконто, но нам нужен контрагент конкретного типа документа (например, Документ.РеализацияТоваровУслуг), мы можем использовать конструкцию ВЫРАЗИТЬ в параметрах виртуальной таблицы. Это позволит СУБД отфильтровать движения только по регистраторам нужного типа и уже для них получить контрагента.


ВЫБРАТЬ 
    ХозрасчетныйДвиженияССубконто.Регистратор, 
    ХозрасчетныйДвиженияССубконто.СчетДт, 
    ХозрасчетныйДвиженияССубконто.СубконтоКт1 КАК СубконтоКт1, 
    ХозрасчетныйДвиженияССубконто.СубконтоКт2 КАК СубконтоКт2, 
    СУММА(ХозрасчетныйДвиженияССубконто.Сумма) КАК Сумма, 
    ХозрасчетныйДвиженияССубконто.КоличествоКт 
ИЗ 
    РегистрБухгалтерии.Хозрасчетный.ДвиженияССубконто(
        &НачалоПериода, 
        &КонецПериода,  
        ,               
        ,               
        СубконтоКт1 = &СубконтоКт1 
        И Регистратор ССЫЛКА Документ.РеализацияТоваровУслуг // Отбор по типу документа
        И ВЫРАЗИТЬ(ХозрасчетныйДвиженияССубконто.Регистратор КАК Документ.РеализацияТоваровУслуг).Контрагент = &Контрагент 
    ) КАК ХозрасчетныйДвиженияССубконто 
СГРУППИРОВАТЬ ПО 
    ХозрасчетныйДвиженияССубконто.КоличествоКт, 
    ХозрасчетныйДвиженияССубконто.Регистратор, 
    ХозрасчетныйДвиженияССубконто.СубконтоКт2, 
    ХозрасчетныйДвиженияССубконто.СубконтоКт1, 
    ХозрасчетныйДвиженияССубконто.СчетДт

Обратите внимание, что мы сначала отфильтровали по типу регистратора (Регистратор ССЫЛКА Документ.РеализацияТоваровУслуг), а затем уже обращаемся к его реквизиту Контрагент. Это значительно более эффективно, чем обращаться к Контрагенту для каждого регистратора без предварительного отбора.

Шаг 3: Фильтрация по полю "Содержание"

Поле "Содержание" в регистре бухгалтерии часто содержит текстовое описание операции (например, "Реализация товаров"). Это поле также можно использовать для фильтрации в параметрах виртуальной таблицы. Важным нюансом здесь является правильный синтаксис для строковых литералов: необходимо использовать две двойные кавычки для обозначения строки внутри параметра запроса.

Пример:


ВЫБРАТЬ 
    ХозрасчетныйДвиженияССубконто.Регистратор, 
    ХозрасчетныйДвиженияССубконто.СчетДт, 
    ХозрасчетныйДвиженияССубконто.СубконтоКт1 КАК СубконтоКт1, 
    ХозрасчетныйДвиженияССубконто.СубконтоКт2 КАК СубконтоКт2, 
    СУММА(ХозрасчетныйДвиженияССубконто.Сумма) КАК Сумма, 
    ХозрасчетныйДвиженияССубконто.КоличествоКт 
ИЗ 
    РегистрБухгалтерии.Хозрасчетный.ДвиженияССубконто(
        &НачалоПериода, 
        &КонецПериода,  
        ,               
        ,               
        СубконтоКт1 = &СубконтоКт1 
        И Содержание = ""Реализация товаров"" // Обратите внимание на две двойные кавычки
    ) КАК ХозрасчетныйДвиженияССубконто 
СГРУППИРОВАТЬ ПО 
    ХозрасчетныйДвиженияССубконто.КоличествоКт, 
    ХозрасчетныйДвиженияССубконто.Регистратор, 
    ХозрасчетныйДвиженияССубконто.СубконтоКт2, 
    ХозрасчетныйДвиженияССубконто.СубконтоКт1, 
    ХозрасчетныйДвиженияССубконто.СчетДт

Такой подход позволяет СУБД отфильтровать движения по их содержанию ещё до того, как они будут переданы основному запросу.

Получаем дополнительные данные: цену, партию и номенклатуру

Исходная задача помимо себестоимости (которую мы получаем из Хозрасчетный.ДвиженияССубконто) также включает получение цены, номенклатуры и партии. Номенклатура (СубконтоКт1) и партия (СубконтоКт2) уже есть в нашем запросе. Вопрос в том, откуда взять цену.

Обращение напрямую к табличным частям документов-регистраторов (например, Документ.РеализацияТоваровУслуг.Товары) не всегда является лучшим решением, поскольку данные в табличной части документа могут быть изменены после проведения, а регистры сохраняют состояние на момент проведения. Более надёжным способом является использование других регистров, например, РегистрНакопления.Продажи или РегистрСведений.ЦеныНоменклатуры.

Предлагаем рассмотреть объединение (JOIN) виртуальной таблицы РегистрБухгалтерии.Хозрасчетный.ДвиженияССубконто с виртуальной таблицей РегистрНакопления.Продажи.Обороты.

Почему РегистрНакопления.Продажи? Этот регистр обычно хранит информацию о проданных товарах, их количестве, сумме и часто себестоимости. Если данные в этом регистре актуальны и корректны (в отличие от вашего случая с переносом из 7.7, где возникли проблемы с себестоимостью по партиям), он является хорошим источником информации о продажах.

Предположим, что регистр Продажи содержит нужную нам цену. Объединим наш текущий запрос с ним:


ВЫБРАТЬ 
    ХозрасчетныйДвиженияССубконто.Регистратор, 
    ХозрасчетныйДвиженияССубконто.СчетДт, 
    ХозрасчетныйДвиженияССубконто.СубконтоКт1 КАК Номенклатура, 
    ХозрасчетныйДвиженияССубконто.СубконтоКт2 КАК Партия, 
    СУММА(ХозрасчетныйДвиженияССубконто.Сумма) КАК Себестоимость, 
    ПродажиОбороты.Цена КАК Цена, // Получаем цену из регистра Продажи
    ХозрасчетныйДвиженияССубконто.КоличествоКт 
ИЗ 
    РегистрБухгалтерии.Хозрасчетный.ДвиженияССубконто(
        &НачалоПериода, 
        &КонецПериода,  
        Счет = &СчетКредита, // Указываем конкретный счет, если нужен
        ,               
        СубконтоКт1 = &Номенклатура // Параметр для номенклатуры
        И Регистратор ССЫЛКА Документ.РеализацияТоваровУслуг 
        И ВЫРАЗИТЬ(ХозрасчетныйДвиженияССубконто.Регистратор КАК Документ.РеализацияТоваровУслуг).Контрагент = &Контрагент
        И Содержание = ""Реализация товаров""
    ) КАК ХозрасчетныйДвиженияССубконто 
        ЛЕВОЕ СОЕДИНЕНИЕ РегистрНакопления.Продажи.Обороты(
            &НачалоПериода, 
            &КонецПериода, 
            Регистратор, 
            Номенклатура = &Номенклатура 
            И Характеристика = ХозрасчетныйДвиженияССубконто.СубконтоКт2 // Если партия это характеристика
            И Контрагент = &Контрагент // Возможно, Продажи тоже хранят контрагента
        ) КАК ПродажиОбороты 
        ПО ХозрасчетныйДвиженияССубконто.Регистратор = ПродажиОбороты.Регистратор 
        И ХозрасчетныйДвиженияССубконто.СубконтоКт1 = ПродажиОбороты.Номенклатура 
        // И ХозрасчетныйДвиженияССубконто.СубконтоКт2 = ПродажиОбороты.Характеристика (если СубконтоКт2 это характеристика)
СГРУППИРОВАТЬ ПО 
    ХозрасчетныйДвиженияССубконто.КоличествоКт, 
    ХозрасчетныйДвиженияССубконто.Регистратор, 
    ХозрасчетныйДвиженияССубконто.СубконтоКт2, 
    ХозрасчетныйДвиженияССубконто.СубконтоКт1, 
    ХозрасчетныйДвиженияССубконто.СчетДт,
    ПродажиОбороты.Цена

В этом примере мы используем ЛЕВОЕ СОЕДИНЕНИЕ, чтобы получить все движения из Хозрасчетный, даже если для них нет записей в Продажи.Обороты. Условия соединения включают Регистратор и Номенклатуру (которая у нас СубконтоКт1). Если СубконтоКт2 представляет собой характеристику или партию, по которой можно связать записи, её также можно добавить в условие соединения.

Общие принципы оптимизации запросов к регистрам бухгалтерии

Подведём итоги и сформулируем общие рекомендации, которые помогут вам писать эффективные запросы:

Используйте параметры виртуальных таблиц: Всегда стремитесь перенести условия отбора (период, счет, субконто, регистратор, вид движения) из секции ГДЕ в параметры виртуальной таблицы. Это позволяет СУБД фильтровать данные на самом раннем этапе.
Ограничивайте период: Никогда не оставляйте параметры периода пустыми, если это не строго необходимо. Даже для "всех данных с начала базы" используйте явные параметры даты.
Избегайте обращения к реквизитам регистратора через точку: Конструкции типа Регистратор.Реквизит, особенно в секции ГДЕ, крайне неэффективны. Используйте отбор по типу регистратора (Регистратор ССЫЛКА ТипДокумента) и, при необходимости, ВЫРАЗИТЬ для доступа к реквизитам внутри параметров виртуальной таблицы.
Используйте индексы: Понимайте, какие поля регистров индексируются, и старайтесь строить запросы таким образом, чтобы СУБД могла их использовать. Параметры виртуальных таблиц обычно хорошо работают с индексами.
Выбирайте правильный регистр: Для получения специфической информации (например, цены, остатки по складам) часто существуют специализированные регистры накопления или сведений, которые более оптимизированы для этих целей, чем общий бухгалтерский регистр.
Объединяйте таблицы: Если вам нужны данные из нескольких источников, используйте соединения (ЛЕВОЕ СОЕДИНЕНИЕ, ВНУТРЕННЕЕ СОЕДИНЕНИЕ) между виртуальными таблицами регистров.
Используйте конструктор запросов: Встроенный конструктор запросов 1С позволяет отлаживать запросы, видеть, как они строятся, и получать планы выполнения, что помогает выявлять "узкие места".

Разница между файловым и клиент-серверным вариантом 1С

Важно понимать, что в файловом варианте 1С все операции с базой данных выполняет сама платформа, что накладывает определённые ограничения на производительность. В клиент-серверном варианте база данных управляется полноценной СУБД (MS SQL Server, PostgreSQL и т.д.), которая обладает мощными механизмами оптимизации запросов, кэширования и распараллеливания. Правильно составленный запрос, который максимально эффективно использует индексы и фильтрацию на уровне СУБД, будет работать значительно быстрее на SQL-сервере.

Однако, некорректно составленный запрос может работать ещё дольше на SQL-сервере, поскольку он может потребовать больше ресурсов сервера для обработки большого объема данных, прежде чем применить фильтрацию. Поэтому оптимизация запросов критична для обоих вариантов работы, но особенно проявляет себя на больших клиент-серверных базах.

Применяя эти методы, мы сможем значительно сократить время выполнения сложных запросов к регистру бухгалтерии, обеспечив более быструю и стабильную работу системы 1С.