Как оптимизировать запрос при резком падении производительности на границе количества строк

В практике разработки на платформе 1С встречается специфическая проблема: запрос работает мгновенно (доли секунды), пока количество записей во временной таблице не превышает определенный порог (например, 3064 строки), но стоит добавить всего одну запись, как время выполнения увеличивается до десятков секунд. Рассмотрим, почему это происходит, и разберем по шагам, как оптимизировать такие запросы.

Проанализируем исходную ситуацию

Типичный сценарий выглядит так: мы выбираем данные во временную таблицу втТаблица, индексируем её, а затем соединяем с регистром сведений. В нашем примере автор совершил классическую ошибку в индексировании и столкнулся с особенностями работы оптимизатора SQL-сервера.

Исходный код запроса:

ВЫБРАТЬ РАЗРЕШЕННЫЕ РАЗЛИЧНЫЕ ПЕРВЫЕ 3064 
    Источник.ПолеСтрока КАК ПолеСтрока
ПОМЕСТИТЬ втТаблица
ИЗ
    Источник КАК Источник
 ИНДЕКСИРОВАТЬ ПО
    Поле1; // ВНИМАНИЕ: Индекс по Поле1, а соединение по ПолеСтрока!
////////////////////////////////////////////////////////////////
ВЫБРАТЬ РАЗРЕШЕННЫЕ
    ПереодическийНезависимыйРегистр.Измерение1 КАК Измерение1
ИЗ
    РегистрСведений.ПереодическийНезависимыйРегистр КАК ПереодическийНезависимыйРегистр
    ВНУТРЕННЕЕ СОЕДИНЕНИЕ втТаблица КАК втТаблица
    ПО ПереодическийНезависимыйРегистр.Измерение1 = втТаблица.ПолеСтрока
ГДЕ
    ПереодическийНезависимыйРегистр.Ресурс = "что-то"

Выясним причину: «Магический порог» 3065 строк

Резкое замедление при достижении определенного количества строк (в данном случае 3065) — это признак того, что оптимизатор СУБД сменил план выполнения запроса. Рассмотрим подробнее, как это работает:

1. Nested Loops (Вложенные циклы): На малых объемах данных SQL-сервер берет каждую строку из временной таблицы и ищет соответствие в регистре по индексу. Это работает очень быстро, если строк мало.
2. Hash Join (Соединение хешированием): Когда строк становится много, оптимизатор решает, что «циклы» будут слишком долгими, и переключается на построение хеш-таблиц. Если поля соединения — длинные строки, или памяти недостаточно, или статистика временной таблицы неактуальна, этот процесс начинает «тормозить», сбрасывая промежуточные данные в TempDB на диск.

Шаг 1: Правильное индексирование временных таблиц

Проанализируем первую ошибку в коде. В запросе указано ИНДЕКСИРОВАТЬ ПО Поле1, однако соединение в следующем пакете идет по полю ПолеСтрока. Для СУБД такой индекс бесполезен. Разберем, как исправить ситуацию:

Важно: Индексировать временную таблицу нужно строго по тем полям, по которым происходит СОЕДИНЕНИЕ или на которые накладывается условие ГДЕ.

Исправленный фрагмент:

ВЫБРАТЬ РАЗРЕШЕННЫЕ РАЗЛИЧНЫЕ
    Источник.ПолеСтрока КАК ПолеСтрока
ПОМЕСТИТЬ втТаблица
ИЗ
    Источник КАК Источник
ИНДЕКСИРОВАТЬ ПО
    ПолеСтрока; // Индексируем поле соединения!

Шаг 2: Явное ограничение типов (Типизация)

В 1С поля типа Строка могут иметь неограниченную длину или длину, превышающую разумные пределы для индексов. Если типы данных во временной таблице и в регистре хотя бы немного различаются (например, NVARCHAR(50) и NVARCHAR(100)), SQL-сервер выполнит неявное приведение типов, что полностью исключит использование индексов.

Применим функцию ВЫРАЗИТЬ, чтобы гарантировать совпадение типов:

ВЫБРАТЬ РАЗРЕШЕННЫЕ РАЗЛИЧНЫЕ
    ВЫРАЗИТЬ(Источник.ПолеСтрока КАК СТРОКА(36)) КАК ПолеСтрока
ПОМЕСТИТЬ втТаблица
...

Шаг 3: Особенности периодических регистров

Разберем, почему запрос к периодическому регистру может быть медленным. В 1С кластерный индекс периодического регистра всегда начинается с поля Period (Период). Если в запросе вы не указываете условие по периоду, SQL-сервер не может выполнить эффективный поиск (Index Seek) по началу индекса и вынужден сканировать весь индекс целиком (Index Scan).

Рекомендация: Если бизнес-логика позволяет, всегда добавляйте ограничение по периоду, даже если это «за последний год» или «на текущую дату».

Шаг 4: Влияние механизма RLS (РАЗРЕШЕННЫЕ)

Использование ключевого слова РАЗРЕШЕННЫЕ заставляет платформу 1С дописывать к вашему SQL-запросу дополнительные подзапросы для проверки прав доступа на уровне записей. Это сильно усложняет жизнь оптимизатору СУБД. Посмотрим на возможные решения:

• Проверьте работу запроса под пользователем с полными правами. Если задержка исчезла — проблема в неоптимальных правилах RLS.
• Попробуйте вынести предварительный отбор разрешенных данных во временную таблицу, чтобы в основном соединении RLS уже не участвовал.

Шаг 5: Использование условия «В» вместо соединения

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

ВЫБРАТЬ РАЗРЕШЕННЫЕ
    Регистр.Измерение1 КАК Измерение1
ИЗ
    РегистрСведений.ПереодическийНезависимыйРегистр КАК Регистр
ГДЕ
    Регистр.Ресурс = "что-то"
    И Регистр.Измерение1 В (ВЫБРАТЬ вт.ПолеСтрока ИЗ втТаблица КАК вт)

Подведем итоги: чеклист оптимизации

1. Проверьте индексы временных таблиц: они должны соответствовать полям соединения.
2. Используйте ВЫРАЗИТЬ: для строковых полей это критично для избежания неявного приведения типов.
3. Избегайте длинных строк в измерениях: если Измерение1 — это строковое представление ГУИДа, лучше хранить его как УникальныйИдентификатор или ссылку на справочник. Индексы по строкам длиннее 36-50 символов работают крайне неэффективно.
4. Анализируйте статистику: на больших базах убедитесь, что регламентные задания по обновлению статистики и дефрагментации индексов на SQL-сервере выполняются регулярно.
5. Порядок соединений: Старайтесь, чтобы в соединении первой шла таблица с меньшим количеством данных (ведущая таблица), хотя современный оптимизатор часто делает это сам, если статистика актуальна.

Соблюдение этих правил позволит избежать «просадок» производительности при росте объема данных и сделает работу системы стабильной.