Почему 1С делает много запросов к базе и как SQL Server выбирает план выполнения?

При работе с платформой «1С:Предприятие 8.3» разработчики часто сталкиваются с ситуациями, когда простейший запрос ведет себя неожиданно при анализе технологического журнала или трассировки SQL Server. В данной статье мы разберем два важных аспекта: почему при выполнении одного запроса в консоли в СУБД улетает сотня мелких обращений, и почему SQL Server иногда предпочитает сканировать весь справочник целиком вместо быстрого поиска по индексу.

Причина появления множества мелких запросов («Проблема N+1»)

Рассмотрим ситуацию: вы выполняете простейший запрос в консоли запросов, выбирая только ссылку из справочника Номенклатура. В трассировке SQL при этом фиксируется один основной запрос и еще 137 (по количеству записей) маленьких запросов вида SELECT _IDRRef, _Description FROM _Reference... WHERE _IDRRef = @P1.

Выясним причину этого поведения:

Это не ошибка платформы, а особенность работы инструментов отображения данных (консолей запросов и динамических списков). Когда вы запрашиваете только поле Ссылка, платформа получает из базы данных уникальные идентификаторы (GUID). Однако для того, чтобы вывести их на экран в читаемом виде, платформе необходимо получить Представление объекта (обычно это Наименование или Код).

Проанализируем ситуацию подробнее: для формирования представления каждой ссылки на форме консоль делает дополнительный вызов к базе. Если в результате запроса 137 строк, вы увидите 137 дополнительных запросов к полям _Description (Наименование) или _Code (Код). Чтобы избежать этого в реальном коде, всегда выбирайте нужные поля (например, Наименование) сразу в основном запросе, а при работе с формами учитывайте кэширование представлений.

Почему SQL Server выбирает сканирование (Scan) вместо поиска (Seek)?

Вторая важная проблема касается работы оптимизатора СУБД. Представим пакетный запрос: мы помещаем небольшой список товаров во временную таблицу (ВТ), а затем соединяем эту ВТ со справочником Номенклатура, чтобы получить наименования.

Логично ожидать, что СУБД выполнит Clustered Index Seek — точечный поиск по уникальному идентификатору для каждой записи из ВТ. Однако в трассировке мы часто видим Index Scan или Hash Match, при которых СУБД просматривает все записи справочника. Разберем по шагам, почему так происходит.

1. Стоимость операций и последовательное чтение.
Для SQL Server чтение страниц данных подряд (Scan) — это быстрая последовательная операция. Точечные прыжки по индексу (Seek) требуют «прыжков» по дереву индекса. Если в вашей временной таблице 100-200 записей, а в справочнике всего 1000, оптимизатору проще один раз прочитать весь индекс справочника целиком, чем 200 раз выполнять процедуру поиска.

2. Размер индекса (ширина строк).
Кластерный индекс в 1С (PrimaryKey) включает в себя все поля таблицы (все реквизиты номенклатуры). Это делает его очень «тяжелым». Некластерный индекс (например, по наименованию) содержит только само наименование и ссылку. Проанализируем логику СУБД: ей выгоднее прочитать целиком «узкий» некластерный индекс (сотни страниц памяти), чем многократно дергать «тяжелый» кластерный индекс (тысячи страниц). Именно поэтому в плане запроса часто фигурирует Index Scan некластерного индекса по наименованию.

3. Отсутствие статистики по временным таблицам.
В рамках одного пакета запросов SQL Server не всегда точно знает, сколько строк окажется в #temp таблице. Если он предполагает, что строк может быть много, он выбирает алгоритм Hash Join, который всегда требует сканирования (Scan) обеих таблиц для построения хеш-таблиц в памяти.

Как оптимизировать такие запросы?

Рассмотрим несколько методов, позволяющих повлиять на план выполнения:

1. Индексирование временных таблиц. Если временная таблица используется в соединении несколько раз, обязательно используйте ИНДЕКСИРОВАТЬ ПО. Это даст оптимизатору информацию о распределении данных.
2. Выбор «легких» полей. Если вы добавите в выборку поле, которое не входит ни в один некластерный индекс (например, какой-то специфический реквизит типа Число), СУБД будет вынуждена обратиться к кластерному индексу. В этом случае при малом объеме данных в ВТ может сработать Nested Loops и Clustered Index Seek.
3. Разрыв пакета запросов. В особо сложных случаях можно выполнить получение данных из ВТ в отдельном вызове, а затем передать их параметром в следующий запрос, но это крайняя мера, обычно замедляющая общую работу системы.

Посмотрим на пример эффективного запроса с использованием ВТ:

ВЫБРАТЬ
    Остатки.Номенклатура КАК Ссылка,
    Остатки.КоличествоОстаток КАК Остаток
ПОМЕСТИТЬ ВтДанные
ИЗ
    РегистрНакопления.ТоварыНаСкладах.Остатки(&Момент, ) КАК Остатки
ИНДЕКСИРОВАТЬ ПО
    Ссылка;

////////////////////////////////////////////////////////////////////////////////
ВЫБРАТЬ
    ВтДанные.Остаток,
    СпрНоменклатура.Наименование,
    СпрНоменклатура.Код
ИЗ
    ВтДанные КАК ВтДанные
    ВНУТРЕННЕЕ СОЕДИНЕНИЕ Справочник.Номенклатура КАК СпрНоменклатура
    ПО ВтДанные.Ссылка = СпрНоменклатура.Ссылка;

Подведем итог: если вы видите в трассировке Index Scan на небольших объемах данных (сотни или тысячи строк) — в большинстве случаев это не является проблемой. Это осознанный выбор SQL Server, направленный на минимизацию нагрузки на дисковую подсистему за счет последовательного чтения. Беспокоиться стоит тогда, когда Scan происходит на таблицах в миллионы записей — в таких случаях следует проверять актуальность статистики и наличие подходящих индексов.