В процессе работы с базами данных 1С на платформе MS SQL Server разработчики нередко сталкиваются с аномальным поведением запросов: один и тот же запрос при первых запусках отрабатывает моментально, а на третий раз намертво зависает на часы. При этом в файловом варианте информационной базы аналогичный код выполняется без задержек. Рассмотрим подробнее, почему возникает такое зависание, выясним причину с точки зрения работы СУБД и разберем по шагам надежные методы решения проблемы.
Проанализируем типовую ситуацию. Имеется регистр сведений ЦеныАТТ (с периодичностью по позиции регистратора) и запрос следующего вида:
ВЫБРАТЬ
ЦеныПродажные.Склад КАК Склад,
ЦеныПродажные.Номенклатура КАК Номенклатура,
ЦеныПродажные.ХарактеристикаНоменклатуры КАК ХарактеристикаНоменклатуры,
ЦеныПродажные.Цена КАК Цена
ИЗ
РегистрСведений.ЦеныАТТ.СрезПоследних(
&Дата,
Склад В (&СписокСкладов)
И Номенклатура В (&СписокНоменклатуры)) КАК ЦеныПродажные
Когда в параметры &СписокСкладов и &СписокНоменклатуры передаются массивы размером более 200 элементов, запрос дважды выполняется быстро, а на третий вызов уходит в бесконечное чтение.
Чтобы понять, что происходит внутри системы, разберем взаимодействие платформы 1С и SQL Server:
В (&Параметр) передается коллекция значений, содержащая более определенного порога элементов (в зависимости от релиза платформы — от 100 до 256 значений), 1С перестает подставлять их простым перечислением литералов в условие IN (...). Вместо этого платформа неявно создает в системной базе tempdb временную таблицу, наполняет ее переданными значениями и соединяет с основной таблицей регистра. Для такой автоматически созданной таблицы платформа не создает индексов и не строит статистику распределения данных.Период + Регистратор + НомерСтроки. Измерения входят только во вторичные индексы. Виртуальная таблица СрезПоследних разворачивается в сложный SQL-запрос с поиском максимального регистратора и обратным соединением за ресурсами. При ошибочном плане это окончательно обрушивает производительность.Самое простое и быстрое исправление на уровне встроенного языка 1С — устранение избыточных данных из параметров. Если в списке складов физически используется всего два склада, в параметр запроса должны попасть только эти два уникальных значения, а не вся колонка документа на 250 строк.
Посмотрим на пример подготовки массивов:
// Очищаем список складов от дубликатов
ТаблицаСкладов = Новый ТаблицаЗначений;
ТаблицаСкладов.Колонки.Добавить("Склад");
Для Каждого СтрТЧ Из Объект.Товары Цикл
ТаблицаСкладов.Добавить().Склад = СтрТЧ.Склад;
КонецЦикла;
ТаблицаСкладов.Свернуть("Склад");
СписокСкладов = ТаблицаСкладов.ВыгрузитьКолонку("Склад");
// Если размер массива не превышает 100 элементов,
// платформа не создает временную таблицу в tempdb,
// а передает список литералами в текст SQL.
Запрос.УстановитьПараметр("СписокСкладов", СписокСкладов);
Если размер коллекции не превышает порог создания временных таблиц, оптимизатор СУБД строит простой и быстрый план через поиск по индексу (Index Seek).
Если список номенклатуры сам по себе содержит сотни или тысячи уникальных позиций, дедупликация массива не спасет от превышения порога. В этом случае правильный архитектурный подход — перенести отбор в явную временную таблицу и обязательно ее проиндексировать.
Разберем по шагам текст оптимизированного пакетного запроса:
ВЫБРАТЬ РАЗЛИЧНЫЕ
ТЧ.Склад КАК Склад,
ТЧ.Номенклатура КАК Номенклатура
ПОМЕСТИТЬ ВТ_Отбор
ИЗ
&ТаблицаТоваров КАК ТЧ
ИНДЕКСИРОВАТЬ ПО
Склад,
Номенклатура
;
////////////////////////////////////////////////////////////////////////////////
ВЫБРАТЬ
Цены.Склад КАК Склад,
Цены.Номенклатура КАК Номенклатура,
Цены.ХарактеристикаНоменклатуры КАК ХарактеристикаНоменклатуры,
Цены.Цена КАК Цена
ИЗ
РегистрСведений.ЦеныАТТ.СрезПоследних(
&Дата,
(Склад, Номенклатура) В
(ВЫБРАТЬ
ВТ.Склад,
ВТ.Номенклатура
ИЗ
ВТ_Отбор КАК ВТ)) КАК Цены
Почему этот вариант работает стабильно:
ИНДЕКСИРОВАТЬ ПО. Платформа 1С отдает команду СУБД на создание полноценного индекса по полям Склад и Номенклатура.РАЗЛИЧНЫЕ исключает дублирующиеся строки еще до обращения к регистру.(Склад, Номенклатура) В (...) позволяет СУБД однозначно применить эффективный план поиска и исключает возникновение губительных вложенных циклов даже при последующих кэшированиях плана выполнения.Для регистров сведений с режимом записи Позиция регистратора и большим объемом движений штатная виртуальная таблица может работать медленно даже с индексированными временными таблицами. В таких случаях высокую скорость дает ручной срез через физическую таблицу регистра.
Посмотрим на пример реализации алгоритма:
// Шаг 1: Выбираем только актуальные записи регистра до нужной даты с отбором
ВЫБРАТЬ
Рег.Склад КАК Склад,
Рег.Номенклатура КАК Номенклатура,
Рег.ХарактеристикаНоменклатуры КАК ХарактеристикаНоменклатуры,
Рег.Период КАК Период,
Рег.Регистратор КАК Регистратор,
Рег.НомерСтроки КАК НомерСтроки
ПОМЕСТИТЬ ВТ_ДвиженияПоОтбору
ИЗ
РегистрСведений.ЦеныАТТ КАК Рег
ГДЕ
Рег.Период <= &Дата
И (Рег.Склад, Рег.Номенклатура) В
(ВЫБРАТЬ ВТ.Склад, ВТ.Номенклатура ИЗ ВТ_Отбор КАК ВТ)
ИНДЕКСИРОВАТЬ ПО
Склад,
Номенклатура,
ХарактеристикаНоменклатуры
;
////////////////////////////////////////////////////////////////////////////////
// Шаг 2: Находим максимальную дату/момент времени для каждой комбинации измерений
ВЫБРАТЬ
Движения.Склад КАК Склад,
Движения.Номенклатура КАК Номенклатура,
Движения.ХарактеристикаНоменклатуры КАК ХарактеристикаНоменклатуры,
МАКСИМУМ(Движения.Период) КАК МаксПериод
ПОМЕСТИТЬ ВТ_МаксПериоды
ИЗ
ВТ_ДвиженияПоОтбору КАК Движения
СГРУППИРОВАТЬ ПО
Движения.Склад,
Движения.Номенклатура,
Движения.ХарактеристикаНоменклатуры
ИНДЕКСИРОВАТЬ ПО
Склад,
Номенклатура,
ХарактеристикаНоменклатуры,
МаксПериод
;
////////////////////////////////////////////////////////////////////////////////
// ШАГ 3: Получаем итоговые цены соединением с исходной таблицей
ВЫБРАТЬ
Цены.Склад КАК Склад,
Цены.Номенклатура КАК Номенклатура,
Цены.ХарактеристикаНоменклатуры КАК ХарактеристикаНоменклатуры,
Цены.Цена КАК Цена
ИЗ
ВТ_МаксПериоды КАК Макс
ВНУТРЕННЕЕ СОЕДИНЕНИЕ РегистрСведений.ЦеныАТТ КАК Цены
ПО Макс.Склад = Цены.Склад
И Макс.Номенклатура = Цены.Номенклатура
И Макс.ХарактеристикаНоменклатуры = Цены.ХарактеристикаНоменклатуры
И Макс.МаксПериод = Цены.Период
Такой подход полностью контролирует структуру временных таблиц и исключает непредсказуемость встроенных механизмов платформы при работе со сложными периодическими регистрами.
Если зависание происходит на рабочей базе прямо сейчас, администратор может применить следующие меры:
ALTER DATABASE SCOPED CONFIGURATION CLEAR PROCEDURE_CACHE;
Это снимет текущее зависание запроса, однако не предотвратит его повторение в будущем.
ALTER DATABASE SCOPED CONFIGURATION SET PARAMETER_SNIFFING = OFF;
Это заставит оптимизатор оценивать распределение по средней плотности данных, что снижает риск построения катастрофических планов. Тем не менее фундаментальным решением остается оптимизация кода 1С через явные индексированные временные таблицы.