В разработке на платформе 1С часто встречается ситуация, когда форма с динамическим списком, прекрасно работающая на тестовой базе, начинает "зависать" на продуктивной базе с большим объемом данных. При этом сам запрос списка кажется простым, содержит отбор по индексированному полю и в консоли запросов выполняется мгновенно. Рассмотрим, почему так происходит и как оптимизировать запрос для быстрой работы даже на сотнях тысяч записей.
Проанализируем реальный пример: форма присоединенных файлов перестала отвечать, когда справочник ПрисоединенныеФайлы разросся до 400 000 записей. При этом для конкретного владельца нужно было отобразить всего 3 файла.
Основная причина кроется в том, как система управления базами данных (СУБД), например, MS SQL Server, строит план выполнения запроса. Когда СУБД получает сложный запрос, особенно с соединениями (JOIN), она пытается найти наиболее эффективный способ его выполнить. Иногда ее выбор оказывается неоптимальным.
Рассмотрим исходный, "проблемный" запрос, который использовался в динамическом списке.
"ВЫБРАТЬ
| Файлы.Ссылка КАК Ссылка,
| Файлы.ПометкаУдаления,
| ВЫБОР
| КОГДА Файлы.ПометкаУдаления = ИСТИНА
| ТОГДА ЕСТЬNULL(Файлы.ИндексКартинки, 2) + 1
| ИНАЧЕ ЕСТЬNULL(Файлы.ИндексКартинки, 2)
| КОНЕЦ КАК ИндексКартинки,
| Файлы.Наименование КАК Наименование,
| ВЫРАЗИТЬ(Файлы.Описание КАК СТРОКА(500)) КАК Описание,
| Файлы.Автор,
| Файлы.ДатаСоздания,
| Файлы.Изменил КАК Отредактировал,
| ДОБАВИТЬКДАТЕ(Файлы.ДатаМодификацииУниверсальная, СЕКУНДА, &СекундДоМестногоВремени) КАК ДатаИзменения,
| ВЫРАЗИТЬ(Файлы.Размер / 1024 КАК ЧИСЛО(10, 0)) КАК Размер,
| Файлы.ПодписанЭП,
| Файлы.Зашифрован,
| // ... другие вычисляемые поля
| Файлы.ВладелецФайла КАК ВладелецФайла,
| Файлы.ХранитьВерсии КАК ХранитьВерсии
|ИЗ
| &ИмяСправочника КАК Файлы
| ЛЕВОЕ СОЕДИНЕНИЕ РегистрСведений.СтатусыСинхронизацииФайловСОблачнымСервисом КАК СтатусыСинхронизацииФайловСОблачнымСервисом
| ПО Файлы.Ссылка = СтатусыСинхронизацииФайловСОблачнымСервисом.Файл
|ГДЕ
| Файлы.ВладелецФайла = &ВладелецФайлов";
На первый взгляд, все логично: мы берем таблицу файлов, соединяем ее с регистром статусов и фильтруем по владельцу с помощью условия ГДЕ. Логика подсказывает, что СУБД сначала должна отфильтровать 3 нужные записи по ВладелецФайла, а затем уже для них выполнить соединение и вычисления.
Однако на практике СУБД может выбрать другой, крайне неэффективный план:
ЛЕВОЕ СОЕДИНЕНИЕ всей таблицы справочника (400 000 записей) с регистром сведений. Эта операция сама по себе является очень ресурсоемкой.ГДЕ Файлы.ВладелецФайла = &ВладелецФайлов.Такой порядок действий приводит к обработке огромного массива данных вместо небольшого набора, что и вызывает "зависание" формы. На это могут влиять устаревшая статистика таблиц в СУБД, сложность вычисляемых полей в секции ВЫБРАТЬ и особенности работы самой платформы 1С с динамическими списками.
Чтобы "подсказать" СУБД правильный и самый быстрый путь выполнения запроса, нужно разбить его на два этапа с помощью временной таблицы. Этот прием является одним из ключевых методов оптимизации запросов в 1С.
Идея проста: сначала мы выполняем максимально быстрый и простой запрос, чтобы отобрать только ключевые поля (в данном случае, ссылки) нужных нам записей. А затем используем эту небольшую выборку в основном, более сложном запросе.
Посмотрим на исправленный код:
"ВЫБРАТЬ
| ЛФ.Ссылка КАК Ссылка
|ПОМЕСТИТЬ втФайлы
|ИЗ
| &ИмяСправочника КАК ЛФ
|ГДЕ
| ЛФ.ВладелецФайла = &ВладелецФайлов
|;
|////////////////////////////////////////////////////////////////////////////////
|ВЫБРАТЬ
| Файлы.Ссылка КАК Ссылка,
| Файлы.ПометкаУдаления,
| ВЫБОР
| КОГДА Файлы.ПометкаУдаления = ИСТИНА
| ТОГДА ЕСТЬNULL(Файлы.ИндексКартинки, 2) + 1
| ИНАЧЕ ЕСТЬNULL(Файлы.ИндексКартинки, 2)
| КОНЕЦ КАК ИндексКартинки,
| Файлы.Наименование КАК Наименование,
| // ... остальные поля, как в первом запросе
| Файлы.ВладелецФайла КАК ВладелецФайла,
| Файлы.ХранитьВерсии КАК ХранитьВерсии
|ИЗ
| втФайлы КАК втФайлы
| ВНУТРЕННЕЕ СОЕДИНЕНИЕ &ИмяСправочника КАК Файлы
| ПО втФайлы.Ссылка = Файлы.Ссылка
| ЛЕВОЕ СОЕДИНЕНИЕ РегистрСведений.СтатусыСинхронизацииФайловСОблачнымСервисом КАК СтатусыСинхронизацииФайловСОблачнымСервисом
| ПО Файлы.Ссылка = СтатусыСинхронизацииФайловСОблачнымСервисом.Файл
|ГДЕ
| Файлы.ВладелецФайла = &ВладелецФайлов";
Разберем по шагам, что здесь происходит:
втФайлы. Он делает только одно — отбирает ссылки на файлы для нужного владельца. Так как поле ВладелецФайла проиндексировано, этот запрос выполняется практически мгновенно, даже на миллионах записей. В результате в втФайлы оказывается всего 3 строки.втФайлы. СУБД получает четкое указание: "возьми эти 3 ссылки и для них получи все остальные данные". Это заставляет ее использовать самый эффективный план — быстрый поиск по первичному ключу (полю Ссылка) и выполнение соединений и вычислений только для строго ограниченного набора данных.Важный момент: обратите внимание, что условие ГДЕ Файлы.ВладелецФайла = &ВладелецФайлов осталось и во втором запросе. Хотя оно кажется избыточным, его наличие помогает оптимизатору 1С и СУБД лучше строить план и использовать индексы. Удалять его не рекомендуется.
Столкнувшись с проблемой производительности динамического списка, не спешите упрощать форму или отказываться от функциональности. Проанализируем ключевые выводы:
Также стоит задуматься и об архитектуре решения. Создание групп файлов по умолчанию для 100 000 владельцев приводит к разрастанию справочника. Возможно, более правильным подходом была бы "ленивая инициализация" — создавать эти группы не заранее, а в момент первого обращения к файлам конкретного владельца. Это позволит хранить в базе только действительно используемые данные.