Почему добавление конструкции TOP 1 в SQL-запрос замедляет его выполнение и как это исправить?

При работе с прямыми SQL-запросами к базе данных 1С:Предприятия 7.7 на MS SQL Server разработчики нередко сталкиваются с аномальным поведением: запрос, возвращающий небольшой массив данных за доли секунды, после добавления инструкции TOP 1 начинает выполняться 10–15 секунд и более. Проанализируем, почему возникает эта проблема, как работает оптимизатор СУБД и какими способами можно вернуть высокую скорость выполнения запроса.

Причины замедления: механизм Row Goal в MS SQL Server

Рассмотрим пример запроса, выбирающего подчиненные документы и соединяющего их с журналом документов _1sjourn:

SELECT --TOP 1
    ЖурналПодч2.*
FROM
(SELECT
    childid
FROM
 _1scrdoc as Подч (nolock)
WHERE
 (mdid = 0) AND (ParentVal = 'O1  BE BYLOKRAD        ')) ПодчДок
INNER JOIN
 _1sjourn as ЖурналПодч (Nolock) ON ЖурналПодч.iddocdef = $ВидДокумента.Рейс AND ЖурналПодч.iddoc = ПодчДок.childid
INNER JOIN
 _1scrdoc as ПодчДок2 (Nolock) ON ПодчДок2.mdid = 0 AND ПодчДок2.ParentVal = 'o1'+$ВидДокумента36.Рейс + ЖурналПодч.iddoc
INNER JOIN
 _1sjourn as ЖурналПодч2 (Nolock) ON ЖурналПодч2.iddocdef = $ВидДокумента.ТранспортнаяНакладная AND ЖурналПодч2.iddoc = ПодчДок2.childid
ORDER BY
 ЖурналПодч2.date_time_iddoc desc

Без указания TOP 1 сервер фильтрует записи по индексам, получает небольшое итоговое количество строк (например, 13 записей), сортирует их и моментально выдает результат. Однако при раскомментировании TOP 1 оптимизатор запросов меняет план выполнения из-за механизмов целевого количества строк (Row Goal):

1. Оптимизатор рассчитывает, что для поиска всего одной строки выгоднее сканировать индекс таблицы _1sjourn в порядке сортировки date_time_iddoc desc, ожидая быстро встретить первую подходящую запись.
2. Тип соединения таблиц меняется на вложенные циклы (Nested Loops).
3. Если устаревшая статистика базы данных неверно отражает распределение значений или нужная запись находится далеко от начала индекса, сервер начинает последовательно считывать гигабайты данных, проверяя условия для каждой строки. Это приводит к задержкам в 10–15 секунд вместо мгновенного ответа.

Способ 1. Актуализация статистик СУБД

Главной причиной неверной оценки стоимости плана запроса оптимизатором являются неактуальные или поврежденные статистики таблиц. Разберем порядок их обновления.

Часто используемая процедура sp_updatestats выполняет сэмплированное (выборочное) обновление. Для таблиц связей документов 1С (таких как _1scrdoc и _1sjourn) этого бывает недостаточно. Выполним полное обновление статистик с помощью параметра FULLSCAN:

UPDATE STATISTICS _1scrdoc WITH FULLSCAN;
UPDATE STATISTICS _1sjourn WITH FULLSCAN;

Также можно запустить полное обновление статистик всей базы данных через планы обслуживания (Maintenance Plans) MS SQL Server. После процедуры сканирования распределения данных оптимизатор снова сможет правильно оценивать селективность условий и выстраивать быстрый план выполнения.

Способ 2. Обход оптимизации Row Goal с помощью агрегации

Если изменить настройки сервера или оперативно обновить статистики невозможно, изменим структуру самого T-SQL запроса. Использование агрегатной функции MAX() вместо TOP 1 принудительно заставляет СУБД сначала отфильтровать все подходящие записи и только затем найти максимальное значение, исключая преждевременный переход на сканирование огромных индексов.

Посмотрим на пример переписанного запроса:

SELECT
    RIGHT(MAX(ЖурналПодч2.date_time_iddoc), 9) [ТранспортнаяНакладная $Документ.ТранспортнаяНакладная]
FROM
(SELECT
    childid
FROM
 _1scrdoc as Подч (nolock)
WHERE
 (mdid = 0) AND (ParentVal = 'O1  BE BYLOKRAD        ')) ПодчДок
INNER JOIN
 _1sjourn as ЖурналПодч (Nolock) ON ЖурналПодч.iddocdef = $ВидДокумента.Рейс AND ЖурналПодч.iddoc = ПодчДок.childid
INNER JOIN
 _1scrdoc as ПодчДок2 (Nolock) ON ПодчДок2.mdid = 0 AND ПодчДок2.ParentVal = 'o1'+$ВидДокумента36.Рейс + ЖурналПодч.iddoc
INNER JOIN
 _1sjourn as ЖурналПодч2 (Nolock) ON ЖурналПодч2.iddocdef = $ВидДокумента.ТранспортнаяНакладная AND ЖурналПодч2.iddoc = ПодчДок2.childid

Этот вариант отрабатывает за доли секунды, так как СУБД не пытается применить алгоритм Row Goal для ранней остановки чтения строк.

Способ 3. Использование временных таблиц

Выясним еще один надежный способ изоляции логики выбора — использование временных таблиц. Сохранив отфильтрованные данные во временную таблицу #temp, мы разрываем единый контекст оптимизации сложности запроса:

1. Выполняем выборку первичного набора документов без TOP 1 и сохраняем результаты во временную таблицу #temp.
2. Делаем итоговый выбор SELECT TOP 1 ... FROM #temp ORDER BY .... Поскольку временная таблица содержит всего несколько строк, операция ограничения выборки выполняется мгновенно.

Рекомендации по обслуживанию базы данных

Для предотвращения подобных проблем в высоконагруженных системах 1С настроим регулярные регламентные процедуры:

1. Проводим дефрагментацию и перестроение индексов (REORGANIZE / REBUILD) в часы наименьшей нагрузки на сервер.

2. Обновляем частотные статистики таблиц 1С с параметром FULLSCAN не реже одного раза в неделю, а для активно меняющихся таблиц документов — ежедневно.

3. В современных версиях СУБД (MS SQL Server 2008 и новее) механизмы оценки кардинальности работают значительно точнее, чем в MS SQL Server 2000, поэтому обновление версии сервера также решает большинство проблем с неоптимальными планами выполнения TOP N.