Разработчики 1С часто сталкиваются с ситуацией, когда запрос, написанный для файловой базы данных, исправно (хоть и не очень быстро) выполняется на локальном компьютере, но при переносе в клиент-серверный вариант (на MS SQL Server или PostgreSQL) намертво вешает рабочую сессию. В режиме ожидания СУБД сессия переходит в состояние «Не отвечает», а утилизация процессора сервером базы данных стремится к 100%.
Давайте подробно разберем, почему так происходит, какие грубые архитектурные ошибки проектирования запросов приводят к подобному поведению системы, и как правильно переписать этот запрос по шагам, чтобы он выполнялся за доли секунды на любом объеме данных.
Рассмотрим структуру исходного запроса. Автор пытается получить лог изменений документов из регистра сведений ЛогРедактированияДокументов.СрезПоследних за определенный период и соединить его левой связью с табличными частями пяти разных документов (Возврат товаров, Заказ покупателя, Заявка покупателя, Корректировка реализации и Реализация товаров и услуг) для получения цен номенклатуры и торговых представителей.
В файловой базе данных (которая использует собственный облегченный движок СУБД от компании 1С) этот запрос обрабатывается локально и завершается за 2–3 минуты на небольшом объеме в 9 000 строк. Для такого объема это критически долго. На сервере же планировщик СУБД пытается построить оптимальный план выполнения (Execution Plan), но сталкивается с каскадом неоптимальных конструкций. В результате СУБД уходит в бесконечные вложенные циклы (Nested Loops) или выполняет полное сканирование таблиц (Table Scan) вместо поиска по индексам (Index Seek).
Проанализируем ключевые системные ошибки в исходном коде запроса:
ГДЕ (WHERE). Это одна из самых критических ошибок оптимизации. Конструкция вида РегистрСведений.ЛогРедактированияДокументов.СрезПоследних(, ) КАК Срез ГДЕ Срез.Период МЕЖДУ ... заставляет СУБД сначала рассчитать срез последних на текущую дату по ВСЕМУ регистру сведений (миллионы записей), а уже затем отфильтровать полученный огромный результат по периоду. Более того, логика среза последних возвращает только ОДНУ (последнюю) запись. Если документ изменялся несколько раз за период, срез вернет только последнее состояние. Если автору нужны все изменения, использовать виртуальную таблицу СрезПоследних категорически нельзя — нужно обращаться к физической таблице регистра сведений.ОБЪЕДИНИТЬ автор пять раз обращается к виртуальной таблице СрезПоследних. СУБД вынуждена рассчитывать одну и ту же тяжелую выборку пять раз подряд. На это указали участники форума в сообщениях 6 и 11.ЛогРедактированияДокументовСрезПоследних.Товар.ЕдиницаДляОтчетов.Вес под капотом СУБД превращаются в неявные левые соединения (LEFT JOIN) с таблицами справочников. Когда такое разыменование происходит внутри конструкции ОБЪЕДИНИТЬ, количество скрытых связей увеличивается в геометрической прогрессии, лавинообразно увеличивая нагрузку на сервер.ОБЪЕДИНИТЬ вместо ОБЪЕДИНИТЬ ВСЕ. Оператор без ключевого слова ВСЕ заставляет СУБД выполнять неявную сортировку и группировку результирующего набора данных для удаления дубликатов. Это крайне ресурсоемкая операция. Если дубликатов заведомо быть не может или они не критичны, всегда нужно использовать ОБЪЕДИНИТЬ ВСЕ. Более того, если в выборке присутствует поле неограниченной длины (например, многострочное текстовое поле ПричинаИзменения), использование обычного ОБЪЕДИНИТЬ вызовет жесткую ошибку СУБД, так как сравнивать строки неограниченной длины SQL не умеет. Это объясняет ошибку автора в Сообщении 12.Ссылка и Номенклатура СУБД тратит много времени на сопоставление данных.СГРУППИРОВАТЬ ПО без агрегатных функций (типа СУММА или МАКСИМУМ) для устранения дублей в документах — неоптимально. Для этого существует более легкая инструкция РАЗЛИЧНЫЕ.Когда автор последовал совету из сообщения 6 и перенес логи во временную таблицу, а затем попытался объединить ее с временными таблицами документов, он получил ошибку. Скорее всего, это произошло по двум причинам:
Во-первых, несовместимость типов или разное количество полей в ветках объединения. Оператор ОБЪЕДИНИТЬ требует, чтобы во всех объединяемых частях было абсолютно одинаковое количество колонок, расположенных в одном и том же порядке и имеющих совместимые типы данных. Например, если в первой ветке для РТУ выбираются поля РТУ.Цена и РТУ.ТорговыйПредставитель, а в ветке для Возвратов этих полей нет, СУБД вернет ошибку. Для решения этой проблемы в пустых ветках нужно указывать дефолтные значения: 0 КАК Цена и ЗНАЧЕНИЕ(Справочник.ФизическиеЛица.ПустаяСсылка) КАК ТорговыйПредставитель.
Во-вторых, использование текстовых полей неограниченной длины (например, реквизит комментария или лога причин изменений) в конструкции ОБЪЕДИНИТЬ (без ВСЕ) вызывает ошибку СУБД, так как сравнение таких строк невозможно.
Давайте спроектируем правильный, быстрый и масштабируемый пакет запросов. Наша стратегия будет состоять из следующих шагов:
ОБЪЕДИНИТЬ ВСЕ, выравнивая состав полей.Обращаемся к физической таблице регистра сведений. Ограничиваем выборку периодом в секции ГДЕ и обязательно индексируем результат по полям, по которым будем производить дальнейшее соединение (Документ, Товар).
ВЫБРАТЬ
ЛогРедактирования.Период КАК Период,
ЛогРедактирования.Документ КАК Документ,
ЛогРедактирования.Товар КАК Товар,
ЛогРедактирования.Было КАК Было,
ЛогРедактирования.Стало КАК Стало,
ЛогРедактирования.ПричинаИзменения КАК ПричинаИзменения,
ЛогРедактирования.Пользователь КАК Пользователь
ПОМЕСТИТЬ ВТ_Логи
ИЗ
РегистрСведений.ЛогРедактированияДокументов КАК ЛогРедактирования
ГДЕ
ЛогРедактирования.Период МЕЖДУ НАЧАЛОПЕРИОДА(&НачалоПериода, ДЕНЬ) И КОНЕЦПЕРИОДА(&КонецПериода, ДЕНЬ)
ИНДЕКСИРОВАТЬ ПО
Документ,
Товар
;
Для каждого документа создаем свою временную таблицу. Используем ключевое слово РАЗЛИЧНЫЕ для исключения дублирования строк по одной и той же номенклатуре в рамках одного документа (если это необходимо по логике задачи). Обязательно индексируем временные таблицы.
Рассмотрим пример создания таблицы для Реализации товаров и услуг (РТУ):
ВЫБРАТЬ РАЗЛИЧНЫЕ
РеализацияТовары.Ссылка КАК Ссылка,
РеализацияТовары.Номенклатура КАК Номенклатура,
РеализацияТовары.Цена КАК Цена,
РеализацияТовары.Ссылка.ТорговыйПредставитель КАК ТорговыйПредставитель
ПОМЕСТИТЬ ВТ_Документы_РТУ
ИЗ
Документ.РеализацияТоваровУслуг.Товары КАК РеализацияТовары
ГДЕ
РеализацияТовары.Ссылка.Дата МЕЖДУ НАЧАЛОПЕРИОДА(&НачалоПериода, ДЕНЬ) И КОНЕЦПЕРИОДА(&КонецПериода, ДЕНЬ)
ИНДЕКСИРОВАТЬ ПО
Ссылка,
Номенклатура
;
Аналогично создаем временные таблицы для остальных документов (например, для Возврата товаров от покупателя):
ВЫБРАТЬ РАЗЛИЧНЫЕ
ВозвратТовары.Ссылка КАК Ссылка,
ВозвратТовары.Номенклатура КАК Номенклатура,
ВозвратТовары.Цена КАК Цена,
ВозвратТовары.Ссылка.ТорговыйПредставитель КАК ТорговыйПредставитель
ПОМЕСТИТЬ ВТ_Документы_Возврат
ИЗ
Документ.ВозвратТоваровОтПокупателя.Товары КАК ВозвратТовары
ГДЕ
ВозвратТовары.Ссылка.Дата МЕЖДУ НАЧАЛОПЕРИОДА(&НачалоПериода, ДЕНЬ) И КОНЕЦПЕРИОДА(&КонецПериода, ДЕНЬ)
ИНДЕКСИРОВАТЬ ПО
Ссылка,
Номенклатура
;
Такой подход позволяет СУБД быстро сопоставить данные в оперативной памяти благодаря индексам по полям связи.
Теперь мы соединяем подготовленную таблицу логов ВТ_Логи с каждой из таблиц документов. Обратите внимание на выравнивание полей во всех частях запроса с использованием ОБЪЕДИНИТЬ ВСЕ.
ВЫБРАТЬ
Логи.Период КАК Период,
Логи.Документ КАК Документ,
Логи.Товар КАК Товар,
Логи.Было КАК Было,
Логи.Стало КАК Стало,
Логи.ПричинаИзменения КАК ПричинаИзменения,
Логи.Пользователь КАК Пользователь,
ДокРТУ.Цена КАК Цена,
ДокРТУ.ТорговыйПредставитель КАК ТорговыйПредставитель
ПОМЕСТИТЬ ВТ_ОбъединенныеДанные
ИЗ
ВТ_Логи КАК Логи
ВНУТРЕННЕЕ СОЕДИНЕНИЕ ВТ_Документы_РТУ КАК ДокРТУ
ПО Логи.Документ = ДокРТУ.Ссылка
И Логи.Товар = ДокРТУ.Номенклатура
ОБЪЕДИНИТЬ ВСЕ
ВЫБРАТЬ
Логи.Период,
Логи.Документ,
Логи.Товар,
Логи.Было,
Логи.Стало,
Логи.ПричинаИзменения,
Логи.Пользователь,
ДокВозврат.Цена,
ДокВозврат.ТорговыйПредставитель
ИЗ
ВТ_Логи КАК Логи
ВНУТРЕННЕЕ СОЕДИНЕНИЕ ВТ_Документы_Возврат КАК ДокВозврат
ПО Логи.Документ = ДокВозврат.Ссылка
И Логи.Товар = ДокВозврат.Номенклатура
;
При необходимости добавьте сюда аналогичные ветки для Заказов покупателей, Заявок покупателей и Корректировок реализации. Использование ВНУТРЕННЕЕ СОЕДИНЕНИЕ здесь оправдано, так как нам нужны только те логи, которые соответствуют конкретному типу документа в данной ветке объединения.
Чтобы избежать многократных неявных соединений на предыдущих шагах, мы получаем вес, объем и производителя номенклатуры только один раз в самом конце, связав получившуюся временную таблицу ВТ_ОбъединенныеДанные со справочником Номенклатура.
ВЫБРАТЬ
Результат.Период КАК Период,
Результат.Документ КАК Документ,
Результат.Товар КАК Товар,
Результат.Было КАК Было,
Результат.Стало КАК Стало,
Результат.ПричинаИзменения КАК ПричинаИзменения,
Результат.Пользователь КАК Пользователь,
Результат.Цена КАК Цена,
Результат.ТорговыйПредставитель КАК ТорговыйПредставитель,
СпрНоменклатура.ЕдиницаДляОтчетов.Вес КАК Вес,
СпрНоменклатура.ЕдиницаДляОтчетов.Объем КАК Объем,
СпрНоменклатура.Производитель КАК Производитель
ИЗ
ВТ_ОбъединенныеДанные КАК Результат
ЛЕВОЕ СОЕДИНЕНИЕ Справочник.Номенклатура КАК СпрНоменклатура
ПО Результат.Товар = СпрНоменклатура.Ссылка
УПОРЯДОЧИТЬ ПО
Период ДЕБ
;
Переписав запрос по этой методике, мы получаем структурированный, легко читаемый и, главное, максимально производительный код. СУБД больше не будет тратить время на повторные расчеты виртуальной таблицы СрезПоследних и зависать в попытках связать неиндексированные наборы данных. Все операции соединения теперь выполняются мгновенно благодаря индексам во временных таблицах.
Придерживайтесь этого подхода при работе с любыми сложными выборками, объединяющими данные логов, регистров и различных типов документов в системе 1С:Предприятие.