Как оптимизировать зависающий запрос 1С на большом периоде?

При работе с базами данных 1С, особенно на платформе SQL Server, мы часто сталкиваемся с проблемой зависания или очень медленного выполнения запросов при попытке выборки данных за большой период. Это может быть связано с множеством факторов, от неоптимального построения самого запроса до проблем с настройками и индексами на стороне SQL Server. В этой статье мы подробно разберем, как подойти к решению такой проблемы, используя пример запроса из реальной практики.

Анализ исходной проблемы и запроса

Представим ситуацию: нам необходимо сформировать отчет по всем данным базы с 2010 года. При попытке запустить запрос за период с 1 января 2020 г. по 1 января 2021 г. запрос зависает. В то же время, за меньший период, например, за текущий месяц, он выполняется всего за 16 секунд. Это явный признак того, что объем данных и сложность операций при выборке большого периода оказываются критичными.

Давайте посмотрим на исходный запрос:

ВЫБРАТЬ РАЗРЕШЕННЫЕ
    МИНИМУМ(ДокЧекККМ.Дата) КАК Дата,
    ДокЧекККМ.БонуснаяКарта КАК ИнформационнаяКарта,
    СУММА(ДокЧекККМ.СуммаДокумента) КАК Сумма
ПОМЕСТИТЬ ВТ_Отчетные
ИЗ
    Документ.ЧекККМ КАК ДокЧекККМ
ГДЕ
    ДокЧекККМ.Проведен
    И ДокЧекККМ.Дата МЕЖДУ &НачДата И &КонДата
    И ДокЧекККМ.Подразделение В ИЕРАРХИИ(&СервисныеЦентры)
{ГДЕ
    (НЕ ДокЧекККМ.БонуснаяКарта В (&ИсключитьИнформационныеКарты)) КАК Поле2,
    (ДокЧекККМ.Подразделение.КаналСбыта = &КаналСбыта) КАК Поле4}
СГРУППИРОВАТЬ ПО
    ДокЧекККМ.БонуснаяКарта
;
////////////////////////////////////////////////////////////­////////////////////
ВЫБРАТЬ РАЗРЕШЕННЫЕ
    МИНИМУМ(ДокЧекККМ.Дата) КАК Дата,
    ДокЧекККМ.БонуснаяКарта КАК ИнформационнаяКарта,
    СУММА(ДокЧекККМ.СуммаДокумента) КАК Сумма
ПОМЕСТИТЬ ВТ_Прошлые
ИЗ
    Документ.ЧекККМ КАК ДокЧекККМ
ГДЕ
    ДокЧекККМ.Проведен
    И ДокЧекККМ.Дата МЕЖДУ &НачДатаПрошлые И &КонДатаПрошлые
    И ДокЧекККМ.Подразделение В ИЕРАРХИИ(&СервисныеЦентры)
{ГДЕ
    (НЕ ДокЧекККМ.БонуснаяКарта В (&ИсключитьИнформационныеКарты)) КАК Поле2,
    (ДокЧекККМ.Подразделение.КаналСбыта = &КаналСбыта) КАК Поле4}
СГРУППИРОВАТЬ ПО
    ДокЧекККМ.БонуснаяКарта
;
////////////////////////////////////////////////////////////­////////////////////
ВЫБРАТЬ
    ВТ_Отчетные.ИнформационнаяКарта КАК ИнформационнаяКарта,
    КОЛИЧЕСТВО(РАЗЛИЧНЫЕ ВТ_Отчетные.ИнформационнаяКарта) КАК КолвоКлиентовОтчет,
    КОЛИЧЕСТВО(ВТ_Отчетные.Дата) КАК КолвоЗаказовОтчет,
    ВТ_Прошлые.ИнформационнаяКарта КАК ИнформационнаяКартаПрошлая,
    КОЛИЧЕСТВО(РАЗЛИЧНЫЕ ВТ_Прошлые.ИнформационнаяКарта) КАК КолвоКлиентовПрошлое,
    КОЛИЧЕСТВО(ВТ_Прошлые.Дата) КАК КолвоЗаказовПрошлое
ПОМЕСТИТЬ ВТ_Количество
ИЗ
    ВТ_Отчетные КАК ВТ_Отчетные
        ПОЛНОЕ СОЕДИНЕНИЕ ВТ_Прошлые КАК ВТ_Прошлые
        ПО ВТ_Отчетные.ИнформационнаяКарта = ВТ_Прошлые.ИнформационнаяКарта
СГРУППИРОВАТЬ ПО
    ВТ_Отчетные.ИнформационнаяКарта,
    ВТ_Прошлые.ИнформационнаяКарта
;
////////////////////////////////////////////////////////////­////////////////////
ВЫБРАТЬ
    ЗаказНаряд.Ссылка КАК Чек,
    ЗаказНарядПрошлый.Ссылка КАК ЧекПрошлый,
    ЗаказНаряд.Подразделение.Сеть КАК Сеть,
    ЗаказНарядПрошлый.Подразделение.Сеть КАК СетьПрошлая,
    ВТ_Отчетные.Сумма КАК СуммаОтчет,
    ВТ_Прошлые.Сумма КАК СуммаПрошлая,
    ВТ_Количество.КолвоКлиентовОтчет КАК КолвоКлиентовОтчет,
    ВТ_Количество.КолвоЗаказовОтчет КАК КолвоЗаказовОтчет,
    ВТ_Количество.КолвоКлиентовПрошлое КАК КолвоКлиентовПрошлое,
    ВТ_Количество.КолвоЗаказовПрошлое КАК КолвоЗаказовПрошлое
ПОМЕСТИТЬ ВТ_Сводная
ИЗ
    ВТ_Отчетные КАК ВТ_Отчетные
        ЛЕВОЕ СОЕДИНЕНИЕ Документ.ЗаказНаряд КАК ЗаказНаряд
        ПО ВТ_Отчетные.Дата = ЗаказНаряд.Дата
            И (ЗаказНаряд.ИнформационнаяКарта = ВТ_Отчетные.ИнформационнаяКарта)
        ЛЕВОЕ СОЕДИНЕНИЕ ВТ_Прошлые КАК ВТ_Прошлые
        ПО ВТ_Отчетные.ИнформационнаяКарта = ВТ_Прошлые.ИнформационнаяКарта
        ЛЕВОЕ СОЕДИНЕНИЕ Документ.ЗаказНаряд КАК ЗаказНарядПрошлый
        ПО (ВТ_Прошлые.Дата = ЗаказНарядПрошлый.Дата)
            И (ЗаказНарядПрошлый.ИнформационнаяКарта = ВТ_Прошлые.ИнформационнаяКарта)
        ЛЕВОЕ СОЕДИНЕНИЕ ВТ_Количество КАК ВТ_Количество
        ПО ВТ_Отчетные.ИнформационнаяКарта = ВТ_Количество.ИнформационнаяКарта
        ЛЕВОЕ СОЕДИНЕНИЕ ВТ_Количество КАК ВТ_КоличествоПрошлое
        ПО (ВТ_Прошлые.ИнформационнаяКарта = ВТ_Количество.ИнформационнаяКарта)
;
////////////////////////////////////////////////////////////­////////////////////
ВЫБРАТЬ
    СУММА(ВЫБОР КОГДА ВТ_Сводная.ЧекПрошлый ЕСТЬ NULL И (ВТ_Сводная.Сеть = &Сеть И ЕСТЬNULL(ВТ_Сводная.СетьПрошлая, 0) = 0) ИЛИ ВТ_Сводная.Сеть = &Сеть И ВТ_Сводная.СетьПрошлая = &Сеть ТОГДА ВТ_Сводная.КолвоКлиентовОтчет + ВТ_Сводная.КолвоКлиентовПрошлое ИНАЧЕ 0 КОНЕЦ) КАК Кол4_Новый_наша,
    СУММА(ВЫБОР КОГДА ВТ_Сводная.ЧекПрошлый ЕСТЬ NULL И ВТ_Сводная.СетьПрошлая <> &Сеть ТОГДА ВТ_Сводная.КолвоКлиентовОтчет ИНАЧЕ 0 КОНЕЦ) КАК Кол4_Новый_Вне,
    СУММА(ВЫБОР КОГДА НЕ ВТ_Сводная.ЧекПрошлый ЕСТЬ NULL И (ВТ_Сводная.Сеть = &Сеть И ЕСТЬNULL(ВТ_Сводная.СетьПрошлая, 0) = 0) ИЛИ ВТ_Сводная.Сеть = &Сеть И ВТ_Сводная.СетьПрошлая = &Сеть ТОГДА ВТ_Сводная.КолвоКлиентовОтчет + ВТ_Сводная.КолвоКлиентовПрошлое ИНАЧЕ 0 КОНЕЦ) КАК Кол4_Постоянный_наша,
    СУММА(ВЫБОР КОГДА НЕ ВТ_Сводная.ЧекПрошлый ЕСТЬ NULL И ВТ_Сводная.СетьПрошлая <> &Сеть ТОГДА ВТ_Сводная.КолвоКлиентовОтчет + ВТ_Сводная.КолвоКлиентовПрошлое ИНАЧЕ 0 КОНЕЦ) КАК Кол4_Постоянный_вне,
    СУММА(ВЫБОР КОГДА ВТ_Сводная.ЧекПрошлый ЕСТЬ NULL И (ВТ_Сводная.Сеть = &Сеть И ЕСТЬNULL(ВТ_Сводная.СетьПрошлая, 0) = 0) ИЛИ ВТ_Сводная.Сеть = &Сеть И ВТ_Сводная.СетьПрошлая = &Сеть ТОГДА ВТ_Сводная.КолвоЧековОтчет + ВТ_Сводная.КолвоЧековПрошлое ИНАЧЕ 0 КОНЕЦ) КАК Кол5_Новый_наша,
    СУММА(ВЫБОР КОГДА ВТ_Сводная.ЧекПрошлый ЕСТЬ NULL И ВТ_Сводная.СетьПрошлая <> &Сеть ТОГДА ВТ_Сводная.КолвоЧековОтчет ИНАЧЕ 0 КОНЕЦ) КАК Кол5_Новый_Вне,
    СУММА(ВЫБОР КОГДА НЕ ВТ_Сводная.ЧекПрошлый ЕСТЬ NULL И (ВТ_Сводная.Сеть = &Сеть И ЕСТЬNULL(ВТ_Сводная.СетьПрошлая, 0) = 0) ИЛИ ВТ_Сводная.Сеть = &Сеть И ВТ_Сводная.СетьПрошлая = &Сеть ТОГДА ВТ_Сводная.КолвоЧековОтчет + ВТ_Сводная.КолвоЧековПрошлое ИНАЧЕ 0 КОНЕЦ) КАК Кол5_Постоянный_наша,
    СУММА(ВЫБОР КОГДА НЕ ВТ_Сводная.ЧекПрошлый ЕСТЬ NULL И ВТ_Сводная.СетьПрошлая <> &Сеть ТОГДА ВТ_Сводная.КолвоЧековОтчет + ВТ_Сводная.КолвоЧековПрошлое ИНАЧЕ 0 КОНЕЦ) КАК Кол5_Постоянный_вне,
    СУММА(ВЫБОР КОГДА ВТ_Сводная.ЧекПрошлый ЕСТЬ NULL И (ВТ_Сводная.Сеть = &Сеть И ЕСТЬNULL(ВТ_Сводная.СетьПрошлая, 0) = 0) ИЛИ ВТ_Сводная.Сеть = &Сеть И ВТ_Сводная.СетьПрошлая = &Сеть ТОГДА ВТ_Сводная.СуммаПрошлая + ВТ_Сводная.СуммаОтчет ИНАЧЕ 0 КОНЕЦ) КАК Кол6_Новый_наша,
    СУММА(ВЫБОР КОГДА ВТ_Сводная.ЧекПрошлый ЕСТЬ NULL И ВТ_Сводная.СетьПрошлая <> &Сеть ТОГДА ВТ_Сводная.СуммаПрошлая + ВТ_Сводная.СуммаОтчет ИНАЧЕ 0 КОНЕЦ) КАК Кол6_Новый_Вне,
    СУММА(ВЫБОР КОГДА НЕ ВТ_Сводная.ЧекПрошлый ЕСТЬ NULL И (ВТ_Сводная.Сеть = &Сеть И ЕСТЬNULL(ВТ_Сводная.СетьПрошлая, 0) = 0) ИЛИ ВТ_Сводная.Сеть = &Сеть И ВТ_Сводная.СетьПрошлая = &Сеть ТОГДА ВТ_Сводная.СуммаПрошлая + ВТ_Сводная.СуммаОтчет ИНАЧЕ 0 КОНЕЦ) КАК Кол6_Постоянный_наша,
    СУММА(ВЫБОР КОГДА НЕ ВТ_Сводная.ЧекПрошлый ЕСТЬ NULL И ВТ_Сводная.СетьПрошлая <> &Сеть ТОГДА ВТ_Сводная.СуммаПрошлая + ВТ_Сводная.СуммаОтчет ИНАЧЕ 0 КОНЕЦ) КАК Кол6_Постоянный_вне
ИЗ
    ВТ_Сводная КАК ВТ_Сводная
СГРУППИРОВАТЬ ПО
    ВТ_Сводная.Чек,
    ВТ_Сводная.ЧекПрошлый,
    ВТ_Сводная.Сеть,
    ВТ_Сводная.СетьПрошлая
;
////////////////////////////////////////////////////////////­////////////////////
УНИЧТОЖИТЬ ВТ_Отчетные
;
////////////////////////////////////////////////////////////­////////////////////
УНИЧТОЖИТЬ ВТ_Прошлые
;
////////////////////////////////////////////////////////////­////////////////////
УНИЧТОЖИТЬ ВТ_Количество
;
////////////////////////////////////////////////////////////­////////////////////
УНИЧТОЖИТЬ ВТ_Сводная

Первое, что бросается в глаза в этом запросе – это многократное обращение к объемной таблице Документ.ЧекККМ с тяжелым условием В ИЕРАРХИИ(&СервисныеЦентры). Это условие может приводить к рекурсивным выборкам на стороне SQL Server, что крайне неэффективно для больших объемов данных. Также мы видим несколько ЛЕВОЕ СОЕДИНЕНИЕ, которые могут быть дорогими.

Оптимизация условия "В ИЕРАРХИИ"

1. Проблема с В ИЕРАРХИИ

   Функция В ИЕРАРХИИ в запросах 1С транслируется в SQL-запросы с использованием рекурсивных CTE (Common Table Expressions) или множественных соединений. Для небольших иерархий это работает быстро, но на большой таблице Документ.ЧекККМ и объемной иерархии подразделений это условие становится главным "тормозом". Каждый раз, когда мы используем его, SQL Server должен заново вычислять всю ветку иерархии для каждого документа, попадающего в выборку.

2. Решение: Выборка иерархии во временную таблицу

   Гораздо более эффективным подходом является предварительная выборка всех необходимых элементов иерархии в отдельную временную таблицу, а затем использование этой временной таблицы для соединения. Это позволяет SQL Server один раз определить всю иерархию и затем быстро использовать ее для фильтрации.

   Рассмотрим, как это реализовать. Сначала мы создаем временную таблицу ВТ_СервисныеЦентры, содержащую все подразделения из указанной иерархии:

   Скопировать код

   
   ВЫБРАТЬ РАЗРЕШЕННЫЕ
       Подразделения.Ссылка КАК Ссылка
   ПОМЕСТИТЬ ВТ_СервисныеЦентры
   ИЗ
       Справочник.Подразделения КАК Подразделения
   ГДЕ
       Подразделения.Ссылка В ИЕРАРХИИ(&СервисныеЦентры)
   ИНДЕКСИРОВАТЬ ПО
       Ссылка
   ;
   

   Важно: мы сразу индексируем временную таблицу по полю Ссылка. Это значительно ускорит операции соединения с ней. После этого мы можем заменить условие В ИЕРАРХИИ(&СервисныеЦентры) на ВНУТРЕННЕЕ СОЕДИНЕНИЕ с нашей новой временной таблицей:

   Скопировать код

   
   ВЫБРАТЬ РАЗРЕШЕННЫЕ
       МИНИМУМ(ДокЧекККМ.Дата) КАК Дата,
       ДокЧекККМ.БонуснаяКарта КАК ИнформационнаяКарта,
       СУММА(ДокЧекККМ.СуммаДокумента) КАК Сумма
   ПОМЕСТИТЬ ВТ_Отчетные
   ИЗ
       Документ.ЧекККМ КАК ДокЧекККМ
           ВНУТРЕННЕЕ СОЕДИНЕНИЕ ВТ_СервисныеЦентры КАК ВТ_СервисныеЦентры
           ПО ДокЧекККМ.Подразделение = ВТ_СервисныеЦентры.Ссылка
   ГДЕ
       ДокЧекККМ.Проведен
       И ДокЧекККМ.Дата МЕЖДУ &НачДата И &КонДата
   {ГДЕ
       (НЕ ДокЧекККМ.БонуснаяКарта В (&ИсключитьИнформационныеКарты)) КАК Поле2,
       (ДокЧекККМ.Подразделение.КаналСбыта = &КаналСбыта) КАК Поле4}
   СГРУППИРОВАТЬ ПО
       ДокЧекККМ.БонуснаяКарта
   ;
   

   Аналогичные изменения следует внести и во второй запрос, создающий ВТ_Прошлые.

3. Альтернатива: Денормализация иерархии

   Если структура иерархии подразделений меняется редко, мы можем рассмотреть более радикальный, но очень эффективный подход – денормализацию. Это означает, что в таблице Документ.ЧекККМ мы можем хранить не только непосредственное подразделение, но и ссылки на всех вышестоящих родителей или даже строковый путь по иерархии. Например, можно добавить в документ поле "СписокРодителей" или "ПолныйПутьПодразделения". Это позволит полностью исключить необходимость рекурсивных запросов или построения временных таблиц для иерархии при каждом выполнении отчета, значительно ускоряя выборку.

Оптимизация запросов для разных периодов и соединений

Исходный запрос многократно обращается к Документ.ЧекККМ, что неоптимально. Мы можем улучшить это, сократив количество обращений к тяжелым таблицам и пересмотрев логику соединений.

1. Объединение первичных выборок

   Вместо двух отдельных запросов для отчетного и прошлого периодов (ВТ_Отчетные и ВТ_Прошлые), мы можем попробовать объединить выборку данных из Документ.ЧекККМ в одну временную таблицу, добавив признак периода. Это позволит один раз прочитать данные из основной таблицы, а затем уже работать с более компактной и предварительно отфильтрованной временной таблицей.

   Пример возможной структуры:

   Скопировать код

   
   ВЫБРАТЬ
       ДокЧекККМ.Дата КАК Дата,
       ДокЧекККМ.БонуснаяКарта КАК ИнформационнаяКарта,
       СУММА(ДокЧекККМ.СуммаДокумента) КАК Сумма,
       ИСТИНА КАК ЭтоОтчетныйПериод,
       ЛОЖЬ КАК ЭтоПрошлыйПериод
   ПОМЕСТИТЬ ВТ_ВсеПериоды
   ИЗ
       Документ.ЧекККМ КАК ДокЧекККМ
           ВНУТРЕННЕЕ СОЕДИНЕНИЕ ВТ_СервисныеЦентры КАК ВТ_СервисныеЦентры
           ПО ДокЧекККМ.Подразделение = ВТ_СервисныеЦентры.Ссылка
   ГДЕ
       ДокЧекККМ.Проведен
       И ДокЧекККМ.Дата МЕЖДУ &НачДата И &КонДата
   СГРУППИРОВАТЬ ПО
       ДокЧекККМ.БонуснаяКарта, ДокЧекККМ.Дата
   ОБЪЕДИНИТЬ ВСЕ
   ВЫБРАТЬ
       ДокЧекККМ.Дата,
       ДокЧекККМ.БонуснаяКарта,
       СУММА(ДокЧекККМ.СуммаДокумента),
       ЛОЖЬ,
       ИСТИНА
   ИЗ
       Документ.ЧекККМ КАК ДокЧекККМ
           ВНУТРЕННЕЕ СОЕДИНЕНИЕ ВТ_СервисныеЦентры КАК ВТ_СервисныеЦентры
           ПО ДокЧекККМ.Подразделение = ВТ_СервисныеЦентры.Ссылка
   ГДЕ
       ДокЧекККМ.Проведен
       И ДокЧекККМ.Дата МЕЖДУ &НачДатаПрошлые И &КонДатаПрошлые
   СГРУППИРОВАТЬ ПО
       ДокЧекККМ.БонуснаяКарта, ДокЧекККМ.Дата
   ИНДЕКСИРОВАТЬ ПО
       ИнформационнаяКарта, Дата, ЭтоОтчетныйПериод, ЭтоПрошлыйПериод
   ;
   

   Далее мы уже можем работать с ВТ_ВсеПериоды для построения агрегатов и определения "новых" и "постоянных" клиентов.

2. Пересмотр логики соединения для прошлых и текущих периодов

   Для сравнения клиентов из отчетного и прошлого периодов часто используют ЛЕВОЕ СОЕДИНЕНИЕ. Однако, если вы хотите выявить клиентов, которые присутствовали хотя бы в одном из периодов, более эффективным может быть использование ОБЪЕДИНИТЬ (UNION) с последующей группировкой или применение ПОЛНОЕ СОЕДИНЕНИЕ (FULL OUTER JOIN) на уровне уже агрегированных временных таблиц, как это сделано в ВТ_Количество.

   Когда мы используем ОБЪЕДИНИТЬ, как предлагалось на форуме, и получаем несколько строк по одной карте, это означает, что нам нужно "свернуть" эти строки на следующем этапе. Это можно сделать через агрегатные функции, такие как СУММА или МАКСИМУМ, группируя по информационной карте, чтобы определить, была ли она в текущем и/или прошлом периоде.

   Например, после создания ВТ_ВсеПериоды, мы можем построить общую сводную таблицу по клиентам:

   Скопировать код

   
   ВЫБРАТЬ
       ИнформационнаяКарта КАК ИнформационнаяКарта,
       МАКСИМУМ(ВЫБОР КОГДА ЭтоОтчетныйПериод ТОГДА ИСТИНА ИНАЧЕ ЛОЖЬ КОНЕЦ) КАК БылаВОтчетномПериоде,
       МАКСИМУМ(ВЫБОР КОГДА ЭтоПрошлыйПериод ТОГДА ИСТИНА ИНАЧЕ ЛОЖЬ КОНЕЦ) КАК БылаВПрошломПериоде,
       СУММА(ВЫБОР КОГДА ЭтоОтчетныйПериод ТОГДА Сумма ИНАЧЕ 0 КОНЕЦ) КАК СуммаОтчет,
       СУММА(ВЫБОР КОГДА ЭтоПрошлыйПериод ТОГДА Сумма ИНАЧЕ 0 КОНЕЦ) КАК СуммаПрошлая
   ПОМЕСТИТЬ ВТ_КлиентыИПериоды
   ИЗ
       ВТ_ВсеПериоды
   СГРУППИРОВАТЬ ПО
       ИнформационнаяКарта
   ИНДЕКСИРОВАТЬ ПО
       ИнформационнаяКарта
   ;
   

   Таким образом, мы получим одну строку на каждую бонусную карту, в которой будет информация о ее присутствии в каждом из периодов и суммарные данные. После этого уже гораздо проще определить "новых" (была только в отчетном), "постоянных" (была в обоих) или "вернувшихся" (была только в прошлом, но нет в отчетном) клиентов, а также их суммы и количество чеков.

   Если нам нужны детали по конкретным чекам или заказам, как в исходном запросе с Документ.ЗаказНаряд, то соединения с этими документами лучше производить уже после первичной агрегации по клиентам и периодам, когда объем данных значительно уменьшен.

3. Разделение больших запросов на части

   При отладке и оптимизации полезно выполнять запрос по частям, как и было предложено. Мы создаем временные таблицы для каждого логического блока, а затем передаем их в качестве источника данных для следующих шагов. Это помогает локализовать узкие места и понять, на каком этапе запрос начинает "тормозить".

Профилирование и индексы на стороне SQL Server

Даже идеально написанный запрос 1С может работать медленно, если база данных SQL Server неоптимально настроена. Здесь мы рассмотрим ключевые аспекты, которые следует проверить.

1. Анализ плана выполнения запроса SQL Server

   Самый эффективный способ понять, где именно "тормозит" запрос, – это получить и проанализировать фактический план выполнения запроса на стороне SQL Server. План покажет, какие операции занимают больше всего времени и ресурсов (например, сканирование таблиц вместо поиска по индексу, дорогие сортировки, неэффективные соединения). Для этого используйте SQL Server Management Studio (SSMS), включив "Include Actual Execution Plan" перед выполнением запроса. Мы увидим графическое представление или текстовый XML-файл с подробной информацией о каждом шаге выполнения запроса, его стоимости и использовании ресурсов.

2. Актуальность статистики SQL Server

   Устаревшая статистика на SQL Server может приводить к построению неоптимальных планов выполнения запросов. SQL Server использует статистику для оценки количества строк, которые будут возвращены операциями, и выбора наиболее эффективного пути выполнения запроса. Если статистика неактуальна, оптимизатор может принять неверные решения. Мы должны обеспечить регулярное обновление статистики по таблицам, участвующим в запросе, особенно по полям, используемым в условиях ГДЕ (например, Дата, Проведен, БонуснаяКарта, Подразделение в Документ.ЧекККМ) и в условиях СОЕДИНЕНИЕ. Мы можем настроить автоматическое обновление статистики или выполнять его вручную для критически важных таблиц.

3. Оптимальное индексирование

   Помимо индексирования временных таблиц, крайне важно убедиться в наличии оптимальных индексов на основных таблицах базы данных 1С, используемых в запросе. Для таблицы Документ.ЧекККМ:

   • Поле Дата: часто полезен кластерный индекс, так как данные часто выбираются по дате.
   • Поля Проведен, БонуснаяКарта, Подразделение: для этих полей должны быть некластерные индексы, поскольку они используются в условиях фильтрации и соединений.
   • В таблице Справочник.Подразделения: индекс по полю Ссылка.
   Мы также должны рассмотреть создание композитных индексов, включающих несколько полей из условий ГДЕ или СОЕДИНЕНИЕ. Например, индекс по (Дата, Подразделение, Проведен) на таблице Документ.ЧекККМ может быть очень эффективным для данного запроса. Мы должны проанализировать план выполнения запроса, чтобы выявить отсутствующие или неоптимальные индексы.

4. Дефрагментация индексов

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

5. Очистка кэша планов запросов

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

Дополнительные советы по оптимизации SQL Server

Помимо вышеперечисленного, мы можем учесть еще несколько моментов, специфичных для взаимодействия 1С и SQL Server.

1. Использование параметра КОНЕЦПЕРИОДА или НАЧАЛОПЕРИОДА

   Вместо прямого использования &НачДата и &КонДата в условии МЕЖДУ, особенно когда даты задаются как "1 января 2020 г." и "1 января 2021 г.", что может подразумевать весь день до полуночи, рекомендуется использовать функции НАЧАЛОПЕРИОДА() и КОНЕЦПЕРИОДА() для явного указания границ. Например, ДокЧекККМ.Дата МЕЖДУ НАЧАЛОПЕРИОДА(&НачДата, День) И КОНЕЦПЕРИОДА(&КонДата, День). Это помогает SQL Server более корректно работать с временными границами и эффективнее использовать индексы по дате.

2. Использование хинтов (подсказок) запроса SQL Server (крайний случай)

   В некоторых случаях, когда оптимизатор SQL Server упорно выбирает неоптимальный план, мы можем использовать подсказки запроса (Query Hints) для принудительного использования определенных индексов, типов соединений или изменения поведения оптимизатора. Однако это требует глубокого понимания SQL Server и должно применяться с осторожностью, поскольку может привести к проблемам после обновления версии SQL Server или изменения структуры данных. Это очень мощный инструмент, но его применение должно быть строго обосновано и протестировано.

3. Параметры сервера SQL Server

   Мы должны проверить, что параметры SQL Server, такие как максимальная степень параллелизма (MAXDOP) и порог стоимости параллелизма (Cost Threshold for Parallelism), настроены оптимально для нашей системы и рабочей нагрузки. Неправильные настройки могут приводить к неэффективному использованию ресурсов при выполнении сложных запросов. Например, слишком низкий порог стоимости параллелизма может заставлять SQL Server использовать параллельные планы для запросов, где это невыгодно, что приводит к дополнительным накладным расходам.

Применяя эти подходы, мы сможем значительно улучшить производительность наших запросов 1С на больших объемах данных и обеспечить стабильную работу отчетов даже на длительных исторических периодах.