Как устранить медленный поиск ключа (Key Lookup) в прямых SQL-запросах в 1С 7.7?

← На главную

При работе с прямыми SQL-запросами в среде 1С:Предприятие 7.7 (с использованием внешних компонент вроде 1С++) разработчики часто сталкиваются с ситуацией, когда один и тот же запрос при разных параметрах (например, за разные периоды) выполняется с кардинально разной скоростью. В один период запрос отрабатывает мгновенно, а в другой — зависает или выполняется чрезвычайно медленно. Выясним причину этого поведения и разберем по шагам, как заставить оптимизатор MS SQL Server всегда выбирать быстрый план выполнения.

Что такое Key Lookup и почему он замедляет работу СУБД?

Для начала проанализируем ситуацию, описанную автором темы. Оптимизатор СУБД может обрабатывать запрос двумя способами: быстрым (с использованием индекса) и медленным (поиск ключа, или Key Lookup). Что же такое Key Lookup?

Операция Key Lookup (в старых версиях MS SQL Server она называлась Bookmark Lookup) возникает в тех случаях, когда оптимизатор решает использовать некластеризованный (второстепенный) индекс для поиска записей по условию WHERE или JOIN, но в самом этом индексе отсутствуют все те поля, которые вы запрашиваете в блоке SELECT. СУБД находит нужные строки в индексе, получает ссылки на физические строки таблицы (ключи кластерного индекса) и вынуждена совершать дополнительные операции чтения («дочитывать» данные) из основной таблицы.

Если запрос возвращает небольшое количество строк, эта операция незаметна. Но если количество выбираемых строк увеличивается, Key Lookup превращается в лавинообразный процесс случайного чтения с диска. На каждую строку приходится отдельная операция ввода-вывода (I/O), что приводит к катастрофическому падению производительности.

Рассмотрим, почему план запроса меняется в зависимости от периода (например, на одной неделе всё быстро, на другой — медленно). Здесь в силу вступают два механизма оптимизатора:

Точка перегиба (Tipping Point): Оптимизатор динамически рассчитывает, что выгоднее — использовать некластеризованный индекс + Key Lookup или выполнить полное сканирование таблицы (Clustered Index Scan). Если объем выбираемых данных превышает определенный порог (обычно 1–3% от общего объема страниц таблицы), оптимизатор переключается на полное сканирование.
Маскировка параметров (Parameter Sniffing): При первом запуске запроса SQL Server компилирует план под конкретные параметры (например, короткий период). Этот план кэшируется. При повторном запуске с другими параметрами (например, за большой период) СУБД пытается использовать старый кэшированный план с Key Lookup, что приводит к зависанию запроса на больших объемах данных.

Ниже мы подробно рассмотрим несколько способов решения этой проблемы: от самого быстрого («костыльного») до наиболее надежных и архитектурно правильных методов.

Решение 1: Принудительное указание индекса (Index Hint)

Самый простой и быстрый способ заставить оптимизатор SQL Server использовать нужный индекс — это жесткое указание подсказки (хинта) в тексте запроса. Как подсказали автору в сообщении 7, для этого используется конструкция INDEX = ИмяИндекса.

Посмотрим на пример того, как это реализуется в синтаксисе прямого запроса 1С 7.7:


FROM
    $Документ.РасходнаяНал as ДокР (NOLOCK INDEX = Имя_Вашего_Индекса)

Важное предупреждение: Использование жестких хинтов на индексы — это «обоюдоострый меч». С одной стороны, это мгновенно решает проблему здесь и сейчас. С другой стороны, данный подход несет в себе серьезные риски:

При реструктуризации базы данных или обновлении конфигурации платформа 1С 7.7 может пересоздать таблицы СУБД, изменив или удалив автоматические индексы. Если индекс с указанным именем исчезнет, ваш запрос аварийно завершится с ошибкой.
Если объем данных за выбранный период окажется критически огромным, принудительный Key Lookup заставит сервер выполнять миллионы лишних дисковых операций чтения, что может полностью «повесить» рабочую базу данных, в то время как обычное сканирование таблицы отработало бы в разы быстрее.

Решение 2: Оптимизация избыточных соединений таблиц

Проанализируем структуру исходного запроса автора темы. В сообщениях 9–12 участники абсолютно верно заметили важную деталь: в запросе присутствует избыточное соединение с таблицей шапки документа $Документ.РасходнаяНал.

Первоначальный вариант запроса содержал соединение:


SELECT DISTINCT
    ДокР.IDDoc as До
    ,$СпрТов.СкладТ as Ск
FROM
    $Документ.РасходнаяНал as ДокР (NOLOCK)
INNER JOIN
    _1SJourn as Жур (NOLOCK) ON Жур.IDDoc = ДокР.IDDoc 
INNER JOIN
    $ДокументСтроки.РасходнаяНал as ДокРСтр (NOLOCK) ON ДокРСтр.IDDoc = ДокР.IDDoc

Обратите внимание: мы соединяем шапку документа ДокР, общий журнал Жур и табличную часть ДокРСтр. При этом уникальный идентификатор документа IDDoc присутствует во всех трех таблицах. Если в блоке SELECT нам не нужны реквизиты из шапки документа (такие как склад шапки, контрагент и т.д.), то соединение с таблицей $Документ.РасходнаяНал является абсолютно избыточным!

Мы можем значительно упростить работу оптимизатора СУБД, исключив лишнее звено. Перепишем запрос, получая идентификаторы документов напрямую из табличной части и общего журнала:


SELECT DISTINCT
    ДокРСтр.IDDoc as [Док $Документ.РасходнаяНал]
    ,$СпрТов.СкладТ as [Склад $Справочник.СкладыТ]
FROM
    $ДокументСтроки.РасходнаяНал as ДокРСтр (NOLOCK)
INNER JOIN
    _1SJourn as Жур (NOLOCK) ON Жур.IDDoc = ДокРСтр.IDDoc 
    AND (Жур.IDDocDef = $ВидДокумента.РасходнаяНал)
    AND (Жур.Date_Time_IDDoc BETWEEN :НачДата AND :КонДата~)
    AND (Жур.Closed & 1 = 1)
INNER JOIN
    $Справочник.Товары as СпрТов (NOLOCK) ON СпрТов.ID = $ДокРСтр.Товар %СкладТ%

Исключение лишней таблицы из соединения уменьшает количество логических чтений, упрощает дерево выполнения запроса и снижает вероятность того, что оптимизатор выберет неверный план с медленным Key Lookup.

Решение 3: Борьба с Parameter Sniffing с помощью OPTION (RECOMPILE)

Если нестабильность планов запроса вызвана тем, что СУБД кэширует неоптимальный план для разных периодов, мы можем использовать специальную инструкцию рекомпиляции запроса. Добавление хинта OPTION (RECOMPILE) в самый конец SQL-запроса заставляет СУБД строить план заново при каждом выполнении, учитывая конкретные переданные значения дат в параметрах :НачДата и :КонДата~.

Посмотрим на пример интеграции этого решения:


|SELECT DISTINCT
|    ДокРСтр.IDDoc as [Док $Документ.РасходнаяНал]
|    ,$СпрТов.СкладТ as [Склад $Справочник.СкладыТ]
|FROM
|    $ДокументСтроки.РасходнаяНал as ДокРСтр (NOLOCK)
|...
|WHERE
|    Т2.До is null
|OPTION (RECOMPILE)

Преимущество метода: План выполнения всегда будет идеален для текущего периода. Для маленького периода выберется быстрый поиск по индексу, а для огромного периода — сканирование, что предотвратит критические зависания базы данных.

Решение 4: Создание покрывающего индекса (Covering Index)

Самый надежный способ раз и навсегда избавиться от операции Key Lookup на уровне базы данных — это создание покрывающего индекса.

Покрывающим называется некластеризованный индекс, который содержит в себе абсолютно все поля, запрашиваемые в конструкции SELECT и участвующие в соединениях. В этом случае СУБД получает все необходимые данные напрямую из самого индекса, не обращаясь к физическим строкам таблицы. Операция Key Lookup просто физически не может возникнуть.

Администратор базы данных (DBA) может зайти в MS SQL Server Management Studio и создать дополнительный индекс для таблицы документа. Например:


CREATE NONCLUSTERED INDEX IX_DocLines_Custom 
ON [dbo].[_1S_DocLines_Table] ([IDDoc]) 
INCLUDE ([SP_ProductField], [SP_WarehouseField])

При этом важно помнить, что нестандартные индексы, созданные вручную в обход конфигуратора 1С 7.7, могут быть удалены при проведении полного ТИС (Тестирования и исправления) или реструктуризации таблиц. Поэтому такие индексы рекомендуется документировать и держать наготове SQL-скрипт для их быстрого пересоздания.

Решение 5: Использование временных таблиц (#temp) для стабилизации планов

Сложные SQL-запросы с конструкциями LEFT OUTER JOIN ... WHERE NULL или оператором EXCEPT часто сбивают оптимизатор с толку. В таких ситуациях очень эффективным приемом является разделение сложного запроса на несколько простых шагов с использованием временных таблиц.

Рассмотрим этот алгоритм по шагам:

Выбираем только идентификаторы документов и складов по условию периода во временную таблицу #TempDocs. Поскольку этот запрос максимально прост, оптимизатор гарантированно выберет для него самый быстрый и стабильный план (обычно это Index Seek по индексу журнала _1SJourn).
Строим индекс по временной таблице #TempDocs для ускорения последующих соединений.
Выполняем итоговое соединение временной таблицы с остальными справочниками и таблицей исключений.

Пример реализации схемы с временной таблицей в коде 1С++:


// Шаг 1: Создание и наполнение временной таблицы
ТекстЗапроса1 = "
|SELECT DISTINCT
|    ДокРСтр.IDDoc as Док
|    ,$СпрТов.СкладТ as Склад
|INTO #TempDocs
|FROM
|    $ДокументСтроки.РасходнаяНал as ДокРСтр (NOLOCK)
|INNER JOIN
|    _1SJourn as Жур (NOLOCK) ON Жур.IDDoc = ДокРСтр.IDDoc 
|    AND (Жур.IDDocDef = $ВидДокумента.РасходнаяНал)
|    AND (Жур.Date_Time_IDDoc BETWEEN :НачДата AND :КонДата~)
|INNER JOIN
|    $Справочник.Товары as СпрТов (NOLOCK) ON СпрТов.ID = $ДокРСтр.Товар
|";

// Шаг 2: Создание индекса для временной таблицы
ТекстЗапроса2 = "
|CREATE CLUSTERED INDEX IX_TempDocs ON #TempDocs (Док, Склад)
|";

// Шаг 3: Получение финального результата с фильтрацией
ТекстЗапроса3 = "
|SELECT 
|    T.Док as [Док $Документ.РасходнаяНал]
|    ,T.Склад as [Склад $Справочник.СкладыТ]
|FROM #TempDocs T
|LEFT OUTER JOIN
|    (
|    SELECT 
|        $Спр.Док as До
|        ,$Спр.Склад as Ск
|    FROM  
|        $Справочник.СтатОтгрузки as Спр (NOLOCK)
|    WHERE  
|        $Спр.ДатаДляОтбора BETWEEN :НачДата AND :КонДата2
|    ) Т2 ON (Т2.До = T.Док) AND (Т2.Ск = T.Склад)
|WHERE
|    Т2.До is null
|DROP TABLE #TempDocs
|";

Использование этого метода позволяет гарантировать высочайшую и, самое главное, абсолютно стабильную скорость выполнения запроса вне зависимости от того, какой период (день, неделя или месяц) выбирает конечный пользователь программы.