Ситуация, когда отчет или обработка идеально работают на локальной файловой копии, но напрочь «вешают» рабочую клиент-серверную базу, является классической для разработчиков на платформе 1С. Проблема часто кроется не в объеме данных, а в том, как именно SQL-сервер интерпретирует запрос, сформированный платформой. Рассмотрим подробно причины возникновения таких «зависаний» и разберем по шагам методы оптимизации на примере запроса к журналу документов и регистру дополнительных сведений.
Проанализируем фрагмент кода, который стал причиной проблемы в рассматриваемом случае:
ВЫБРАТЬ РАЗРЕШЕННЫЕ
ОбщийЖурнал.Ссылка КАК Ссылка,
...
ДополнительныеСведения.Значение КАК Значение
ИЗ
ЖурналДокументов.ОбщийЖурнал КАК ОбщийЖурнал
ПОЛНОЕ СОЕДИНЕНИЕ РегистрСведений.ДополнительныеСведения КАК ДополнительныеСведения
ПО ОбщийЖурнал.Ссылка = ДополнительныеСведения.Объект.Ссылка
ГДЕ
ОбщийЖурнал.Дата МЕЖДУ &НачалоПериода И &КонецПериода
Главная ошибка здесь скрыта в условии соединения: ОбщийЖурнал.Ссылка = ДополнительныеСведения.Объект.Ссылка. Выясним причину, почему это «зло», как выразились участники обсуждения. Поле Объект в регистре ДополнительныеСведения имеет составной тип (оно может ссылаться на сотни различных видов документов). Когда мы пишем .Ссылка через точку от поля составного типа, платформа 1С при генерации SQL-запроса выполняет операцию разыменования.
На уровне СУБД это превращается в Implicit Join (неявное левое соединение) со всеми таблицами, которые входят в состав этого типа. Если в вашей конфигурации свойство «Дополнительные сведения» разрешено для 50 типов документов, SQL-сервер попытается соединить основную таблицу еще с 50-ю таблицами. Даже если данных мало, построение плана такого запроса может занять колоссальное время, что пользователь видит как «бесконечное колесико».
Решение: Никогда не обращайтесь к .Ссылка от ссылочного поля. Ссылка — это и есть само значение поля. Условие должно выглядеть так: ПО ОбщийЖурнал.Ссылка = ДополнительныеСведения.Объект. Это позволит SQL-серверу сравнивать значения напрямую, не обращаясь к метаданным всех возможных типов документов.
В исходном запросе использовалось ПОЛНОЕ СОЕДИНЕНИЕ. Посмотрим на ситуацию с точки зрения логики: полное соединение пытается сопоставить записи из обеих таблиц, включая те, для которых нет пары. В контексте отчета по журналу документов это практически никогда не оправдано.
Если нам нужно получить данные документов и их свойства, мы используем ЛЕВОЕ СОЕДИНЕНИЕ. Это гарантирует, что мы увидим все документы из журнала, а сведения добавятся только там, где они есть. СУБД (особенно PostgreSQL) гораздо эффективнее обрабатывают левые соединения, так как им проще определить ведущую таблицу и использовать соответствующие индексы. Как было замечено в обсуждении, простая замена типа соединения и устранение лишнего разыменования часто «заставляют запрос летать».
Если база данных большая, а журнал документов содержит десятки типов, даже «правильное» соединение может работать медленно из-за сложности виртуальной таблицы журнала. Рассмотрим более надежный и профессиональный метод — использование временных таблиц. Разберем этот алгоритм по шагам:
ЖурналДокументов с применением всех фильтров по дате, пометке удаления и проведению.Ссылка во временной таблице.Посмотрим на пример кода, реализующего этот подход:
// 1. Выбираем ссылки во временную таблицу
ВЫБРАТЬ
Журнал.Ссылка КАК Ссылка
ПОМЕСТИТЬ ВТ_Документы
ИЗ
ЖурналДокументов.ОбщийЖурнал КАК Журнал
ГДЕ
Журнал.Дата МЕЖДУ &НачалоПериода И &КонецПериода
И Журнал.ПометкаУдаления = ЛОЖЬ
И (Журнал.Проведен = ИСТИНА ИЛИ Журнал.Ссылка ССЫЛКА Документ.ОперацияБух)
ИНДЕКСИРОВАТЬ ПО
Ссылка
;
// 2. Соединяем готовую выборку с данными
ВЫБРАТЬ
ВТ.Ссылка,
ДопСведения.Свойство,
ДопСведения.Значение
ИЗ
ВТ_Документы КАК ВТ
ЛЕВОЕ СОЕДИНЕНИЕ РегистрСведений.ДополнительныеСведения КАК ДопСведения
ПО ВТ.Ссылка = ДопСведения.Объект
Такой подход «разрывает» сложный запрос на несколько простых. SQL-серверу гораздо легче сначала выполнить эффективную выборку из одной таблицы журнала по дате, а затем совершить точечное соединение по индексу.
Проанализируем еще два неочевидных фактора. Первый — ключевое слово РАЗРЕШЕННЫЕ. В клиент-серверном варианте оно активирует механизм RLS (Record Level Security). Если для пользователя настроены ограничения прав (например, доступ только по определенным организациям), платформа 1С автоматически добавит в ваш запрос множество подзапросов для проверки прав. В сочетании с «кривым» соединением через две точки это создает экспоненциальный рост сложности запроса.
Второй фактор — статистика SQL-сервера. СУБД строит план выполнения запроса, опираясь на данные о распределении значений в таблицах. Если в базе БИТ:Финанс давно не выполнялось обслуживание (реиндексация и обновление статистик), SQL-сервер может выбрать крайне неэффективный план, например, Table Scan (полное сканирование таблицы) вместо использования индекса. Именно поэтому в одной базе (УПП) запрос может работать за счет «удачного» накопленного плана, а в другой (БП) — зависать.
Подведем итоги нашего исследования. Для обеспечения стабильной работы запросов в клиент-серверном режиме следует придерживаться следующих правил:
Объект вместо Объект.Ссылка.ЛЕВОЕ.Соблюдение этих принципов позволит избежать «зависаний» и обеспечит одинаково быструю работу ваших отчетов как в файловом, так и в серверном вариантах работы 1С.