Знакома ли вам ситуация: на тестовой базе отчет формируется за секунды, а в рабочей — намертво зависает? Или по одной организации всё работает быстро, а по другой — программа уходит в глубокое раздумье?
Часто причиной медленной работы отчетов и проведения документов становятся не СУБД или недостаток оперативной памяти, а неоптимально написанные запросы. В этой статье мы на практике разберем 3 классических примера в запросах 1С, которые могут приводить к «тормозам» при выполнении запросов
Тестовый стенд
Замеры в рассмотренных кейсах проводились на мини-конфигурации, где были сгенерированы тестовые данные — сотни тысяч справочников и документов, чтобы разница в производительности была наглядной.
- Справочники:
- Номенклатура (Реквизиты: Артикул — строка, без индекса; АртикулИнд — строка, с индексом; ФизическоеЛицо — ссылка)
- Контрагенты
- ФизическиеЛица
- Сотрудники (Реквизит: ФизическоеЛицо — ссылка на ФизическиеЛица с индексом)
- Правообладатели
- Пользователи
- ЮрЛица
- ВнешниеКонтрагенты
- Партнеры
- Документы:
- РеализацияТоваровУслуг (Реквизит шапки: Покупатель — составной тип: Контрагенты, ФизическиеЛица, Правообладатели, Пользователи, ЮрЛица, ВнешниеКонтрагенты, Партнеры. Табличная часть Товары с реквизитами Номенклатура, Количество, Сумма)
- Регистры накопления:
- ОстаткиТоваров (Вид: Остатки. Измерение: Номенклатура. Ресурс: Количество)
- Продажи (Вид: Обороты. Измерения: Номенклатура, Покупатель (составной тип). Ресурсы: Количество, Сумма)
- Регистры сведений:
- ОсновныеСотрудникиФизическихЛиц (Независимый, непериодический. Измерение: ФизическоеЛицо. Ресурс: Сотрудник — ссылка на Сотрудники).
Фильтрация виртуальных таблиц без использования параметров
Начнем с одного из самых часто встречающихся нарушений стандартов разработки 1С.
Задача: Нам необходимо получить текущий остаток на складе по одной конкретной номенклатурной позиции.
Неоптимальное решение
Очень часто разработчики переносят привычки из работы с обычными физическими таблицами (такими как справочники или документы) на работу с виртуальными таблицами регистров. Запрос пишется интуитивно: выбрать данные из таблицы остатков и отфильтровать их в секции ГДЕ.
ВЫБРАТЬ Остатки.Номенклатура КАК Номенклатура, Остатки.КоличествоОстаток КАК КоличествоОстаток ИЗ РегистрНакопления.ОстаткиТоваров.Остатки КАК Остатки ГДЕ Остатки.Номенклатура = &Номенклатура
Почему это тормозит?
При таком написании платформа 1С сначала получает из СУБД все остатки товаров на текущую дату, строит временную структуру в памяти, и только после этого отсекает ненужные записи по условию ГДЕ. На реальной базе с сотнями тысяч позиций номенклатуры это приводит к огромным непроизводительным затратам ресурсов.
Оптимальное решение
Правильный подход требует передачи всех условий фильтрации непосредственно в параметры самой виртуальной таблицы.
ВЫБРАТЬ Остатки.Номенклатура КАК Номенклатура, Остатки.КоличествоОстаток КАК КоличествоОстаток ИЗ РегистрНакопления.ОстаткиТоваров.Остатки(, Номенклатура = &Номенклатура) КАК Остатки
В чем разница?
Передавая отбор в параметры виртуальной таблицы, мы заставляем платформу сформировать SQL-запрос, который накладывает фильтр на самом раннем этапе — при обращении к таблице итогов СУБД. В результате от СУБД передаются только те строки, которые действительно нужны.
Кажется, что тут такого, даже не оптимальный запрос выполнился меньше чем за секунду. Но сравните — 859 миллисекунд или одна миллисекунда? Разница многократна!
Использование логического "ИЛИ" в условиях
Не всегда это очевидно, но логический оператор ИЛИ в условиях соединения или фильтрации часто является скрытой угрозой для производительности.
Задача: Найти товар в справочнике Номенклатура, если нам известен либо его уникальный артикул, либо его наименование. Оба поля (АртикулИнд и стандартное поле Наименование) проиндексированы.
Неоптимальное решение
Разработчик объединяет условия поиска по двум индексированным полям через логическое ИЛИ в одной секции ГДЕ.
ВЫБРАТЬ Номенклатура.Ссылка КАК Ссылка ИЗ Справочник.Номенклатура КАК Номенклатура ГДЕ (Номенклатура.АртикулИнд = &Артикул ИЛИ Номенклатура.Наименование ПОДОБНО &Наименование)
Почему это тормозит?
Когда оптимизатор запросов СУБД видит конструкцию ИЛИ, он чаще всего не может подобрать один оптимальный индекс для поиска. В большинстве случаев СУБД отказывается от использования индексов и переходит к полному сканированию таблицы (Table Scan / Clustered Index Scan), что крайне неэффективно на больших объемах данных.
Оптимизированный вариант (замена на ОБЪЕДИНИТЬ):
Запрос разделяется на две независимые и простые ветки выборки, результаты которых объединяются с помощью оператора ОБЪЕДИНИТЬ (который автоматически исключает дубликаты, если один и тот же товар удовлетворяет обоим условиям).
ВЫБРАТЬ Номенклатура.Ссылка КАК Ссылка ИЗ Справочник.Номенклатура КАК Номенклатура ГДЕ Номенклатура.АртикулИнд = &Артикул ОБЪЕДИНИТЬ ВЫБРАТЬ Номенклатура.Ссылка ИЗ Справочник.Номенклатура КАК Номенклатура ГДЕ Номенклатура.Наименование ПОДОБНО &Наименование
В чем разница?
Мы разбили один сложный запрос на два простых, объединив их результаты. СУБД может оптимизировать каждую ветку запроса независимо друг от друга. Для первой ветки будет задействован индекс по полю Артикул, а для второй ветки (в зависимости от настроек и наличия индексов) — свой оптимальный сценарий.
И снова разница во времени выполнении запроса многократна — 27 мс против 888 мс в неоптимальном варианте. Поэтому иногда есть смысл написать больше текста в запросе, но выиграть в производительности.
Обращение через точку к полям составного типа данных
Обращение через точку к реквизитам объектов (например, Продажи.Покупатель.Наименование) — очень удобный инструмент языка запросов 1С. Но если поле имеет составной тип данных, это удобство оборачивается серьезным падением производительности.
Задача: Вывести объем продаж в разрезе покупателей, но только по покупателям, относящимся к справочнику Контрагенты. Реквизит Покупатель в регистре Продажи имеет составной тип, и одним из входящих типов является справочник Контрагенты.
Неоптимальное решение
Разработчик накладывает условие по типу покупателя в секции ГДЕ, но само наименование выбирает через прямое разыменование покупателя «через точку».
ВЫБРАТЬ Продажи.Номенклатура КАК Номенклатура, Продажи.Покупатель.Наименование КАК ИмяПокупателя ИЗ РегистрНакопления.Продажи КАК Продажи ГДЕ Продажи.Покупатель ССЫЛКА Справочник.Контрагенты
Почему это тормозит?
Поле Покупатель может содержать ссылки на разные справочники (например, Контрагенты, ФизическиеЛица, Партнеры, и т.д.). Когда вы пишете Покупатель.Наименование, платформа 1С на уровне СУБД вынуждена сделать неявное левое соединение (LEFT JOIN) абсолютно со всеми таблицами справочников, которые входят в этот составной тип. Из-за этого итоговый SQL-текст разрастается, а СУБД тратит ресурсы на избыточные соединения.
Оптимизированный вариант (использование ВЫРАЗИТЬ):
Мы объединяем отбор по типу в секции ГДЕ с явным приведением типа с помощью оператора ВЫРАЗИТЬ в секциях ВЫБРАТЬ и СГРУППИРОВАТЬ ПО
ВЫБРАТЬ Продажи.Номенклатура КАК Номенклатура, ВЫБОР КОГДА Продажи.Покупатель ССЫЛКА Справочник.Контрагенты ТОГДА ВЫРАЗИТЬ(Продажи.Покупатель КАК Справочник.Контрагенты).Наименование КОНЕЦ КАК ИмяПокупателя ИЗ РегистрНакопления.Продажи КАК Продажи ГДЕ Продажи.Покупатель ССЫЛКА Справочник.Контрагенты
В чем разница?
Функция ВЫРАЗИТЬ жестко указывает платформе, к какому именно типу данных мы приводим поле. На уровне СУБД выполняется соединение только с одной конкретной таблицей справочника Контрагенты, минуя все остальные неиспользуемые типы.
Здесь разница уже не так ощутима в процентах, но очень ощутима в натуральных единицах (на 4 секунды быстрее после оптимизации запроса).
И чем больше типов входит в составной тип, тем больше будет эта разница заметна.
Заключение
Мы разобрали три базовых примера оптимизации на уровне кода запроса. Даже эти простые правила позволяют существенно снизить нагрузку на сервер приложений и СУБД. Реальные боевые задачи подбрасывают более сложные вызовы:
- что выбрать — вынести запрос во временную таблицу, и заметить после этого тормоза, или нарушить стандарты и написать левое соединение с виртуальной таблицей?
- почему после добавления индекса к временной таблице запрос стал выполняться медленнее?
- почему вложенные запросы могут приводить к сильным тормозам?
И много чего еще интересного и странного.
Вы, должно быть, обратили внимание, что на скриншотах примеров больше трех рассмотренных. Все так. Для самых внимательных, кто это заметил: в закрытом Клубе для 1С разработчиков Alexcode.PRO вы сможете скачать тестовую конфигурацию и подробно рассмотреть все 8 примеров:
Кейс 1. Фильтрация виртуальных таблиц без использования параметров
Кейс 2. Соединения со вложенными запросами
Кейс 3. Соединение записей с виртуальными таблицами
Кейс 4. Несоответствие индексов и условий запроса (отбор по неиндексируемому полю)
Кейс 5. Использование логического «ИЛИ» в условиях
Кейс 6. Использование подзапросов в условии соединения
Кейс 7. Получение данных через точку от полей составного типа
Кейс 8. Соединение по неиндексируемому ресурсу регистра сведений