В практике разработки на платформе 1С часто встречается парадоксальная ситуация: сложный запрос с агрегатными функциями и группировками отрабатывает за миллисекунды, в то время как максимально упрощенный запрос, выбирающий всего одну строку, «вешает» систему на минуты. Особенно часто это проявляется в типовых механизмах, таких как проверка даты запрета редактирования. Разберем, почему так происходит, и как особенности работы оптимизатора SQL Server влияют на производительность наших решений.
Рассмотрим два варианта запроса к регистру накопления РегистрНакопления.ПрочиеРасходыНезавершенногоПроизводства. Оба запроса пытаются получить дату из периода записей по конкретному регистратору.
Первый запрос с использованием группировки выполняется мгновенно:
ВЫБРАТЬ ПЕРВЫЕ 1
ВЫРАЗИТЬ(ТекущаяТаблица.Период КАК ДАТА) КАК Дата,
СУММА(1) КАК Поле1
ИЗ
РегистрНакопления.ПрочиеРасходыНезавершенногоПроизводства КАК ТекущаяТаблица
ГДЕ
ТекущаяТаблица.Регистратор = &Регистратор
СГРУППИРОВАТЬ ПО
ВЫРАЗИТЬ(ТекущаяТаблица.Период КАК ДАТА)
УПОРЯДОЧИТЬ ПО
Дата
Второй запрос, который кажется более простым (без агрегации), выполняется около минуты:
ВЫБРАТЬ ПЕРВЫЕ 1
ВЫРАЗИТЬ(ТекущаяТаблица.Период КАК ДАТА) КАК Дата,
1 КАК Поле1
ИЗ
РегистрНакопления.ПрочиеРасходыНезавершенногоПроизводства КАК ТекущаяТаблица
ГДЕ
ТекущаяТаблица.Регистратор = &Регистратор
УПОРЯДОЧИТЬ ПО
Дата
Выясним причину такого поведения, проанализировав планы выполнения на уровне СУБД. Проанализируем ситуацию: почему исключение СГРУППИРОВАТЬ ПО так кардинально меняет скорость работы?
Когда мы используем конструкцию ВЫБРАТЬ ПЕРВЫЕ 1 (которая в SQL превращается в TOP 1) совместно с УПОРЯДОЧИТЬ ПО (ORDER BY), оптимизатор SQL Server пытается найти самый эффективный способ получения данных. У него есть два основных пути:
Регистратор стоит на первом месте. После этого отсортировать найденные записи по периоду и вернуть первую.Регистратором.В первом запросе наличие СГРУППИРОВАТЬ ПО и СУММА(1) заставляет SQL Server в обязательном порядке обработать все записи по данному регистратору, чтобы вычислить сумму. В этом случае наиболее выгодным оказывается Index Seek. Оптимизатор понимает, что «схитрить» не получится, и выбирает быстрый поиск по индексу регистратора.
Во втором запросе агрегации нет. Оптимизатор видит TOP 1 и ORDER BY Период. Он делает ошибочное предположение: «Зачем мне искать записи по регистратору и сортировать их, если я могу просто пойти по порядку периодов и остановиться, как только найду нужную запись?». Если записей в регистре миллионы, а нужный регистратор находится в «конце» временной шкалы или его записей мало, SQL Server выполняет огромный объем сканирования (Clustered Index Scan), что и вызывает тормоза.
Посмотрим на планы выполнения, предоставленные в сообщениях. В быстром запросе мы видим операцию Index Seek по некластерному индексу _AccumRg43819_2. Это означает, что СУБД точечно нашла записи регистратора.
В медленном запросе (сообщение 3) мы видим Clustered Index Seek (хотя фактически это ведет себя как Scan в контексте поиска конкретного регистратора), где условие по регистратору проверяется в секции WHERE, а не SEEK. Это подтверждает, что СУБД просматривает таблицу в порядке периода, надеясь на быструю удачу.
Рассмотрим по шагам основные методы исправления этой ситуации:
Первое, что необходимо сделать — выполнить команду UPDATE STATISTICS для таблицы регистра. Если статистика актуальна, оптимизатор с большей вероятностью поймет, что записей по конкретному регистратору мало и сканирование всей таблицы будет стоить дороже, чем поиск по индексу. В 1С это можно сделать через консоль СУБД или регламентным заданием.
Высокая фрагментация индексов может вводить оптимизатор в заблуждение относительно стоимости операций. Выполнение ALTER INDEX REBUILD поможет восстановить структуру индексов _AccumRg.
Если вы пишете собственный запрос, добавление фиктивной группировки (как в первом примере автора) является легитимным способом «заставить» оптимизатор использовать Index Seek. Хотя это кажется избыточным кодом, с точки зрения производительности это дает гарантированный результат.
Если запрос типовой (как проверка даты запрета) и вы не можете изменить его текст в коде 1С, на уровне SQL Server можно создать Plan Guide. Это позволит жестко закрепить правильный план выполнения для конкретного SQL-текста, принудительно указав использование нужного индекса через подсказку FORCESEEK.
Иногда СУБД «запоминает» неудачный план из-за Parameter Sniffing. Выполнение команды DBCC FREEPROCCACHE заставит SQL Server перестроить планы. Важно: делайте это с осторожностью на высоконагруженных продуктивных базах, так как это вызовет кратковременный скачок нагрузки на CPU.
Стоит помнить, что поле Регистратор в 1С является составным. В SQL оно представлено двумя колонками: _RecorderTRef (тип документа) и _RecorderRRef (ссылка на документ). Индексы 1С по умолчанию включают оба этих поля. Если в запросе 1С вы пишете ГДЕ Регистратор = &Регистратор, платформа транслирует это в SQL с использованием обоих полей. Проверьте, что в индексе вашего регистра поле регистратора стоит в начале списка полей (обычно это второй индекс регистра накопления после кластерного).
Рассмотрим подробнее влияние поля «Область данных» (Data Area). Если база используется в режиме разделения данных, первым полем во всех индексах будет _Fld1670. Если статистика по распределению данных между областями устарела, это также станет причиной выбора неэффективного плана ORDER BY.
Проблема «непонятных тормозов» в простых запросах 1С чаще всего кроется в избыточном оптимизме SQL Server при попытке выполнить сортировку через сканирование кластерного индекса. Использование GROUP BY лишает оптимизатор возможности этого «маневра», обеспечивая стабильную скорость. Для исправления ситуации в типовых механизмах следует в первую очередь обратить внимание на актуальность статистики и отсутствие фрагментации индексов регистра.