Как ускорить выполнение сложных запросов в файловой базе 1С?

← На главную

Нередко разработчики сталкиваются с ситуацией, когда запрос, выполняющийся в клиент-серверном варианте (SQL) за считанные секунды, в файловой базе 1С (файл 1CD) зависает на минуту и более. Разберем, почему файловый движок платформы ведет себя иначе, какие ошибки в тексте запроса приводят к резкому падению производительности и как оптимизировать запрос для быстрой работы.

Особенности файлового движка 1С: почему SQL быстрее

В полноценных СУБД (таких как MS SQL Server или PostgreSQL) оптимизатор запросов обладает развитыми механизмами: он строит планы выполнения с использованием алгоритмов Hash Join или Merge Join, параллелит операции и кэширует промежуточные данные в оперативной памяти. В файловом режиме встроенный механизм СУБД 1С устроен проще:

Использование вложенных циклов (Nested Loops): при соединении таблиц без индексов файловый движок выполняет последовательный перебор строк (Table Scan), что при больших объемах данных приводит к катастрофическому замедлению.
Работа с временными таблицами: промежуточные временные таблицы сохраняются в файлы на локальном диске пользователя (каталог %TEMP%). Без явных индексов каждое соединение с такой таблицей требует полного чтения с диска.

Главное решение: индексация временных таблиц и разбиение соединений

Проанализируем практический пример. В тяжелом запросе связывались остатки по ячейкам, товарам, ценам и штрихкодам. После помещения результатов во временные таблицы соединения происходили без указания индексов:


ВЫБРАТЬ
    ТоварыНаСкладах.Номенклатура,
    ТоварыНаСкладах.Характеристика КАК Характеристика,
    ТоварыНаСкладах.КоличествоОстаток КАК КоличествоОстаток
ПОМЕСТИТЬ ВТ_Остатки
ИЗ
    РегистрНакопления.ТоварыНаСкладах.Остатки(&Период, Склад В (&Склад)) КАК ТоварыНаСкладах
;

Если в дальнейшем эта временная таблица соединяется с другими источниками по полям Номенклатура и Характеристика, файловый движок будет многократно сканировать временный файл целиком. Чтобы ускорить работу в десятки раз, применим два правила:

Не связывать более двух-трех таблиц в одном пакете: разделяем громоздкие многоуровневые соединения на последовательные промежуточные временные таблицы.
Всегда индексировать поля соединений: используем конструкцию ИНДЕКСИРОВАТЬ ПО для всех временных таблиц, которые участвуют в последующих соединениях.

Посмотрим на правильное объявление временной таблицы с индексами:


ВЫБРАТЬ
    ТоварыНаСкладах.Номенклатура КАК Номенклатура,
    ТоварыНаСкладах.Характеристика КАК Характеристика,
    ТоварыНаСкладах.КоличествоОстаток КАК КоличествоОстаток
ПОМЕСТИТЬ ВТ_Остатки
ИЗ
    РегистрНакопления.ТоварыНаСкладах.Остатки(&Период, Склад В (&Склад)) КАК ТоварыНаСкладах
ИНДЕКСИРОВАТЬ ПО
    Номенклатура,
    Характеристика
;

Устранение неявных соединений (обращений через точку)

В тексте запроса часто встречаются конструкции вида ВТ_Товары.Номенклатура.Производитель, ВТ_Товары.Номенклатура.Наименование, Ячейка.Владелец. В файловой базе каждое обращение через точку к реквизиту ссылочного типа разворачивается в отдельное неявное ЛЕВОЕ СОЕДИНЕНИЕ со всей таблицей соответствующего справочника.

Разберем, как исправить эту ошибку:

Вместо множественных точек от поля Номенклатура соединяем таблицу справочника Справочник.Номенклатура один раз явно по ссылке.
В условиях отбора исключаем конструкции вроде Ячейка.Ссылка ЕСТЬ NULL — достаточно написать Ячейка ЕСТЬ NULL, чтобы избежать лишнего соединения с таблицей ячеек.

Перенос условий в параметры виртуальных таблиц

Рассмотрим распространенную ошибку при работе с регистрами. Если получить данные через РегистрНакопления.сфТоварыВЯчейках.Остатки(, ), а условия по измерению наложить ниже в блоке ГДЕ, платформа сначала рассчитает остатки по всей базе данных и только потом отфильтрует строки.

Всегда передаем условия отбора непосредственно в параметры виртуальной таблицы:


// Неправильно:
ИЗ
    РегистрНакопления.сфТоварыВЯчейках.Остатки(, ) КАК сфТоварыВЯчейкахОстатки
ГДЕ
    НЕ сфТоварыВЯчейкахОстатки.Характеристика ЕСТЬ NULL

// Правильно:
ИЗ
    РегистрНакопления.сфТоварыВЯчейках.Остатки(&Период, НЕ Характеристика = ЗНАЧЕНИЕ(Справочник.ХарактеристикиНоменклатуры.ПустаяСсылка)) КАК сфТоварыВЯчейкахОстатки

Использование «ОБЪЕДИНИТЬ ВСЕ» вместо «ОБЪЕДИНИТЬ»

При объединении таблиц ключевое слово ОБЪЕДИНИТЬ без модификатора ВСЕ принудительно запускает алгоритм поиска и удаления дубликатов. Это влечет за собой полную сортировку всех объединяемых наборов данных на диске.

Если мы объединяем заведомо непересекающиеся данные (например, товары из наличия и позиции под заказ), всегда пишем:


ВЫБРАТЬ
    ВТ_Остатки.Номенклатура,
    ВТ_Остатки.Количество
ИЗ
    ВТ_Остатки КАК ВТ_Остатки

ОБЪЕДИНИТЬ ВСЕ

ВЫБРАТЬ
    ВТ_Дефектура.Номенклатура,
    ВТ_Дефектура.Количество
ИЗ
    ВТ_Дефектура КАК ВТ_Дефектура
;

Упрощение сложных условий ВЫБОР и ПОДОБНО в секции ГДЕ

Громоздкие вложенные конструкции ВЫБОР КОГДА, а также строковые операции сравнения (например, ПОДОБНО "!%"), размещенные в секции ГДЕ итогового запроса, полностью отключают возможность использования индексов. Платформа переходит в режим тяжелой построчной проверки.

Рекомендуется упрощать такие условия, заменяя вложенные ВЫБОР на базовые логические связки И / ИЛИ, а также выполнять первичную фильтрацию номенклатуры еще на этапе помещения во временные таблицы.

Внешние факторы: накопитель и антивирус

Так как файловая база активно производит операции ввода-вывода при работе с временными файлами запросов, проверим следующие системные настройки:

Исключения антивируса: добавляем каталог с информационной базой и папку временных файлов %TEMP% в исключения антивирусного сканера.
Тип диска: файловая база крайне чувствительна к скорости случайного чтения и записи (IOPS), поэтому размещение базы на быстрых SSD/NVMe-дисках дает ощутимый прирост скорости при выполнении объемных выборок.