При разработке внешних отчетов в системе компоновки данных (СКД) начинающие программисты часто сталкиваются с ситуацией, когда, казалось бы, простой запрос намертво вешает систему. В этой статье мы подробно разберем, почему простая замена ЛЕВОГО СОЕДИНЕНИЯ на условия в секции ГДЕ может привести к катастрофическим последствиям для производительности, и как этого избежать.
Рассмотрим ситуацию, описанную автором. В исходном запросе таблицы перечисляются через запятую в секции ИЗ, а условия связи прописаны в секции ГДЕ. Проанализируем этот фрагмент кода:
ИЗ
Документ.РегистрацияДефекта КАК РегистрацияДефекта,
Справочник.ОбъектыЭксплуатации КАК ОбъектыЭксплуатации,
Документ.ЗаказНаРемонт КАК ЗаказНаРемонт
ГДЕ
РегистрацияДефекта.ОбъектЭксплуатации.Ссылка = ОбъектыЭксплуатации.Ссылка
И ЗаказНаРемонт.ОбъектЭксплуатации.Ссылка = ОбъектыЭксплуатации.Ссылка
С точки зрения реляционных баз данных, такое написание запроса инициирует так называемое декартово произведение (Cross Join). Выясним, что происходит «под капотом» SQL-сервера. Система сначала пытается сопоставить каждую строку первой таблицы с каждой строкой второй и каждой строкой третьей таблицы. Если в каждой таблице всего по 1 000 записей, в промежуточном результате (до применения фильтров ГДЕ) получится 1 000 * 1 000 * 1 000 = 1 000 000 000 (один миллиард) строк.
Этот гигантский массив данных система пытается сформировать в оперативной памяти. Именно на этом этапе 1С «зависает». Даже если процессор загружен всего на 10%, это может означать, что одно ядро полностью занято бессмысленным перебором миллиардов комбинаций, а оперативная память стремительно заполняется временными данными СУБД.
Важно понимать фундаментальное различие: конструкция ВНУТРЕННЕЕ СОЕДИНЕНИЕ ... ПО ограничивает выборку данных в момент их считывания. Конструкция ГДЕ накладывает отбор уже после того, как таблицы были соединены. Рассмотрим правильный вариант написания запроса, который предложили участники обсуждения:
ВЫБРАТЬ
ОбъектыЭксплуатации.Ссылка КАК Ссылка,
РегистрацияДефекта.Ссылка КАК РегистрацияДефекта,
МатериалыИРаботы.Номенклатура КАК Номенклатура
ИЗ
Справочник.ОбъектыЭксплуатации КАК ОбъектыЭксплуатации
ВНУТРЕННЕЕ СОЕДИНЕНИЕ
Документ.РегистрацияДефекта КАК РегистрацияДефекта
ПО ОбъектыЭксплуатации.Ссылка = РегистрацияДефекта.ОбъектЭксплуатации
ВНУТРЕННЕЕ СОЕДИНЕНИЕ
Документ.ЗаказНаРемонт КАК ЗаказНаРемонт
ПО ОбъектыЭксплуатации.Ссылка = ЗаказНаРемонт.ОбъектЭксплуатации
При таком подходе SQL-сервер использует индексы и выбирает только те данные, которые соответствуют условию связи. Это сокращает объем обрабатываемой информации на несколько порядков.
Проанализируем еще одну ошибку в запросе: использование конструкции ОбъектыЭксплуатации.Родитель.Наименование. В языке запросов 1С каждое обращение через точку к ссылочному полю порождает неявное левое соединение. Если вы обращаетесь к родителю, а потом к его реквизитам, система создает дополнительную связь с таблицей справочника.
Чем больше таких обращений в полях ВЫБРАТЬ и условиях ГДЕ, тем сложнее становится итоговый SQL-запрос. Рекомендуется выбирать саму ссылку (Родитель), а представление или наименование выводить уже средствами СКД на закладке «Наборы данных» или «Оформление».
В исходном запросе фигурирует обращение к табличной части: ЗаказНаРемонт.МатериалыИРаботы.( Номенклатура ). Разберем, почему это опасно. Табличная часть в базе данных — это отдельная физическая таблица. Когда она просто перечисляется в списке таблиц без явного соединения, она также участвует в декартовом произведении. Если у одного документа 10 строк в табличной части, количество строк в итоговой выборке увеличится еще в 10 раз для каждого документа.
Правильный алгоритм работы с табличными частями:
Ссылка.Рассмотрим профессиональный подход к построению подобных отчетов. Использование документов напрямую в запросах — это «антипаттерн». Документы предназначены для ввода данных, а для отчетности в 1С существуют регистры (накопления или сведений).
Проанализируем, почему лучше использовать регистры:
Если же без документов не обойтись, разберем по шагам использование временных таблиц:
ОбъектыЭксплуатации, подходящие под ваши фильтры (например, по родителю), и помещаем их во временную таблицу ВТ_Объекты.РегистрацияДефекта, соединяясь с ВТ_Объекты. Это сразу отсечет лишние документы. Результат — в таблицу ВТ_Дефекты.Такой подход гарантирует, что на каждом этапе SQL-сервер работает с минимально необходимым набором данных. Индексы по полю Ссылка во временных таблицах позволят соединениям отрабатывать мгновенно.
Выясним разницу в логике результатов. ВНУТРЕННЕЕ СОЕДИНЕНИЕ удалит из отчета объекты эксплуатации, по которым не найдено ни одного дефекта или заказа на ремонт. ЛЕВОЕ СОЕДИНЕНИЕ оставит объект в списке, просто проставив NULL (пустые значения) в колонках документов.
Для отчетов-списков чаще всего используется именно ЛЕВОЕ СОЕДИНЕНИЕ, чтобы пользователь видел полную картину по всем объектам, включая те, по которым еще нет записей в базе. С точки зрения скорости, при наличии правильных индексов, разница между ними минимальна, но логика отчета меняется кардинально.
Подведем итог нашему разбору. Чтобы ваши запросы в 1С работали быстро и не вызывали зависаний, следуйте этим правилам:
СОЕДИНЕНИЕ.ГДЕ. Все связи таблиц должны быть в секции ПО.Соблюдение этих базовых принципов позволит вам создавать стабильные и быстрые отчеты даже на очень больших объемах данных.