При работе с базами данных 1С:Предприятие 8, особенно в клиент-серверном режиме с использованием SQL Server, мы иногда сталкиваемся со странным и на первый взгляд необъяснимым поведением. Одной из таких проблем является зависание системы при выполнении запросов, содержащих конструкцию ВЫБРАТЬ ПЕРВЫЕ N (аналог SELECT TOP N в SQL). Рассмотрим ситуацию, когда типовой запрос 1С из конфигурации "Бухгалтерия предприятия" версии 3.0, используемый в процедуре закрытия месяца, приводит к полному зависанию клиента при его выполнении на SQL Server 2016.
Автор столкнулся с тем, что запрос "вешает клиента намертво", и помогает только сброс текущего серверного вызова. При этом, если в запросе убрать условие ВЫБРАТЬ ПЕРВЫЕ 1, он выполняется корректно, хоть и возвращает 2.5 миллиона строк. Интересно, что в файловой версии базы данных этот же запрос работает без проблем. Ситуация усугубляется тем, что обслуживание базы (перестроение индексов, обновление статистики) и даже выгрузка/загрузка в чистую базу не решили проблему. Более того, было замечено, что зависание происходит только при значениях ПЕРВЫЕ от 1 до 4, а при ПЕРВЫЕ 5 и более запрос отрабатывает нормально. Это явное указание на различия в планах выполнения SQL Server в зависимости от параметра TOP N.
Для начала давайте разберем, почему поведение запросов в файловом и клиент-серверном режимах 1С может существенно различаться. В файловом режиме все операции с данными, включая выполнение запросов, обрабатывает непосредственно клиентское приложение 1С. Это означает, что 1С использует свои внутренние, иногда менее сложные, но более предсказуемые алгоритмы работы с данными. В клиент-серверном режиме, с которым мы имеем дело при использовании SQL Server, архитектура усложняется. Запрос, написанный на языке 1С, сначала обрабатывается сервером 1С:Предприятия, который транслирует его в специфический SQL-запрос. Затем этот SQL-запрос отправляется в СУБД (SQL Server), которая отвечает за его выполнение и возврат результатов серверу 1С, а затем и клиенту.
Такая многозвенная архитектура вносит свои нюансы. Ключевую роль здесь играет оптимизатор запросов SQL Server. Его задача — проанализировать SQL-запрос и выбрать наиболее эффективный план выполнения. Однако, на практике оптимизатор не всегда принимает "оптимальные" решения для всех типов запросов, особенно для тех, что генерируются такими платформами, как 1С. Поведение оптимизатора SQL Server с конструкциями TOP N (ВЫБРАТЬ ПЕРВЫЕ N) в сочетании с UNION ALL (ОБЪЕДИНИТЬ ВСЕ) и ORDER BY может быть особенно капризным. Оптимизатор может неверно оценить стоимость операций и выбрать план, который для получения даже одной записи (ПЕРВЫЕ 1) заставляет его обрабатывать все или значительную часть данных из одной из ветвей объединения, которая может быть "тяжелой".
Наблюдение, что запрос зависает при малых значениях ПЕРВЫЕ (от 1 до 4) и нормально работает при больших (от 5 и выше), является классическим признаком того, что оптимизатор SQL Server выбирает разные планы выполнения. Для очень маленьких TOP N он может использовать стратегию, которая в данном конкретном случае становится крайне неэффективной, например, выполнять полное сканирование одной из ветвей UNION ALL, прежде чем переходить к другой, даже если первая ветвь не содержит нужных данных или является значительно более объемной. Это может быть связано с предположениями оптимизатора о распределении данных или с ошибками оценки кардинальности.
Важно также учитывать, что SQL Server 2016, несмотря на ряд улучшений, мог иметь специфические проблемы с производительностью для определенных типов запросов, особенно если база данных работала на устаревшем уровне совместимости (например, 120 для SQL Server 2014, а не 130 для SQL Server 2016). Новые возможности оптимизатора активируются только при соответствующем уровне совместимости. Кроме того, сложности с множественными условиями типа И НЕ Хозрасчетный.Регистратор ССЫЛКА Документ.X, где Регистратор является полем составного типа, могут существенно усложнить работу оптимизатора, заставляя его выполнять проверки типов для каждой строки.
Давайте подробно рассмотрим сам запрос, который вызывает проблемы. Он является частью типовой конфигурации "Бухгалтерия предприятия" и находится в общем модуле РаботаСПоследовательностями, в функции МоментВремениПервогоДокументаВПоследовательности. Эта функция вызывается из обработки ЗакрытиеМесяца, что объясняет критичность проблемы.
Текст запроса выглядит следующим образом:
ВЫБРАТЬ ПЕРВЫЕ 1
ТаблицаПоследовательности.Регистратор КАК Регистратор,
ТаблицаПоследовательности.Период КАК Период,
ИСТИНА КАК ЭтоДанныеПоследовательности
ИЗ
Последовательность.ДокументыОрганизаций КАК ТаблицаПоследовательности
ГДЕ
ТаблицаПоследовательности.Организация = &Организация
И ТаблицаПоследовательности.Период >= &НачинаяСДаты
ОБЪЕДИНИТЬ ВСЕ
ВЫБРАТЬ
Хозрасчетный.Регистратор,
Хозрасчетный.Период,
ЛОЖЬ
ИЗ
РегистрБухгалтерии.Хозрасчетный КАК Хозрасчетный
ЛЕВОЕ СОЕДИНЕНИЕ Последовательность.ДокументыОрганизаций КАК ТаблицаПоследовательности
ПО Хозрасчетный.Регистратор = ТаблицаПоследовательности.Регистратор
ГДЕ
Хозрасчетный.Организация = &Организация
И Хозрасчетный.Период >= &НачинаяСДаты
И НЕ Хозрасчетный.Регистратор ССЫЛКА Документ.ФормированиеЗаписейКнигиПокупок
И НЕ Хозрасчетный.Регистратор ССЫЛКА Документ.УведомлениеОбОстаткахПрослеживаемыхТоваров
И НЕ Хозрасчетный.Регистратор ССЫЛКА Документ.СправкиНДФЛДляПередачиВНалоговыйОрган
И НЕ Хозрасчетный.Регистратор ССЫЛКА Документ.РаспределениеНДС
И НЕ Хозрасчетный.Регистратор ССЫЛКА Документ.РезервыПоОплатеТруда
И НЕ Хозрасчетный.Регистратор ССЫЛКА Документ.ДепонированиеЗарплаты
И НЕ Хозрасчетный.Регистратор ССЫЛКА Документ.УдалитьНачислениеЗарплатыРаботникамОрганизаций
И НЕ Хозрасчетный.Регистратор ССЫЛКА Документ.ПерерасчетСтраховыхВзносов
И НЕ Хозрасчетный.Регистратор ССЫЛКА Документ.ОперацияНалоговогоУчетаПоНДФЛ
И НЕ Хозрасчетный.Регистратор ССЫЛКА Документ.ОперацияСБилетом
И НЕ Хозрасчетный.Регистратор ССЫЛКА Документ.УдалитьСписаниеДепонентовВДоходыОрганизаций
И НЕ Хозрасчетный.Регистратор ССЫЛКА Документ.СписаниеНДС
И НЕ Хозрасчетный.Регистратор ССЫЛКА Документ.АктПроверкиСтраховыхВзносов
И НЕ Хозрасчетный.Регистратор ССЫЛКА Документ.УдалитьДепонированиеОрганизаций
И НЕ Хозрасчетный.Регистратор ССЫЛКА Документ.НачислениеДивидендов
И НЕ Хозрасчетный.Регистратор ССЫЛКА Документ.РегламентнаяОперация
И НЕ Хозрасчетный.Регистратор ССЫЛКА Документ.ПерерасчетНДФЛ
И НЕ Хозрасчетный.Регистратор ССЫЛКА Документ.ВосстановлениеНДС
И НЕ Хозрасчетный.Регистратор ССЫЛКА Документ.ПодтверждениеНулевойСтавкиНДС
И НЕ Хозрасчетный.Регистратор ССЫЛКА Документ.КорректировкаЕНС
И НЕ Хозрасчетный.Регистратор ССЫЛКА Документ.ФормированиеЗаписейКнигиПродаж
И НЕ Хозрасчетный.Регистратор ССЫЛКА Документ.УдалитьОтражениеЗарплатыВУчете
И НЕ Хозрасчетный.Регистратор ССЫЛКА Документ.СведенияОбУдержанномНДФЛ
И НЕ Хозрасчетный.Регистратор ССЫЛКА Документ.ФормированиеУставногоКапитала
И НЕ Хозрасчетный.Регистратор ССЫЛКА Документ.УдалитьРасчетЕСН
И НЕ Хозрасчетный.Регистратор ССЫЛКА Документ.НачислениеНДСпоСМРхозспособом
И НЕ Хозрасчетный.Регистратор ССЫЛКА Документ.ВосстановлениеНДСПоОбъектамНедвижимости
И НЕ Хозрасчетный.Регистратор ССЫЛКА Документ.СчетФактураПолученный
И НЕ Хозрасчетный.Регистратор ССЫЛКА Документ.СчетФактураВыданный
И ТаблицаПоследовательности.Регистратор ЕСТЬ NULL
УПОРЯДОЧИТЬ ПО
Период,
Регистратор
Рассмотрим ключевые особенности этого запроса:
ОБЪЕДИНИТЬ ВСЕ: Запрос состоит из двух частей, объединенных оператором ОБЪЕДИНИТЬ ВСЕ. Это означает, что результаты обеих частей сначала собираются, а затем обрабатываются как единый набор данных.ВЫБРАТЬ ПЕРВЫЕ 1: Данная конструкция применена только к первой ветви объединения в том виде, в котором автор представил запрос. Однако, в исходном типовом коде (сообщение 30) ВЫБРАТЬ ПЕРВЫЕ 1 применяется к каждой из двух ветвей объединения перед финальной сортировкой. В итоге, если мы выбираем ПЕРВЫЕ 1 из всего результата, то это условие применяется к результату после выполнения ОБЪЕДИНИТЬ ВСЕ и УПОРЯДОЧИТЬ ПО.И ТаблицаПоследовательности.Регистратор ЕСТЬ NULL: Это очень важное условие во второй ветви объединения. Оно означает, что мы выбираем записи из регистра бухгалтерии Хозрасчетный, для которых не нашлось соответствующего регистратора в таблице Последовательность.ДокументыОрганизаций. По сути, эта ветвь ищет документы, которые должны были попасть в последовательность, но по какой-то причине там отсутствуют. Это условие появилось в типовой конфигурации относительно недавно (БП 3.0.150.29), что может влиять на производительность.И НЕ Хозрасчетный.Регистратор ССЫЛКА Документ.X: Список исключаемых документов очень длинный. Для SQL Server это означает необходимость проверки типа каждого регистратора и сравнения его с большим списком возможных типов документов. Хотя 1С пытается оптимизировать это в SQL-предложение типа NOT IN (список_идентификаторов_типов), большое количество таких проверок, особенно при неоптимальных индексах или устаревшей статистике, может сильно замедлять запрос.УПОРЯДОЧИТЬ ПО: Сортировка по Период и Регистратор применяется ко всему результату после объединения. Для получения ПЕРВЫЕ 1 элемента SQL Server должен сначала выполнить всю сортировку, что может быть очень ресурсоемко для больших объемов данных.Критическое наблюдение автора о том, что запрос зависает при ВЫБРАТЬ ПЕРВЫЕ 1-4, но работает при 5+, настойчиво указывает на то, что оптимизатор SQL Server для малых значений N выбирает план, который является катастрофически неэффективным. Возможно, он пытается материализовать всю или большую часть "тяжелой" второй ветви объединения, прежде чем применять ORDER BY и TOP N.
Для эффективного решения этой сложной проблемы нам потребуется комплексный подход, включающий как анализ на уровне SQL Server, так и возможную модификацию запроса 1С.
Это первый и самый важный шаг. Без понимания того, как SQL Server выполняет запрос, любые попытки оптимизации будут носить случайный характер. Мы можем получить план выполнения с помощью:
В плане выполнения мы будем искать следующие моменты:
Table Scan (полное сканирование таблицы), Clustered Index Scan (полное сканирование кластерного индекса), Nested Loops Join (вложенные циклы соединения), если они выполняются над большими объемами данных.Организация и Период в регистрах Хозрасчетный и Последовательность.ДокументыОрганизаций. Особенно важны индексы, которые могут помочь в сортировке (ORDER BY Период, Регистратор) и в условиях соединения.NOT IN: Оптимизатор может столкнуться с трудностями при обработке длинного списка исключаемых типов документов, преобразуя их в SQL-конструкции, которые могут быть неэффективны.Изучение плана выполнения поможет нам выявить "узкие места" и определить, какая именно часть запроса вызывает зависание.
Один из самых эффективных способов обойти проблемы оптимизатора с сложными запросами, особенно содержащими ОБЪЕДИНИТЬ ВСЕ и ВЫБРАТЬ ПЕРВЫЕ, — это его декомпозиция с использованием временных таблиц. Мы можем выполнить каждую часть объединения отдельно, поместив результаты во временные таблицы, а затем выполнить финальное объединение и выборку уже из этих временных таблиц.
Преимущества такого подхода:
ОБЪЕДИНИТЬ ВСЕ и УПОРЯДОЧИТЬ ПО к этим, возможно, уже более компактным и индексированным наборам данных.ПЕРВЫЕ N.Рассмотрим, как можно модифицировать запрос в 1С, используя временные таблицы:
ВЫБРАТЬ
ТаблицаПоследовательности.Регистратор КАК Регистратор,
ТаблицаПоследовательности.Период КАК Период,
ИСТИНА КАК ЭтоДанныеПоследовательности
ПОМЕСТИТЬ ВТ_Последовательность
ИЗ
Последовательность.ДокументыОрганизаций КАК ТаблицаПоследовательности
ГДЕ
ТаблицаПоследовательности.Организация = &Организация
И ТаблицаПоследовательности.Период >= &НачинаяСДаты
;
ВЫБРАТЬ
Хозрасчетный.Регистратор КАК Регистратор,
Хозрасчетный.Период КАК Период,
ЛОЖЬ КАК ЭтоДанныеПоследовательности
ПОМЕСТИТЬ ВТ_Хозрасчетный
ИЗ
РегистрБухгалтерии.Хозрасчетный КАК Хозрасчетный
ЛЕВОЕ СОЕДИНЕНИЕ Последовательность.ДокументовОрганизаций КАК ТаблицаПоследовательности
ПО Хозрасчетный.Регистратор = ТаблицаПоследовательности.Регистратор
ГДЕ
Хозрасчетный.Организация = &Организация
И Хозрасчетный.Период >= &НачинаяСДаты
И НЕ Хозрасчетный.Регистратор ССЫЛКА Документ.ФормированиеЗаписейКнигиПокупок
И НЕ Хозрасчетный.Регистратор ССЫЛКА Документ.УведомлениеОбОстаткахПрослеживаемыхТоваров
// ... (все остальные условия НЕ ССЫЛКА)
И ТаблицаПоследовательности.Регистратор ЕСТЬ NULL
;
ВЫБРАТЬ ПЕРВЫЕ 1
Результат.Регистратор,
Результат.Период,
Результат.ЭтоДанныеПоследовательности
ИЗ
ВТ_Последовательность КАК Результат
ОБЪЕДИНИТЬ ВСЕ
ВЫБРАТЬ
Результат.Регистратор,
Результат.Период,
Результат.ЭтоДанныеПоследовательности
ИЗ
ВТ_Хозрасчетный КАК Результат
УПОРЯДОЧИТЬ ПО
Период,
Регистратор
Этот подход гарантирует, что каждая часть запроса будет выполнена до того, как начнется объединение и сортировка, что может значительно улучшить предсказуемость плана выполнения для SQL Server.
Длинный список условий И НЕ Хозрасчетный.Регистратор ССЫЛКА Документ.X может быть очень неэффективен. Вместо множественных сравнений ссылок, мы можем собрать список исключаемых типов документов в массив и передать его в запрос как параметр.
Преимущества этого подхода:
NOT (ТИП = 'Документ.X' ИЛИ ТИП = 'Документ.Y' ...), 1С сгенерирует более компактное ТИПЗНАЧЕНИЯ(Регистратор) NOT IN (список_идентификаторов_типов), что может быть более эффективно для оптимизатора.Пример кода 1С для формирования и использования такого массива:
// Создаем массив исключаемых типов
МассивИсключаемыхТипов = Новый Массив();
МассивИсключаемыхТипов.Добавить(Тип("Документ.ФормированиеЗаписейКнигиПокупок"));
МассивИсключаемыхТипов.Добавить(Тип("Документ.УведомлениеОбОстаткахПрослеживаемыхТоваров"));
// ... добавьте все остальные исключаемые типы документов
МассивИсключаемыхТипов.Добавить(Тип("Документ.СчетФактураПолученный"));
МассивИсключаемыхТипов.Добавить(Тип("Документ.СчетФактураВыданный"));
Запрос = Новый Запрос;
Запрос.УстановитьПараметр("Организация", Организация);
Запрос.УстановитьПараметр("НачинаяСДаты", НачинаяСДаты);
Запрос.УстановитьПараметр("МассивИсключаемыхТипов", МассивИсключаемыхТипов);
Запрос.Текст = "
// ... (Первая ветвь ОБЪЕДИНИТЬ ВСЕ без изменений)
ОБЪЕДИНИТЬ ВСЕ
ВЫБРАТЬ
Хозрасчетный.Регистратор,
Хозрасчетный.Период,
ЛОЖЬ
ИЗ
РегистрБухгалтерии.Хозрасчетный КАК Хозрасчетный
ЛЕВОЕ СОЕДИНЕНИЕ Последовательность.ДокументовОрганизаций КАК ТаблицаПоследовательности
ПО Хозрасчетный.Регистратор = ТаблицаПоследовательности.Регистратор
ГДЕ
Хозрасчетный.Организация = &Организация
И Хозрасчетный.Период >= &НачинаяСДаты
И ТИПЗНАЧЕНИЯ(Хозрасчетный.Регистратор) НЕ В (&МассивИсключаемыхТипов) // Оптимизированное условие
И ТаблицаПоследовательности.Регистратор ЕСТЬ NULL
УПОРЯДОЧИТЬ ПО
Период,
Регистратор";
// ... дальнейшее выполнение запроса
Такой подход делает запрос более "чистым" для оптимизатора SQL Server и уменьшает сложность проверки типов.
Даже при идеальном запросе настройки самого SQL Server играют ключевую роль в производительности:
130 для SQL Server 2016). Это гарантирует использование всех актуальных оптимизаций запросов.Max Degree of Parallelism (MAXDOP): Для экземпляров SQL Server, работающих с 1С, часто рекомендуется устанавливать значение MAXDOP = 1. Платформа 1С обычно не рассчитана на параллельное выполнение запросов внутри СУБД, и распараллеливание может приводить к избыточным накладным расходам и замедлениям.Lock pages in memory (Блокировка страниц в памяти), чтобы предотвратить выгрузку памяти SQL Server в файл подкачки.Иногда проблемы производительности могут быть вызваны ошибками или особенностями конкретной версии платформы 1С. Обновление платформы до актуального стабильного релиза может содержать исправления, улучшающие взаимодействие с СУБД и оптимизацию запросов.
Проблема зависания запроса 1С с ВЫБРАТЬ ПЕРВЫЕ 1 на SQL Server является сложной и многогранной, требующей комплексного подхода к диагностике и решению. Переход на файловую базу, хотя и решает проблему временно, не является долгосрочным решением для производительной работы в клиент-серверном режиме.
Настоящее решение заключается в глубоком анализе плана выполнения SQL Server, понимании того, как оптимизатор обрабатывает сложные запросы 1С, и, при необходимости, в адаптации самого запроса (например, через декомпозицию с временными таблицами или оптимизацию условий). Совместно с правильной настройкой SQL Server это поможет добиться стабильной и быстрой работы вашей информационной базы 1С.