В процессе разработки и сопровождения систем на базе 1С:Предприятие, особенно таких нагруженных, как УПП или ERP, программисты часто сталкиваются с проблемой низкой производительности отчетов, использующих данные бухгалтерского учета. Рассмотрим типичную ситуацию: запрос к виртуальной таблице ДвиженияССубконто выполняется недопустимо долго. Выясним причины такого поведения системы и разберем по шагам методы оптимизации, которые позволят ускорить получение данных в десятки раз.
Проанализируем исходный запрос, который вызывает затруднения. Основная задача — получить суммы оплат по конкретному счету (аналитике) с детализацией по регистраторам.
ВЫБРАТЬ
Рег.Регистратор,
Рег.Период КАК Период,
СУММА(Рег.ВалютнаяСуммаДт) КАК ВалютнаяСуммаДт,
СУММА(Рег.Сумма) КАК Сумма
ИЗ
РегистрБухгалтерии.Хозрасчетный.ДвиженияССубконто(, , Субконто1 = &Счет, , ) КАК Рег
СГРУППИРОВАТЬ ПО
Рег.Регистратор,
Рег.Период
УПОРЯДОЧИТЬ ПО
Период
В данном коде мы видим несколько критических ошибок, которые заставляют СУБД выполнять избыточную работу. Разберем их подробнее:
ДвиженияССубконто не заполнены, 1С обращается ко всей исторической глубине данных регистра. Даже если в итоге фильтр Субконто1 = &Счет отсечет лишнее, системе сначала придется просканировать огромный объем данных или индексов.ДвиженияССубконто является наиболее ресурсоемкой, так как она динамически соединяет основную таблицу движений с таблицей значений субконто.Первым делом мы должны ограничить выборку по времени. В большинстве бизнес-задач отчеты строятся за определенный интервал. Даже если нам нужна "вся история", всегда эффективнее задать дату начала (например, дату начала ведения учета в базе или дату создания конкретного документа-основания).
Рассмотрим, как изменить параметры виртуальной таблицы для ускорения поиска. Укажем период и добавим фильтр по счету в параметры, а не в условие ГДЕ. Это позволит оптимизатору запросов использовать правильные индексы.
ВЫБРАТЬ
Рег.Регистратор,
Рег.Период КАК Период,
СУММА(Рег.Сумма) КАК Сумма
ИЗ
РегистрБухгалтерии.Хозрасчетный.ДвиженияССубконто(&НачалоПериода, &КонецПериода, Счет = &НужныйСчет И Субконто1 = &ЗначениеАналитики, , ) КАК Рег
СГРУППИРОВАТЬ ПО
Рег.Регистратор,
Рег.Период
Важный момент: Если вы укажете в параметрах виртуальной таблицы конкретный Счет, система сможет использовать индекс по полям Счет + Субконто1. Без указания счета поиск по субконто превращается в Full Scan (полное сканирование таблицы), что и вызывает "тормоза".
Если нам не нужны детализированные проводки в том виде, в котором их дает ДвиженияССубконто, а требуются только итоговые суммы в разрезе документов, стоит рассмотреть таблицу Обороты. Она работает значительно быстрее, так как обращается к предварительно рассчитанным итогам (если периодичность позволяет) или более легким структурам.
Разберем пример использования таблицы Обороты с периодичностью Регистратор:
ВЫБРАТЬ
Рег.Регистратор,
Рег.Период,
Рег.СуммаОборотДт КАК Сумма
ИЗ
РегистрБухгалтерии.Хозрасчетный.Обороты(&НачалоПериода, &КонецПериода, Регистратор, Счет = &Счет, , Субконто1 = &СчетАналитика, , ) КАК Рег
Такой подход предпочтительнее, так как таблица оборотов оптимизирована для получения суммовых показателей.
В некоторых случаях, особенно на старых версиях платформы или при наличии специфических RLS-ограничений, виртуальные таблицы могут работать неоптимально из-за сложности генерируемого SQL-кода. Тогда мы можем обратиться напрямую к физическим таблицам регистра.
Проанализируем ситуацию: виртуальная таблица ДвиженияССубконто внутри представляет собой соединение основной таблицы движений (где лежат суммы и счета) и таблицы значений субконто (где лежит аналитика). Мы можем реализовать это соединение самостоятельно через ВНУТРЕННЕЕ СОЕДИНЕНИЕ, что часто дает выигрыш в скорости, так как исключает лишние проверки и LEFT JOIN, которые платформа вставляет "на всякий случай".
ВЫБРАТЬ
Хозрасчетный.Регистратор КАК Регистратор,
Хозрасчетный.Период КАК Период,
СУММА(Хозрасчетный.Сумма) КАК Сумма
ИЗ
РегистрБухгалтерии.Хозрасчетный КАК Хозрасчетный
ВНУТРЕННЕЕ СОЕДИНЕНИЕ РегистрБухгалтерии.Хозрасчетный.Субконто КАК ХозрасчетныйСубконто
ПО Хозрасчетный.МоментВремени = ХозрасчетныйСубконто.МоментВремени
И Хозрасчетный.НомерСтроки = ХозрасчетныйСубконто.НомерСтроки
ГДЕ
Хозрасчетный.Период МЕЖДУ &НачалоПериода И &КонецПериода
И ХозрасчетныйСубконто.ВидДвижения = ЗНАЧЕНИЕ(ВидДвиженияБухгалтерии.Дебет)
И ХозрасчетныйСубконто.Значение = &НужноеСубконто
СГРУППИРОВАТЬ ПО
Хозрасчетный.Регистратор,
Хозрасчетный.Период
Этот метод позволяет СУБД максимально эффективно использовать индексы по Периоду и Значению субконто. Обратите внимание, что мы используем ВНУТРЕННЕЕ СОЕДИНЕНИЕ, которое сразу отсекает все записи, не соответствующие нашему субконто.
Выясним причину, почему на разных версиях 1С один и тот же запрос может работать с разной скоростью. Начиная с релиза платформы 8.2.15, механизмы работы виртуальных таблиц были существенно переработаны. Если база работает в режиме совместимости с версиями 8.1 или ранними 8.2, система генерирует менее эффективные SQL-запросы.
Рекомендации по настройке системы:
UPDATE STATISTICS. Оптимизатор СУБД может выбрать неверный план (например, сканирование вместо поиска по индексу), если статистика распределения данных устарела.tempdb расположена на быстрых SSD-дисках.Мы рассмотрели несколько способов оптимизации "тяжелого" бухгалтерского отчета. Подведем итог: всегда начинайте с установки периода в параметрах виртуальной таблицы. Если это не помогает, переходите к уточнению условий по счету. В самых сложных случаях используйте физические таблицы регистра и проверяйте технические настройки платформы и SQL-сервера. Совокупность этих мер позволяет добиться стабильной и быстрой работы отчетов даже на базах объемом в несколько терабайт.