В практике разработки на платформе 1С:Предприятие часто возникает классическая задача: необходимо получить значение из регистра сведений по системе приоритетов (от самого частного к самому общему правилу). Например, у нас есть регистр настроек округления со следующими измерениями: Организация, Склад, ГруппаНоменклатуры, и ресурсом Округление.
Нам требуется найти наиболее подходящую настройку округления для конкретной строки номенклатуры по следующему алгоритму:
Организация, Склад и ГруппаНоменклатуры.Организация и Склад, а ГруппаНоменклатуры пустая (общее правило для склада).Организация и ГруппаНоменклатуры, а Склад пустой (общее правило для группы товаров в рамках организации).Организации (когда и Склад, и ГруппаНоменклатуры не заполнены).Разберем детально, как решить эту задачу с помощью одного эффективного и быстрого запроса, проанализируем ошибки из обсуждения на форуме и рассмотрим лучшие практики оптимизации СУБД.
Рассмотрим сначала первый вариант решения, предложенный на форуме (сообщение 12). Он основан на создании четырех временных таблиц в пакете запросов и их последующем полном соединении.
ВЫБРАТЬ
ОкруглениеСрезПоследних.Организация КАК Организация,
ОкруглениеСрезПоследних.Склад КАК Склад,
ОкруглениеСрезПоследних.ПапкаНоменклатуры КАК ПапкаНоменклатуры,
ОкруглениеСрезПоследних.Округление КАК Округление
ПОМЕСТИТЬ Таблица_ОргСклНом
ИЗ
РегистрСведений.Округление.СрезПоследних(
&Дата,
Организация = &Организация
И Склад = &Склад
И ПапкаНоменклатуры = &ПапкаНоменклатуры) КАК ОкруглениеСрезПоследних
;
// ... (аналогично создаются еще 3 временные таблицы) ...
Проанализируем этот подход. Выделение четырех отдельных запросов во временные таблицы с последующим ПОЛНЫМ СОЕДИНЕНИЕМ крайне неоптимально по следующим причинам:
Во-первых, это создает огромную избыточную нагрузку на дисковую подсистему сервера СУБД (базу tempdb в MS SQL или pg_temp в PostgreSQL). Вместо быстрого получения одной записи СУБД вынуждена создавать физические временные таблицы на диске, записывать туда данные, а затем выполнять ресурсоемкие операции слияния.
Во-вторых, в параметрах виртуальных таблиц присутствует серьезная ошибка оптимизации: использование конструкции Склад.Ссылка ЕСТЬ NULL. В платформе 1С обращение через точку к реквизитам ссылочного типа (в данном случае к свойству Ссылка) транслируется в SQL-запрос как неявное левое соединение (LEFT JOIN) с таблицей справочника. Это полностью исключает возможность использования индексов регистра сведений СУБД и приводит к полному сканированию таблиц (Table Scan / Index Scan), что критично для производительности больших баз данных.
В-третьих, происходит логическая путаница между понятиями NULL и Пустая ссылка. В базе данных 1С незаполненные измерения ссылочного типа хранятся не как NULL, а как пустые ссылки соответствующего типа. Исключение составляют только составные типы данных. Поэтому проверка на пустое значение должна выполняться с помощью функции ЗНАЧЕНИЕ(), например: ЗНАЧЕНИЕ(Справочник.Склады.ПустаяСсылка).
Гораздо более изящный и лаконичный вариант был предложен в сообщении 21. Рассмотрим его структуру:
ВЫБРАТЬ
РС.Округление КАК Округление,
ВЫБОР
КОГДА РС.Организация = &Организация
И РС.Склад = &Склад
И РС.ГруппаНоменклатуры = &ГруппаНоменклатуры
ТОГДА 1
КОГДА РС.Организация = &Организация
И РС.Склад = &Склад
И РС.ГруппаНоменклатуры = ЗНАЧЕНИЕ(Справочник.ГруппыНоменклатуры.ПустаяСсылка)
ТОГДА 2
КОГДА РС.Организация = &Организация
И РС.Склад = ЗНАЧЕНИЕ(Справочник.Склады.ПустаяСсылка)
И РС.ГруппаНоменклатуры = &ГруппаНоменклатуры
ТОГДА 3
ИНАЧЕ 4
КОНЕЦ КАК Порядок
ПОМЕСТИТЬ ВременнаяТаблица
ИЗ
РегистрСведений.Округление.СрезПоследних(
&Дата,
Организация = &Организация
И (Склад = &Склад ИЛИ Склад = ЗНАЧЕНИЕ(Справочник.Склады.ПустаяСсылка))
И (ГруппаНоменклатуры = &ГруппаНоменклатуры ИЛИ ГруппаНоменклатуры = ЗНАЧЕНИЕ(Справочник.ГруппыНоменклатуры.ПустаяСсылка))
) КАК РС
;
ВЫБРАТЬ ПЕРВЫЕ 1
ВТ.Округление
ИЗ
ВременнаяТаблица КАК ВТ
УПОРЯДОЧИТЬ ПО
ВТ.Порядок ВОЗР
Выясним преимущества этого подхода. Он задействует всего одну временную таблицу и выполняет логику ранжирования на стороне СУБД с помощью конструкции ВЫБОР КОГДА.... Мы присваиваем каждому результату числовой "вес" (приоритет от 1 до 4), а затем с помощью ключевого слова ПЕРВЫЕ 1 и сортировки по возрастанию приоритета моментально получаем единственную нужную запись.
Однако и здесь кроется подводный камень оптимизации. Обратим внимание на параметры виртуальной таблицы СрезПоследних. Использование логического оператора ИЛИ (OR) в условиях фильтрации СУБД часто сбивает с толку планировщик запросов. СУБД не всегда может эффективно построить план запроса с использованием индексов (Index Seek) при наличии ИЛИ, особенно если статистика по таблице не актуализирована. Вместо этого СУБД может прибегнуть к сканированию всей таблицы, что вызовет просадку производительности при высокой частоте вызова этого запроса.
Для достижения максимального быстродействия и гарантированного использования индексов СУБД на уровне "железа" наилучшим решением является объединение простых запросов с помощью оператора ОБЪЕДИНИТЬ ВСЕ (UNION ALL). Разберем этот алгоритм по шагам.
Каждая ветка объединения представляет собой максимально простой запрос к виртуальной таблице с использованием только операторов И (равенство конкретным значениям). СУБД выполняет мгновенный точечный поиск по индексам (Index Seek) для каждого блока отдельно, а затем быстро склеивает результаты.
Посмотрим на пример реализации такого оптимального запроса:
ВЫБРАТЬ ПЕРВЫЕ 1
Результаты.Округление КАК Округление
ИЗ
(ВЫБРАТЬ
1 КАК Приоритет,
ОкруглениеСрезПоследних.Округление КАК Округление
ИЗ
РегистрСведений.Округление.СрезПоследних(
&Дата,
Организация = &Организация
И Склад = &Склад
И ГруппаНоменклатуры = &ГруппаНоменклатуры) КАК ОкруглениеСрезПоследних
ОБЪЕДИНИТЬ ВСЕ
ВЫБРАТЬ
2,
ОкруглениеСрезПоследних.Округление
ИЗ
РегистрСведений.Округление.СрезПоследних(
&Дата,
Организация = &Организация
И Склад = &Склад
И ГруппаНоменклатуры = ЗНАЧЕНИЕ(Справочник.ГруппыНоменклатуры.ПустаяСсылка)) КАК ОкруглениеСрезПоследних
ОБЪЕДИНИТЬ ВСЕ
ВЫБРАТЬ
3,
ОкруглениеСрезПоследних.Округление
ИЗ
РегистрСведений.Округление.СрезПоследних(
&Дата,
Организация = &Организация
И Склад = ЗНАЧЕНИЕ(Справочник.Склады.ПустаяСсылка)
И ГруппаНоменклатуры = &ГруппаНоменклатуры) КАК ОкруглениеСрезПоследних
ОБЪЕДИНИТЬ ВСЕ
ВЫБРАТЬ
4,
ОкруглениеСрезПоследних.Округление
ИЗ
РегистрСведений.Округление.СрезПоследних(
&Дата,
Организация = &Организация
И Склад = ЗНАЧЕНИЕ(Справочник.Склады.ПустаяСсылка)
И ГруппаНоменклатуры = ЗНАЧЕНИЕ(Справочник.ГруппыНоменклатуры.ПустаяСсылка)) КАК ОкруглениеСрезПоследних) КАК Результаты
УПОРЯДОЧИТЬ ПО
Результаты.Приоритет ВОЗР
Проанализируем, почему этот вариант работает быстрее всех других решений:
ОБЪЕДИНИТЬ ВСЕ работает с конкретными константными значениями в параметрах среза последних. Для SQL-сервера это идеальный сценарий поиска по индексу регистра.ОБЪЕДИНИТЬ ВСЕ (вместо обычного ОБЪЕДИНИТЬ) крайне важно: оно отключает внутреннюю проверку СУБД на уникальность строк (исключает неявную операцию DISTINCT), что экономит оперативную память и процессорное время.tempdb, работая целиком "на лету" в оперативной памяти сервера.Описанный выше подход с конструкцией ВЫБРАТЬ ПЕРВЫЕ 1 идеально подходит для получения настройки по одному конкретному объекту (например, при расчете округления для одной выбранной строки в интерактивном режиме).
Однако если перед нами стоит задача обработать массив данных (например, рассчитать округление для всех 500 строк табличной части проводимого документа), то вызывать такой запрос в цикле категорически запрещено по стандартам разработки 1С. В таком случае необходимо использовать пакетный запрос с последовательным левым соединением.
Рассмотрим, как правильно решить задачу для списка номенклатуры и складов, предварительно помещенных во временную таблицу ВТ_ТоварыДокумента:
ВЫБРАТЬ
Товары.НомерСтроки КАК НомерСтроки,
Товары.Организация КАК Организация,
Товары.Склад КАК Склад,
Товары.ГруппаНоменклатуры КАК ГруппаНоменклатуры,
ЕСТЬNULL(ОкруглениеПр1.Округление,
ЕСТЬNULL(ОкруглениеПр2.Округление,
ЕСТЬNULL(ОкруглениеПр3.Округление,
ЕСТЬNULL(ОкруглениеПр4.Округление, 0)
)
)
) КАК Округление
ИЗ
ВТ_ТоварыДокумента КАК Товары
// Приоритет 1: Полное совпадение всех трех параметров
ЛЕВОЕ СОЕДИНЕНИЕ РегистрСведений.Округление.СрезПоследних(&Дата, ) КАК ОкруглениеПр1
ПО Товары.Организация = ОкруглениеПр1.Организация
И Товары.Склад = ОкруглениеПр1.Склад
И Товары.ГруппаНоменклатуры = ОкруглениеПр1.ГруппаНоменклатуры
// Приоритет 2: Организация + Склад (группа пустая)
ЛЕВОЕ СОЕДИНЕНИЕ РегистрСведений.Округление.СрезПоследних(&Дата, ГруппаНоменклатуры = ЗНАЧЕНИЕ(Справочник.ГруппыНоменклатуры.ПустаяСсылка)) КАК ОкруглениеПр2
ПО Товары.Организация = ОкруглениеПр2.Организация
И Товары.Склад = ОкруглениеПр2.Склад
// Приоритет 3: Организация + Группа (склад пустой)
ЛЕВОЕ СОЕДИНЕНИЕ РегистрСведений.Округление.СрезПоследних(&Дата, Склад = ЗНАЧЕНИЕ(Справочник.Склады.ПустаяСсылка)) КАК ОкруглениеПр3
ПО Товары.Организация = ОкруглениеПр3.Организация
И Товары.ГруппаНоменклатуры = ОкруглениеПр3.ГруппаНоменклатуры
// Приоритет 4: Только Организация (склад и группа пустые)
ЛЕВОЕ СОЕДИНЕНИЕ РегистрСведений.Округление.СрезПоследних(&Дата,
Склад = ЗНАЧЕНИЕ(Справочник.Склады.ПустаяСсылка)
И ГруппаНоменклатуры = ЗНАЧЕНИЕ(Справочник.ГруппыНоменклатуры.ПустаяСсылка)) КАК ОкруглениеПр4
ПО Товары.Организация = ОкруглениеПр4.Организация
Проанализируем этот пакетный запрос. С помощью каскадной конструкции ЕСТЬNULL мы последовательно проверяем наличие связи с таблицами приоритетов от 1 до 4. Если на самом высоком уровне приоритета 1 значение не найдено (результат соединения возвращает NULL), то функция ЕСТЬNULL переходит к проверке уровня приоритета 2, затем 3, и наконец — дефолтного уровня 4. Если настройки нет вообще, вернется значение по умолчанию (в нашем примере — 0).
Использование этого метода при проведении документов гарантирует стабильную, быструю и предсказуемую работу системы даже при пиковых нагрузках предприятия.