Как оптимизировать прямой SQL-запрос 1С++ для быстрого выбора документов по списку номенклатуры

← На главную

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

В этой статье мы подробно разберем реальный практический кейс: оптимизацию прямого запроса, который собирает количество документов трех различных типов (Реализация, Реализация_Розница и Реализация_Распоряжение), в табличных частях которых содержится номенклатура из переданного списка значений. В исходном состоянии на базе объемом 45 Гб при обработке 1700 позиций номенклатуры и 35 тысяч документов за 11 месяцев запрос выполнялся около 7 секунд. Мы разберем по шагам, почему этот запрос работал медленно и как ускорить его выполнение до долей секунды.

Анализ исходного запроса и выявление «узких мест»

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


|SELECT
| СпрНоменклатура.Code [Код],
| t1.Номенклатура [Номенклатура $Справочник.Номенклатура],
| sum(t1.КолПродаж) КолПродаж
|FROM
|(
|SELECT
| $ДокументСтроки.Реализация.Номенклатура [Номенклатура],
| count(distinct ДокР.IDDOC) AS КолПродаж
|FROM
| _1SJourn Жур
|INNER JOIN
| $ДокументСтроки.Реализация AS ДокР ON ДокР.IDDoc = Жур.IDDoc
|WHERE
| (Жур.CLOSED&1=1) AND
| Жур.Date_Time_IDDoc BETWEEN :НачДата AND :КонДата~ AND
| ($ДокументСтроки.Реализация.Номенклатура IN (SELECT Val FROM #TempTable))
|GROUP BY $ДокР.Номенклатура
|UNION
|... (аналогичные блоки для Реализация_Розница и Реализация_Распоряжение)
|) t1
|INNER JOIN
| $Справочник.Номенклатура AS СпрНоменклатура ON СпрНоменклатура.ID=t1.Номенклатура
|GROUP BY t1.Номенклатура, СпрНоменклатура.code

Проанализировав этот код, мы можем выделить сразу несколько критических проблем производительности, которые в совокупности дают задержку в 7 секунд:

Использование оператора UNION вместо UNION ALL. Это одна из самых распространенных ошибок. Оператор UNION заставляет СУБД MS SQL Server не просто объединить результаты подзапросов, но и выполнить их полную сортировку для удаления дубликатов строк. Это крайне ресурсоемкая операция, требующая больших объемов оперативной памяти и нагружающая процессор. Кроме того, в данном конкретном алгоритме использование UNION ведет к логической ошибке: если одна и та же номенклатура продавалась ровно по 5 раз в разных типах документов, UNION схлопнет эти строки, исказив итоговую сумму продаж.
Конструкция фильтрации IN (SELECT Val FROM #TempTable). На старых версиях СУБД MS SQL Server оптимизатор запросов часто строит крайне неэффективный план выполнения (например, Table Scan вместо быстрых поисков по индексам), когда видит конструкцию IN с подзапросом по временной таблице. СУБД может начать выполнять этот подзапрос повторно для каждой строки основной таблицы.
Отсутствие индекса на временную таблицу #TempTable. Переданный список номенклатуры содержит 1700 позиций. Для SQL Server это достаточно большой объем данных. Если временная таблица не проиндексирована, то при каждом сопоставлении строк СУБД будет вынуждена сканировать всю таблицу целиком, выполняя операцию Table Scan.
Отсутствие хинтов блокировок (NOLOCK). На рабочей базе объемом 45 Гб параллельно работают десятки пользователей. Выполнение тяжелого чтения данных без явного указания уровня изоляции транзакций приводит к тому, что запрос накладывает разделяемые блокировки (S-locks) и ожидает снятия монопольных блокировок от пишущих транзакций (проведение документов). Это приводит к простоям на уровне дисковой подсистемы и процессора.
Промежуточная группировка в каждом подзапросе. В каждом блоке объединения выполняется предварительный расчет COUNT(DISTINCT IDDOC) и группировка GROUP BY $ДокР.Номенклатура. Это заставляет SQL Server производить локальные сортировки и группировки трижды (для каждого типа документа), вместо того чтобы один раз сгруппировать плоский объединенный список во внешнем запросе.

Разбор ошибок в предложенном решении (Сообщение 3)

В ходе обсуждения на форуме в Сообщении 3 был предложен альтернативный вариант с использованием промежуточных временных таблиц #TT1, #TT2, #TT3 и #TT4. Давайте проанализируем эту идею подробнее.

Хотя автор идеи пытался разбить тяжелый запрос на шаги, этот подход имеет серьезные архитектурные недостатки:

Высокая нагрузка на системную базу данных tempdb. Создание четырех физических временных таблиц на диске и последовательная запись в них промежуточных данных вызовет колоссальную паразитную нагрузку на дисковую подсистему сервера. Запись на диск в tempdb для десятков тысяч строк будет работать медленнее, чем выполнение одного правильно спроектированного пакетного запроса в оперативной памяти сервера.
Неявное превращение LEFT JOIN в INNER JOIN. В коде Сообщения 3 используется конструкция LEFT JOIN _1SJourn Жур ON ... WHERE (Жур.CLOSED&1=1). Поскольку в секции WHERE накладывается жесткое условие на правую таблицу, этот LEFT JOIN физически работает как жесткий INNER JOIN, но при этом сбивает с толку планировщик запросов MS SQL Server, заставляя его выбирать неоптимальные индексы.

Пошаговый план оптимизации и правильное решение

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

Шаг 1. Создание индекса на временную таблицу. Перед тем как запускать основной блок выборки, нам необходимо проиндексировать поле Val в таблице #TempTable. Это позволит СУБД мгновенно находить нужные элементы номенклатуры при соединениях.

Шаг 2. Замена IN на явное соединение INNER JOIN. Вместо использования медленной конструкции WHERE Номенклатура IN (SELECT...) мы свяжем таблицы строк документов с нашей временной таблицей с помощью INNER JOIN #TempTable. Оптимизатор СУБД гарантированно выберет быстрый алгоритм слияния (например, Hash Match или Nested Loops).

Шаг 3. Использование UNION ALL и перенос группировки. Мы уберем локальные группировки из внутренних запросов. Вместо этого мы выберем плоские пары значений (Номенклатура, IDDoc), объединим их с помощью сверхбыстрого оператора UNION ALL (который не делает никаких сортировок) и выполним единственную финальную группировку во внешнем запросе.

Шаг 4. Внедрение хинтов (NOLOCK). К каждой таблице, участвующей в запросе, мы добавим указание (NOLOCK). Это позволит выполнять «грязное чтение» данных без ожидания освобождения заблокированных пользователями таблиц.

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


-- Шаг 1: Индексируем временную таблицу со списком номенклатуры
CREATE INDEX IX_TempTable_Val ON #TempTable (Val);

-- Шаг 2: Выполняем основную выборку
SELECT 
    СпрНоменклатура.Code [Код],
    t1.Номенклатура [Номенклатура $Справочник.Номенклатура],
    COUNT(DISTINCT t1.IDDoc) AS КолПродаж
FROM (
    SELECT 
        $ДокР.Номенклатура AS Номенклатура,
        ДокР.IDDoc AS IDDoc
    FROM $ДокументСтроки.Реализация AS ДокР (NOLOCK)
    INNER JOIN _1SJourn AS Жур (NOLOCK) ON ДокР.IDDoc = Жур.IDDoc
    INNER JOIN #TempTable AS TT ON $ДокР.Номенклатура = TT.Val
    WHERE Жур.CLOSED & 1 = 1 
      AND Жур.Date_Time_IDDoc BETWEEN :НачДата AND :КонДата~

    UNION ALL

    SELECT 
        $ДокРоз.Номенклатура AS Номенклатура,
        ДокРоз.IDDoc AS IDDoc
    FROM $ДокументСтроки.Реализация_Розница AS ДокРоз (NOLOCK)
    INNER JOIN _1SJourn AS Жур (NOLOCK) ON ДокРоз.IDDoc = Жур.IDDoc
    INNER JOIN #TempTable AS TT ON $ДокРоз.Номенклатура = TT.Val
    WHERE Жур.CLOSED & 1 = 1 
      AND Жур.Date_Time_IDDoc BETWEEN :НачДата AND :КонДата~

    UNION ALL

    SELECT 
        $ДокРасп.Номенклатура AS Номенклатура,
        ДокРасп.IDDoc AS IDDoc
    FROM $ДокументСтроки.Реализация_Распоряжение AS ДокРасп (NOLOCK)
    INNER JOIN _1SJourn AS Жур (NOLOCK) ON ДокРасп.IDDoc = Жур.IDDoc
    INNER JOIN #TempTable AS TT ON $ДокРасп.Номенклатура = TT.Val
    WHERE Жур.CLOSED & 1 = 1 
      AND Жур.Date_Time_IDDoc BETWEEN :НачДата AND :КонДата~
) AS t1
INNER JOIN $Справочник.Номенклатура AS СпрНоменклатура (NOLOCK) ON СпрНоменклатура.ID = t1.Номенклатура
GROUP BY t1.Номенклатура, СпрНоменклатура.Code

Административный уровень ускорения: Создание физических индексов

Если после внедрения предложенного выше SQL-кода скорость работы вас все еще не устраивает, необходимо проверить физическую структуру таблиц в базе данных MS SQL Server.

В платформе 1С 7.7 реквизиты табличных частей документов (такие как реквизит «Номенклатура», который физически хранится в базах SQL в виде колонок типа SPxxxxx в таблицах строк документов типа _1SDNxxxx) по умолчанию не индексируются самой платформой. При фильтрации по конкретной номенклатуре СУБД вынуждена выполнять полное сканирование всей таблицы строк документов, даже если мы ограничили период по таблице _1SJourn.

Для достижения максимального быстродействия (выполнения запроса за сотые доли секунды) рекомендуется обратиться к администратору баз данных и вручную создать некластерные индексы в SQL Server Management Studio на таблицы строк документов. Создадим пример структуры такого индекса:


CREATE NONCLUSTERED INDEX IX_1SDN_Nomenclature 
ON _1SDNxxxx (SPxxxxx) 
INCLUDE (IDDoc);

Здесь _1SDNxxxx — это имя SQL-таблицы строк нужного нам документа, а SPxxxxx — имя колонки, соответствующей реквизиту «Номенклатура». Использование конструкции INCLUDE (IDDoc) позволит SQL Server выполнять операцию Index Seek и получать идентификатор документа напрямую из индекса, вообще не обращаясь к физическим строкам таблицы на диске.

Резюме

Оптимизация прямых запросов в 1С 7.7 — это всегда комплексный процесс, требующий понимания работы реляционных баз данных. Заменив тяжелые сортировки UNION на легкие UNION ALL, избавившись от лишних промежуточных группировок, проиндексировав временную таблицу и внедрив хинты (NOLOCK), мы смогли ускорить выполнение отчета в десятки раз. А ручное создание точечных индексов в СУБД позволит выполнять подобные аналитические выборки практически мгновенно даже на базах объемом в сотни гигабайт.