Разработка сложных отчетов в системе 1С:Предприятие 8 часто сталкивает программиста с дилеммой: написать понятный, но медленный код, или создать сложный пакетный запрос, который будет летать, но станет трудным в поддержке. Одной из самых коварных проблем является "размножение" или дублирование числовых показателей (сумм, количеств) при использовании соединений в запросах. Разберем по шагам, как уйти от неоптимальных циклов и исправить структуру запроса для получения корректных итогов в Системе компоновки данных (СКД).
Рассмотрим стандартную ситуацию, с которой столкнулся автор темы. В запросе соединяются несколько таблиц: основные начисления, плановые показатели ФОТ и данные о занятости позиций. Когда мы используем ЛЕВОЕ СОЕДИНЕНИЕ, СУБД сопоставляет строки левой таблицы со строками правой. Если в правой таблице для одной строки из левой находится несколько соответствий (связь "один ко многим"), строка левой таблицы будет продублирована столько раз, сколько соответствий нашлось справа. В результате при суммировании ресурсов в СКД мы получаем завышенные цифры — те самые "удвоенные и утроенные суммы".
Вторая проблема — использование циклов вида Пока ДатаНачала < ДатаОкончания Цикл. Выполнение запросов внутри цикла — это один из самых серьезных антипаттернов в 1С. Каждый вызов запроса — это отдельное обращение к серверу СУБД, что порождает колоссальные накладные расходы. Наша задача — перевести логику на рельсы пакетных запросов и работы с массивами данных.
Проанализируем наиболее эффективный метод борьбы с дублированием данных. Вместо того чтобы пытаться соединить "в бок" (через JOIN) разнородные данные (например, Факт и План), мы будем складывать их "вниз" (через ОБЪЕДИНИТЬ ВСЕ), приводя к единой структуре колонок.
Представим, что нам нужно вывести в одной строке Факт и План. Вместо соединения таблиц по сотруднику, мы создадим одну общую таблицу, где в строках с фактом поле "План" будет равно 0, а в строках с планом поле "Факт" будет равно 0. Система компоновки данных сама "схлопнет" эти записи по группировкам, и суммы всегда будут верными.
Посмотрим на пример трансформации запроса:
// Вместо ЛЕВОЕ СОЕДИНЕНИЕ используем ОБЪЕДИНИТЬ ВСЕ
ВЫБРАТЬ
ТЗ.Сотрудник КАК Сотрудник,
ТЗ.Сумма КАК Факт,
0 КАК План
ПОМЕСТИТЬ ИтоговыеДанные
ИЗ
ТЗ КАК ТЗ
ОБЪЕДИНИТЬ ВСЕ
ВЫБРАТЬ
ПлановыеДанные.Сотрудник,
0,
ПлановыеДанные.Размер
ИЗ
РегистрСведений.ПлановыеНачисления.СрезПоследних(&Дата, ) КАК ПлановыеДанные
;
Такой подход гарантирует, что ни одна сумма не будет умножена на количество записей из другой таблицы. Это делает отчет надежным и легко масштабируемым.
Использование МенеджераВременныхТаблиц (МВТ) позволяет разбить сложный алгоритм на понятные этапы. Рассмотрим подробнее, как правильно организовать работу с временными таблицами (ВТ):
Сотрудник, Подразделение). Это критично для производительности.NULL. Используйте конструкцию ВЫРАЗИТЬ(0 КАК ЧИСЛО(15,2)).Проанализируем ситуацию с виртуальными таблицами. В исходном коде автора используется СрезПоследних внутри соединения. Это "тяжелая" операция. Выясним причину: СУБД вынуждена выполнять сложный подзапрос для получения актуальной записи для каждой строки основной таблицы.
Как сделать правильно: Сначала выгружаем нужный срез в отдельную временную таблицу с отбором (например, по организации), индексируем её, и только потом соединяем с основной выборкой.
Если вам нужно получить данные за несколько периодов (например, помесячно за год), не используйте цикл в коде 1С. Вместо этого передайте в запрос массив дат или используйте условие В ИЕРАРХИИ. Если нужно заполнить "дырки" в периодах, где не было оборотов, используйте вспомогательную таблицу периодов.
Рассмотрим пример подготовки данных без цикла:
// Подготавливаем таблицу периодов заранее и передаем в запрос
ВЫБРАТЬ
Периоды.ДатаНачала,
Периоды.ДатаОкончания
ПОМЕСТИТЬ ВТ_Периоды
ИЗ
&ТаблицаПериодов КАК Периоды
;
// Теперь одним запросом получаем данные сразу за все месяцы
ВЫБРАТЬ
ВТ_Периоды.ДатаНачала КАК Период,
Данные.Сотрудник,
СУММА(Данные.Сумма) КАК Сумма
ИЗ
ВТ_Периоды КАК ВТ_Периоды
ЛЕВОЕ СОЕДИНЕНИЕ РегистрНакопления.Начисления КАК Данные
ПО Данные.ПериодРегистрации МЕЖДУ ВТ_Периоды.ДатаНачала И ВТ_Периоды.ДатаОкончания
ГРУППИРОВАТЬ ПО
ВТ_Периоды.ДатаНачала,
Данные.Сотрудник
Иногда "оптимальный код" — это отсутствие кода. Система компоновки данных позволяет подключать несколько независимых наборов данных. Вы можете создать один "Набор данных - запрос" для факта и второй для плана. Затем на закладке "Связи наборов данных" настройте их соединение по сотруднику и периоду.
Преимущества этого метода:
Чтобы понять, насколько ваш код оптимален, всегда используйте Замер производительности (меню Отладка — Замер производительности). Обратите внимание на колонку "Кол-во вызовов". Если вы видите значение 10 000 для строки с запросом, значит, ваш запрос выполняется в цикле, и это точка роста.
Также помните про инструмент "Консоль запросов" с возможностью просмотра плана выполнения SQL. Это поможет увидеть, на каких этапах СУБД тратит больше всего времени (например, сканирование всей таблицы вместо использования индекса).
Для создания действительно качественного отчета придерживайтесь следующих правил:
МенеджерВременныхТаблиц для структурирования логики.ОБЪЕДИНИТЬ ВСЕ вместо сложных соединений, чтобы избежать раздувания итогов.Соблюдение этих принципов позволит вам писать код, который будет не только быстро работать, но и останется понятным для коллег, которым предстоит поддерживать ваши разработки в будущем.