При разработке отчетов на платформе 1С:Предприятие часто возникает задача расширить структуру отчета, добавив в нее документы-регистраторы, связанные с определенным объектом (например, договором контрагента). Типичный сценарий: в отчете уже выводится группировка по договорам, и нам необходимо сделать так, чтобы пользователь мог раскрыть эту группировку и увидеть конкретные документы, которые сделали движения по данному договору. Однако прямолинейное добавление поля Регистратор из виртуальных таблиц регистра бухгалтерии часто приводит к тому, что построение отчета начинает занимать критически много времени. Выясним причину этой проблемы и разберем по шагам, как оптимизировать такой запрос.
Рассмотрим ситуацию, с которой столкнулся автор темы. В исходном запросе выполняется левое соединение справочника специальностей с регистром сведений, а затем внутреннее соединение с виртуальной таблицей остатков и оборотов регистра бухгалтерии:
ВЫБРАТЬ
Специальности.Ссылка КАК ОбразовательнаяПрограмма,
ЕПСБУОстаткиИОбороты.Субконто4 КАК НаправлениеДеятельности,
СведенияОСтудентах.Договор.Ссылка КАК Договор,
СведенияОСтудентах.Договор.Контрагент КАК Контрагент,
ЕПСБУОстаткиИОбороты.Регистратор КАК Регистратор
ИЗ
Справочник.Специальности КАК Специальности
ЛЕВОЕ СОЕДИНЕНИЕ РегистрСведений.СведенияОСтудентах КАК СведенияОСтудентах
ПО Специальности.Ссылка = СведенияОСтудентах.Специальность
ВНУТРЕННЕЕ СОЕДИНЕНИЕ РегистрБухгалтерии.ЕПСБУ.ОстаткиИОбороты(&НачалоПериода, &КонецПериода, Регистратор, , , , ) КАК ЕПСБУОстаткиИОбороты
ПО (ЕПСБУОстаткиИОбороты.Субконто3 = СведенияОСтудентах.Договор)
СГРУППИРОВАТЬ ПО
Специальности.Ссылка,
ЕПСБУОстаткиИОбороты.Субконто4,
СведенияОСтудентах.Договор.Ссылка,
СведенияОСтудентах.Договор.Контрагент,
ЕПСБУОстаткиИОбороты.Регистратор
Этот запрос содержит сразу несколько критических уязвимостей с точки зрения производительности СУБД. Проанализируем их:
ОстаткиИОбороты при детализации до регистратора вынуждает SQL-сервер рассчитывать остатки (как начальный, так и конечный) на момент времени абсолютно каждой проводки за выбранный период. СУБД строит сложнейшие запросы с вычислением нарастающих итогов, задействуя базу временных таблиц tempdb. Если документов в базе много, выполнение такого запроса может занять часы.СведенияОСтудентах.Договор.Ссылка избыточна, так как поле Договор уже является ссылкой. Лишнее разыменование (обращение через точку к реквизитам ссылочного поля) заставляет СУБД выполнять неявные соединения таблиц, что сильно замедляет работу.Если в отчете требуется вывести только документы (регистраторы), сделавшие движения по договору, рассчитывать остатки на каждый момент времени не имеет смысла. Достаточно использовать виртуальную таблицу Обороты вместо ОстаткиИОбороты. Физически таблица оборотов устроена значительно проще, и получение данных из нее происходит в разы быстрее, так как СУБД не вычисляет входящие и исходящие итоги по каждой операции.
Для ускорения работы виртуальной таблицы обязательно нужно передавать массив используемых видов субконто в качестве пятого параметра. Рассмотрим пример настройки параметров в коде 1С:
// Заполняем массив субконто и передаем его в запрос как параметр
ВидыСубконто = Новый Массив;
ВидыСубконто.Добавить(ПланыВидовХарактеристик.ВидыСубконтоХозрасчетные.ДоговорыКонтрагентов);
ВидыСубконто.Добавить(ПланыВидовХарактеристик.ВидыСубконтоХозрасчетные.НаправленияДеятельности);
Запрос.УстановитьПараметр("ВидыСубконто", ВидыСубконто);
После передачи этого массива в параметры виртуальной таблицы, порядок субконто в запросе жестко фиксируется. Теперь мы можем использовать поля Субконто1 и Субконто2, не опасаясь, что на разных счетах они находятся на разных позициях:
ВЫБРАТЬ
Т.Субконто1 КАК Договор,
Т.Субконто2 КАК НаправлениеДеятельности,
Т.Регистратор КАК Регистратор
ИЗ
РегистрБухгалтерии.ЕПСБУ.Обороты(&НачалоПериода, &КонецПериода, Регистратор, , &ВидыСубконто, , ) КАК Т
Этот подход исключает из анализа СУБД все проводки по счетам, у которых нет указанных видов субконто, что существенно снижает нагрузку на дисковую подсистему сервера.
Наиболее быстрым и масштабируемым решением является предварительный отбор нужных данных во временную таблицу (ВТ). Мы можем обратиться напрямую к физической таблице значений субконто регистра бухгалтерии (или к таблице движений), отобрать только уникальные пары «Регистратор — Договор» за нужный период, проиндексировать их и лишь затем соединить с основными данными отчета.
Проанализируем пример запроса, реализующего данный подход:
ВЫБРАТЬ РАЗЛИЧНЫЕ
Субконто.Регистратор КАК Регистратор,
Субконто.Значение КАК Договор
ПОМЕСТИТЬ ВтСубконто
ИЗ
РегистрБухгалтерии.ЕПСБУ.Субконто КАК Субконто
ГДЕ
Субконто.Период МЕЖДУ &НачалоПериода И &КонецПериода
И Субконто.Вид = ЗНАЧЕНИЕ(ПланВидовХарактеристик.ВидыСубконтоХозрасчетные.ДоговорыКонтрагентов)
ИНДЕКСИРОВАТЬ ПО
Договор
;
////////////////////////////////////////////////////////////////////////////////
ВЫБРАТЬ
Специальности.Ссылка КАК ОбразовательнаяПрограмма,
СведенияОСтудентах.Договор КАК Договор,
СведенияОСтудентах.Договор.Контрагент КАК Контрагент,
ВтСубконто.Регистратор КАК Регистратор
ИЗ
Справочник.Специальности КАК Специальности
ЛЕВОЕ СОЕДИНЕНИЕ РегистрСведений.СведенияОСтудентах КАК СведенияОСтудентах
ПО Специальности.Ссылка = СведенияОСтудентах.Специальность
ВНУТРЕННЕЕ СОЕДИНЕНИЕ ВтСубконто КАК ВтСубконто
ПО СведенияОСтудентах.Договор = ВтСубконто.Договор
За счет использования временной таблицы ВтСубконто с индексом по полю Договор, СУБД выполняет соединение мгновенно. Основной запрос больше не обращается к тяжелым виртуальным таблицам регистра бухгалтерии.
При работе с регистром бухгалтерии и выводом документов-регистраторов следует соблюдать еще несколько архитектурных правил платформы 1С:
Счет. Если выполнять запрос к таблице субконто или движений без указания конкретного счета (или списка счетов) в условиях, СУБД не сможет использовать существующие индексы и будет вынуждена делать полное сканирование таблицы (Table Scan/Index Scan). Рекомендуется всегда накладывать ограничение на счета расчетов (например, счета 205, 302 для госсектора или 60, 62, 76 для хозрасчета).РегистрБухгалтерии.ЕПСБУ) в современных конфигурациях работает быстрее, так как не требует соединения с таблицей субконто на уровне SQL-сервера.Регистратор имеет составной тип данных, включающий десятки и сотни видов документов. Если в запросе или настройках компоновки данных (СКД) происходит обращение к реквизитам регистратора через точку (например, Регистратор.Дата или Регистратор.Организация), СУБД на уровне SQL выполнит левое соединение (LEFT JOIN) со всеми таблицами этих документов. Это мгновенно снизит скорость работы до нуля. Во избежание этого выводите только саму ссылку на регистратор либо используйте явное ограничение типа с помощью функции ВЫРАЗИТЬ.ДвиженияССубконто. Она спроектирована специально для выборки проводок и их субконто без расчета итогов, что делает ее гораздо более производительной по сравнению с таблицами остатков и оборотов.ПериодСекунда (а также начальный и конечный остатки одновременно). Без поля ПериодСекунда СКД не сможет корректно упорядочить документы внутри одной секунды, что приведет к неверным итогам в группировках.Применяя данные подходы (использование таблицы Обороты вместо ОстаткиИОбороты, обязательное использование временных таблиц с индексацией и ограничение по видам субконто и счетам), вы сможете добиться мгновенного формирования отчетов даже на базах данных объемом в сотни гигабайт.