При разработке отчетов и обработок в среде 1С:Предприятие перед разработчиком часто возникает задача: собрать в один список документы разных типов, созданные определенным пользователем или закрепленные за конкретным менеджером. Например, необходимо вывести все заказы покупателей и все реализации товаров, за которые отвечает определенный сотрудник.
Первое решение, которое часто приходит в голову начинающим разработчикам — это связать таблицы документов через конструкцию ЛЕВОЕ СОЕДИНЕНИЕ или ВНУТРЕННЕЕ СОЕДИНЕНИЕ по реквизиту Ответственный. Однако такой подход является грубейшей архитектурной ошибкой, способной полностью заблокировать работу СУБД на реальной базе данных. В этой статье мы подробно разберем, почему прямое соединение документов по ответственному недопустимо, проанализируем возникающие проблемы производительности и рассмотрим правильные, профессиональные способы решения этой задачи.
Попробуем детально разобраться, что происходит на уровне системы управления базами данных (СУБД), когда мы пытаемся связать журнал документов (или таблицу одного документа) с таблицей другого документа по текстовому или ссылочному полю, не являющемуся уникальным ключом связи (например, по реквизиту Ответственный или СоздательДокумента).
Реквизит Ответственный — это обычная ссылка на справочник Пользователи или ФизическиеЛица. Один и тот же менеджер может быть указан в тысячах документов разных видов. При попытке выполнить соединение СУБД пытается сопоставить каждую строку из левой таблицы с каждой строкой из правой таблицы, где совпадает значение этого реквизита. Математически это называется декартовым произведением (Cartesian product) двух множеств по неключевому полю.
Рассмотрим практический пример:
Представим, что в базе данных у менеджера Иванова есть 1 000 проведенных заказов покупателей и 1 000 оформленных расходных накладных (реализаций). Если мы напишем запрос с соединением по ответственному, СУБД сопоставит каждый заказ Иванова с каждой его реализацией. В результате работы запроса система вернет:
1 000 (заказов) * 1 000 (реализаций) = 1 000 000 (один миллион!) строк для одного только сотрудника.
Если в компании работает 10-20 активных менеджеров, объем возвращаемых данных вырастет лавинообразно. Сервер СУБД испытает колоссальную нагрузку, оперативная память будет перегружена, а сама выборка окажется абсолютно бесполезной, поскольку документы свяжутся случайным образом (каждый заказ сцепится со всеми накладными этого менеджера, независимо от того, связаны они по бизнес-логике или нет).
Основное правило разработки в 1С: напрямую соединять таблицы документов имеет смысл только по цепочке их подчиненности (например, по реквизиту Сделка, ДокументОснование) или через сопоставление ключевых полей. Если же перед нами стоит задача просто вывести список всех документов, за которые отвечает менеджер, их нужно объединять не горизонтально (через соединения), а вертикально (через объединение выборок).
Для того чтобы получить единый плоский список документов разных типов по конкретному ответственному, мы должны написать два (или более) независимых запроса к каждой таблице и склеить их результаты по вертикали. Для этого в языке запросов 1С используется конструкция ОБЪЕДИНИТЬ ВСЕ.
При использовании этой конструкции крайне важно соблюдать правила оптимизации и синтаксиса:
ВЫБРАТЬ должен быть идентичным.ОБЪЕДИНИТЬ ВСЕ, а не просто ОБЪЕДИНИТЬ. Оператор ОБЪЕДИНИТЬ без приставки ВСЕ заставляет СУБД тратить значительные ресурсы на проверку уникальности строк и удаление дубликатов из результирующего набора данных. Оператор ОБЪЕДИНИТЬ ВСЕ просто и мгновенно «склеивает» таблицы.Проанализируем пример запроса, решающего нашу задачу:
ВЫБРАТЬ
Заказы.Ссылка КАК Документ,
Заказы.Ответственный КАК Ответственный,
Заказы.Дата КАК Дата,
"Заказ покупателя" КАК ТипДокумента
ИЗ
ЖурналДокументов.ЗаказыПокупателей КАК Заказы
ГДЕ
Заказы.Ответственный = &Ответственный
И Заказы.Дата МЕЖДУ &ДатаНачала И &ДатаОкончания
ОБЪЕДИНИТЬ ВСЕ
ВЫБРАТЬ
Реализации.Ссылка КАК Документ,
Реализации.Ответственный КАК Ответственный,
Реализации.Дата КАК Дата,
"Реализация товаров и услуг" КАК ТипДокумента
ИЗ
Документ.РеализацияТоваровУслуг КАК Реализации
ГДЕ
Реализации.Ответственный = &Ответственный
И Реализации.Дата МЕЖДУ &ДатаНачала И &ДатаОкончания
Посмотрим на результат выполнения такого запроса. Если у менеджера есть 1 000 заказов и 1 000 реализаций, запрос вернет ровно 2 000 строк. СУБД отработает этот запрос за сотые доли секунды, так как выборка будет происходить по прямым индексам таблиц без тяжелых математических операций сопоставления «многие-ко-многим».
Часто в отчетах требуется представить информацию в структурированном виде: сгруппировать все документы по конкретным менеджерам, чтобы в заголовке группировки шел ответственный сотрудник, а под ним раскрывался список его документов (как заказов, так и реализаций).
Для реализации такой структуры в языке запросов 1С применяется конструкция ИТОГИ ПО. Мы можем легко модернизировать наш предыдущий запрос, добавив в его окончание секцию подведения итогов.
Рассмотрим, как будет выглядеть этот запрос:
ВЫБРАТЬ
Заказы.Ссылка КАК Документ,
Заказы.Ответственный КАК Ответственный,
Заказы.Дата КАК Дата
ИЗ
ЖурналДокументов.ЗаказыПокупателей КАК Заказы
ГДЕ
Заказы.Ответственный В (&СписокОтветственных)
ОБЪЕДИНИТЬ ВСЕ
ВЫБРАТЬ
Реализации.Ссылка,
Реализации.Ответственный,
Реализации.Дата
ИЗ
Документ.РеализацияТоваровУслуг КАК Реализации
ГДЕ
Реализации.Ответственный В (&СписокОтветственных)
ИТОГИ ПО
Ответственный
При обходе результатов такого запроса в коде на встроенном языке 1С мы сможем использовать выборку по группировкам. Это позволит построить красивое дерево документов в отчете или форме обработки. Пример обхода такого результата запроса в коде:
ВыборкаОтветственный = РезультатЗапроса.Выбрать(ОбходРезультатаЗапроса.ПоГруппировкам);
Пока ВыборкаОтветственный.Следующий() Цикл
// Выводим строку группировки по менеджеру
Сообщить("Менеджер: " + ВыборкаОтветственный.Ответственный);
ВыборкаДетальныеЗаписи = ВыборкаОтветственный.Выбрать();
Пока ВыборкаДетальныеЗаписи.Следующий() Цикл
// Выводим документы этого менеджера (и заказы, и реализации вперемешку)
Сообщить(" Документ: " + ВыборкаДетальныеЗаписи.Документ + " от " + ВыборкаДетальныеЗаписи.Дата);
КонецЦикла;
КонецЦикла;
Прежде чем приступать к написанию сложных запросов с объединением таблиц, полезно проанализировать структуру метаданных используемой конфигурации. В типовых решениях от фирмы 1С (таких как «Управление торговлей», «ERP Управление предприятием», «Комплексная автоматизация» или «Управление нашей фирмой») уже заложены готовые механизмы интеграции документов — общие журналы.
Например, в конфигурациях часто существуют журналы документов ЖурналДокументов.ДокументыПродаж или ЖурналДокументов.Продажи. В эти журналы на уровне метаданных уже включены и заказы покупателей, и реализации товаров и услуг.
Если нужные нам документы входят в один и тот же журнал, то задача значительно упрощается. Нам не нужно использовать операторы объединения — достаточно написать простой и лаконичный запрос к одной таблице журнала:
ВЫБРАТЬ
ЖурналПродаж.Ссылка КАК Документ,
ЖурналПродаж.Ответственный КАК Ответственный,
ЖурналПродаж.Дата КАК Дата,
ЖурналПродаж.ТипСсылка КАК ТипДокумента
ИЗ
ЖурналДокументов.ДокументыПродаж КАК ЖурналПродаж
ГДЕ
ЖурналПродаж.Ответственный = &Ответственный
И ЖурналПродаж.Дата МЕЖДУ &ДатаНачала И &ДатаОкончания
Преимущества использования общего журнала документов:
При работе с крупными базами данных, где количество документов исчисляется миллионами, даже правильные запросы с ОБЪЕДИНИТЬ ВСЕ могут начать замедляться, если пренебрегать базовыми правилами оптимизации СУБД.
Рассмотрим ключевые настройки, которые необходимо выполнить:
Ответственный или СоздательДокумента, для этих реквизитов в свойствах метаданных документов обязательно должно быть включено свойство Индексировать (или Индексировать с дополнительным упорядочиванием). Без этого СУБД будет вынуждена выполнять полное сканирование таблиц (Table Scan), что приведет к катастрофическому падению скорости работы по мере роста базы данных.ГДЕ) внутри каждого из объединяемых запросов. Никогда не объединяйте полные таблицы во временную таблицу с целью наложить отборы позже. СУБД должна отсекать ненужные данные на самом раннем этапе выборки.&Ответственный, &ДатаНачала). Избегайте динамического формирования текста запроса путем склейки строк с конкретными значениями, так как это мешает СУБД кэшировать планы выполнения запросов.Применяя данные подходы на практике, мы получим быстрое, масштабируемое и безопасное решение для вывода любых связанных списков документов в системе 1С:Предприятие.