В практике разработки на платформе 1С:Предприятие часто встречается парадоксальная на первый взгляд ситуация: запрос с одним условием отрабатывает мгновенно, но стоит добавить еще несколько фильтров по индексированным полям, как время выполнения увеличивается в десятки раз. Особенно остро эта проблема проявляется на больших таблицах, например, в регистрах сведений с десятками миллионов записей. Проанализируем, почему так происходит, и разберем механизмы работы СУБД и платформы «под капотом».
Первое, что нам необходимо понять — как именно 1С создает индексы в базе данных SQL Server для регистров сведений. Многие разработчики полагают, что если у трех реквизитов стоит галочка Индексировать, то система сможет эффективно использовать их все вместе в одном запросе. Однако это не совсем так.
Для каждого реквизита или ресурса, у которого включено индексирование, платформа создает отдельный некластерный индекс. Рассмотрим структуру такого индекса для периодического регистра сведений:
Реквизит.Период.Измерения регистра в том порядке, в котором они определены в конфигураторе.Когда мы пишем запрос с тремя условиями И по разным реквизитам, SQL-оптимизатор оказывается перед сложным выбором. Он не может использовать один индекс для поиска сразу по трем полям, потому что эти поля находятся в разных индексных структурах. В итоге СУБД приходится либо выбирать один «лучший» индекс, либо пытаться применить механизм Index Intersection (пересечение индексов), что на больших объемах данных (например, 60 миллионов записей) может работать крайне медленно.
Проанализируем ситуацию с точки зрения оптимизатора SQL. Допустим, у нас есть три условия:
ГДЕ
CRM_ВзаимодействияСКлиентом.Ответственный = &Ответственный
И CRM_ВзаимодействияСКлиентом.ПодразделениеОтветственного = &ПодразделениеОтветственного
И CRM_ВзаимодействияСКлиентом.СтатусРегистратора = &СтатусРегистратора
Если условие по полю Ответственный очень селективно (то есть возвращает всего 10–20 записей из 60 миллионов), то первый запрос работает быстро. Но когда мы добавляем условия по Подразделению и Статусу, оптимизатор может ошибиться. Если статистика по таблице устарела, СУБД может решить, что выгоднее начать поиск с индекса по Статусу.
Если индекс по статусу вернет 1 000 000 записей, системе придется проверить каждое из этих значений на соответствие остальным двум условиям. Это приводит к огромному количеству операций чтения и перегрузке дисковой подсистемы.
Разберем еще одну важную причину замедления — Key Lookup (закладки). В примере запроса выбирается поле Период:
ВЫБРАТЬ
КОЛИЧЕСТВО(CRM_ВзаимодействияСКлиентом.Период) КАК Период
...
Поскольку наши индексы построены по реквизитам (Ответственный, Подразделение и т.д.), они могут не содержать всех данных, необходимых для результата. Если оптимизатор выбрал индекс по полю Ответственный, он нашел там ссылки на записи, но чтобы получить значение Период (если оно не входит в «покрывающую» часть индекса) или проверить условия по другим полям, СУБД вынуждена для каждой найденной строки обращаться к основной таблице (кластерному индексу). 100 таких обращений — это быстро, 100 000 обращений — это катастрофически медленно.
В обсуждениях часто упоминается параметр mdop (MAXDOP — Maximum Degree of Parallelism). Это настройка SQL Server, определяющая, сколько ядер процессора может быть задействовано для выполнения одного запроса.
Для систем на базе 1С часто рекомендуется устанавливать MAXDOP = 1 (или небольшое значение, в зависимости от количества ядер и нагрузки). Почему это важно в нашем контексте?
CXPACKET) часто превышают выигрыш от параллельности, что визуально выглядит как «зависание» запроса при добавлении условий.Рассмотрим конкретные шаги, которые помогут ускорить выполнение запроса:
1. Обновление статистики СУБД. Это первое, что нужно сделать. Если статистика устарела, оптимизатор строит неверные планы. Выполните команду обновления статистики для таблицы регистра средствами SQL Server Management Studio или через регламентные задания 1С.
2. Использование временных таблиц. Вместо одного сложного запроса с тремя И, попробуем разбить задачу. Сначала выберем данные во временную таблицу только по самому селективному полю (например, по Ответственному), а затем наложим остальные фильтры на эту временную таблицу. Это принудительно заставит систему использовать нужный нам индекс в начале.
3. Проверка состава индекса. Помните, что в 1С порядок измерений в конфигураторе определяет структуру основного индекса. Если ваши условия часто включают одни и те же поля, возможно, стоит сделать их Измерениями и расположить в начале списка. Индекс по измерениям является составным и работает гораздо эффективнее, чем несколько отдельных индексов по реквизитам.
4. Анализ типа полей. Убедитесь, что типы передаваемых параметров (&Ответственный, &СтатусРегистратора) точно совпадают с типами полей в базе данных. Неявное приведение типов (например, сравнение строки разной длины) может сделать использование индекса невозможным (сканирование вместо поиска).
5. Настройка MAXDOP. Если у вас SQL Server, проверьте настройки параллелизма. Для высоконагруженных систем 1С значение 0 часто является неоптимальным. Попробуйте установить значение 1 и сравнить скорость выполнения проблемного запроса.
Замедление запроса при добавлении условий — это не «баг» платформы, а следствие работы алгоритмов оптимизации СУБД на определенных структурах данных. Основная причина кроется в том, что 1С создает одиночные индексы для каждого реквизита. Для эффективной работы на таблицах в 60 миллионов записей требуется либо идеальное состояние статистики, либо использование составных индексов (через измерения), либо грамотное разделение запроса на этапы с использованием временных таблиц.