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

Программист 1С v8.3 (Управляемые формы) 1C:Бухгалтерия Бухгалтерский учет IT и автоматизация бизнеса
← На главную

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

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

Понимание проблемы: медленные запросы к регистру бухгалтерии

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


ВЫБРАТЬ
    ХозрасчетныйОстаткиИОбороты.Счет,
    ХозрасчетныйОстаткиИОбороты.Субконто1,
    ХозрасчетныйОстаткиИОбороты.Субконто2,
    ХозрасчетныйОстаткиИОбороты.СуммаНачальныйОстатокКт,
    ХозрасчетныйОстаткиИОбороты.СуммаОборотКт,
    ХозрасчетныйОстаткиИОбороты.СуммаКонечныйОстатокКт
ИЗ
    РегистрБухгалтерии.Хозрасчетный.ОстаткиИОбороты(
        &ДатаНачала,
        &ДатаОкончания,
        ,
        ,
        Счет В ИЕРАРХИИ (&Счет6001),
        ,
        &Организация
    ) КАК ХозрасчетныйОстаткиИОбороты

Если такой запрос выполняется долго, особенно на больших объемах данных, нам необходимо искать пути оптимизации. Прежде всего, давайте выясним, в чем может быть причина низкой производительности.

  1. Большой объем данных: Регистр бухгалтерии может содержать миллионы записей за длительный период, и выборка итогов по ним требует значительных ресурсов.
  2. Неэффективное использование итогов: 1С поддерживает специальные механизмы для ускорения получения итогов, но их нужно правильно использовать.
  3. Сложность условий отбора: Использование иерархических условий (В ИЕРАРХИИ) или сложных связей может замедлять запрос.
  4. Отсутствие или неэффективность индексов: Хотя регистры бухгалтерии обычно хорошо индексированы по умолчанию, иногда требуется дополнительная оптимизация.

Рассмотрим несколько подходов к решению этой проблемы.

Решение 1: Использование временных таблиц для агрегации данных

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

Давайте разберем по шагам, как мы можем применить этот подход:

Шаг 1: Получение исходных записей в временную таблицу

Сначала мы выберем необходимые данные из регистра бухгалтерии, но не будем сразу агрегировать их. Вместо этого, мы поместим их в временную таблицу.


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

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

Шаг 2: Агрегация данных из временной таблицы

Теперь, когда у нас есть временная таблицу с исходными движениями, мы можем выполнить агрегацию, используя оператор СГРУППИРОВАТЬ ПО. Это позволит нам получить необходимые остатки и обороты более эффективно.


ВЫБРАТЬ
    ВТ_Движения.Счет,
    ВТ_Движения.Субконто1,
    ВТ_Движения.Субконто2,
    СУММА(ВТ_Движения.Сумма) КАК Оборот
ИЗ
    ВТ_Движения КАК ВТ_Движения
СГРУППИРОВАТЬ ПО
    ВТ_Движения.Счет,
    ВТ_Движения.Субконто1,
    ВТ_Движения.Субконто2

Обратите внимание, что для получения начальных и конечных остатков, а также дебетовых/кредитовых оборотов, нам может потребоваться более сложная логика. Однако, использование временных таблиц для промежуточной агрегации зачастую оказывается быстрее, чем прямой запрос к виртуальной таблице ОстаткиИОбороты на больших объемах.

Решение 2: Использование итоговых таблиц регистра бухгалтерии

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

Шаг 1: Проверка настройки регистра

Убедимся, что для регистра бухгалтерии (например, Хозрасчетный) установлен флаг "Рассчитывать итоги". Эта настройка находится в конфигураторе, в свойствах самого регистра. Без этого флага система не сможет использовать предварительно рассчитанные итоги, и каждый запрос будет выполняться "на лету" по всем движениям.

Шаг 2: Правильное использование виртуальных таблиц

Виртуальные таблицы регистра бухгалтерии (Остатки, Обороты, ОстаткиИОбороты) спроектированы таким образом, чтобы использовать механизм итогов. Однако, есть нюансы в их применении:

  1. Виртуальная таблица Остатки:

    Мы используем эту таблицу, когда нам нужны остатки на определенную дату. Если мы запрашиваем остатки на конец дня, а итоги рассчитаны по дням (что чаще всего и есть), запрос будет быстрым.

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

    Если &ДатаОкончания – это граница итогов (например, конец месяца), запрос будет еще быстрее, так как данные возьмутся из предварительно рассчитанных итогов.

  2. Виртуальная таблица Обороты:

    Эта таблица используется для получения оборотов за период. Она также опирается на механизм итогов.

    
ВЫБРАТЬ
    ХозрасчетныйОбороты.Счет,
    ХозрасчетныйОбороты.Субконто1,
    ХозрасчетныйОбороты.Субконто2,
    ХозрасчетныйОбороты.СуммаОборотКт
ИЗ
    РегистрБухгалтерии.Хозрасчетный.Обороты(
        &ДатаНачала,
        &ДатаОкончания,
        Период,
        Счет В ИЕРАРХИИ (&Счет6001),
        ,
        &Организация
    ) КАК ХозрасчетныйОбороты
  3. Виртуальная таблица ОстаткиИОбороты:

    Как мы уже видели, эта таблица позволяет получить и начальные, и конечные остатки, а также обороты за один запрос. Она отлично подходит для таких задач, как формирование отчета остатки и обороты по счетам с субконто по месяцам.

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

Шаг 3: Объединение запросов для комплексного результата

Иногда выгоднее получить остатки и обороты отдельными запросами, а затем объединить их. Разделение и объединение могут улучшить общую производительность, особенно когда система управления базами данных (СУБД) может параллелизовать выполнение отдельных запросов.

Решение 3: Оптимизация отбора по субконто и использование индексации

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

Понимание индексации субконто

В регистрах бухгалтерии субконто часто индексируются, но порядок следования полей в индексе имеет значение. Важно понимать, что выберет MS SQL при использовании селективных индексов от 1С, так как логика оптимизатора может отличаться от ваших ожиданий. Если мы отбираем данные по Субконто1, Субконто2 и Субконто3, а индекс создан по этим полям, запрос будет быстрым.

Пример оптимизации отбора

Рассмотрим случай, когда нам нужны остатки и обороты по контрагенту (Субконто1) и договору (Субконто2) для определенного счета.


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

Если запрос все равно медленный, следует анализировать SQL сервер глазами 1С-ника, проверяя текущие запросы, ожидания и рекомендации по индексам через специализированные обработки.

Дополнительные рекомендации и анализ

Давайте подытожим и расширим наши знания о принципах оптимизации:

  1. Ограничиваем период запроса: Чем короче период, тем быстрее отрабатывает запрос к виртуальным таблицам.
  2. Минимизируем количество полей: Выбираем только те поля, которые действительно нужны. Это уменьшает объем передаваемых данных.
  3. Использование оператора В ИЕРАРХИИ: Этот оператор может быть ресурсоемким. Если возможно, замените его на список конкретных счетов.
  4. Анализ плана запроса: Всегда используем инструменты для анализа планов запросов. Это позволит нам точно увидеть, какие шаги запроса занимают больше всего времени.
  5. Настройка итогов по периодам: В свойствах регистра бухгалтерии мы можем настроить периодичность расчета итогов (по дням, неделям, месяцам).
  6. Флаг "Рассчитывать итоги": Мы не устаем повторять, что этот флаг должен быть установлен для регистра бухгалтерии.

Рассмотрим пример из практики, когда требуется получить обороты по контрагентам за период без разбивки:


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

Заключение

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

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

← На главную