В процессе разработки и сопровождения высоконагруженных систем на платформе 1С:Предприятие 8.3 программисты часто сталкиваются с ситуацией, когда запрос к виртуальной таблице остатков регистра накопления начинает «деградировать». Проблема проявляется специфически: в консоли запросов всё отрабатывает мгновенно (менее секунды), а в реальном коде при проведении документа система «зависает». В этой статье мы подробно разберем, почему так происходит, проанализируем влияние структуры метаданных на производительность SQL Server и выясним, как избавиться от ежедневной необходимости обновлять статистику в базе данных.
Рассмотрим типичный пример запроса к регистру ПартииТоваровКомпании. Регистр имеет значительный объем (11 Гб, более 1.5 млн строк), 7 измерений, большинство из которых проиндексировано. Запрос выглядит следующим образом:
ВЫБРАТЬ
ПартииТоваровКомпанииОстатки.СкладКомпании КАК СкладКомпании,
ПартииТоваровКомпанииОстатки.Номенклатура КАК Номенклатура,
ПартииТоваровКомпанииОстатки.СтатусПартии КАК СтатусПартии,
ПартииТоваровКомпанииОстатки.Партия КАК Партия,
ВЫБОР
КОГДА ПартииТоваровКомпанииОстатки.СтатусПартии = &Купленные
ТОГДА 1
ИНАЧЕ 0
КОНЕЦ КАК ПорядокСтатуса,
ПартииТоваровКомпанииОстатки.КоличествоОстаток КАК Количество,
ПартииТоваровКомпанииОстатки.СуммаНДСОстаток КАК СуммаНДС,
ПартииТоваровКомпанииОстатки.СуммаОстаток КАК Сумма,
ПартииТоваровКомпанииОстатки.СуммаУпрОстаток КАК СуммаУпр
ИЗ
РегистрНакопления.ПартииТоваровКомпании.Остатки(
&Момент,
Номенклатура В (&Номенклатура)
И СкладКомпании = &СкладКомпании) КАК ПартииТоваровКомпанииОстатки
ДЛЯ ИЗМЕНЕНИЯ
РегистрНакопления.ПартииТоваровКомпании.Остатки
УПОРЯДОЧИТЬ ПО
ПорядокСтатуса УБЫВ,
Партия.Дата УБЫВ,
Партия УБЫВ
На первый взгляд запрос стандартный, однако в нем скрыто несколько «подводных камней», которые приводят к непредсказуемому поведению оптимизатора MS SQL Server.
Проанализируем конструкцию УПОРЯДОЧИТЬ ПО Партия.Дата УБЫВ. Если измерение Партия имеет составной тип данных (например, включает несколько видов документов), то при выполнении запроса на уровне СУБД происходит множественное соединение (LEFT JOIN) основной таблицы со всеми таблицами, входящими в этот составной тип. Это необходимо, чтобы СУБД могла «найти» поле Дата в каждой из возможных таблиц.
Рассмотрим, что происходит внутри системы:
Характеристика.ТипыПартий.Партия в текущей выборке пустое (NULL или Неопределено), SQL Server все равно обязан выполнить эти соединения для проверки условий сортировки.Решение: Следует избегать сортировки по реквизитам измерений через точку. Если сортировка по дате партии критична, рекомендуется добавить реквизит ДатаПартии непосредственно в сам регистр накопления (свойство «Индексировать» поможет ускорить выборку), либо вынести получение дат во временную таблицу в отдельном шаге запроса.
Выясним, почему обновление статистики помогает лишь на короткое время. MS SQL Server кэширует планы выполнения запросов. При первом запуске (или после обновления статистики) он строит план, исходя из переданных параметров (например, списка номенклатуры). Если в первый раз вы выбрали одну позицию номенклатуры, SQL создаст план, оптимизированный под малый объем данных (Index Seek).
Однако, если в следующий раз в параметр &Номенклатура будет передан список из тысячи элементов, старый план может оказаться крайне неэффективным, заставляя сервер выполнять сканирование всей таблицы (Index Scan). Когда вы делаете UPDATE STATISTICS, вы заставляете сервер сбросить старые планы и пересчитать распределение данных, что временно исправляет ситуацию.
Как с этим бороться:
Номенклатура В (&Номенклатура) лучше загрузить список номенклатуры во временную таблицу, проиндексировать её и соединить с виртуальной таблицей остатков.Важно понимать разницу между выполнением в консоли и в модуле проведения. Конструкция ДЛЯ ИЗМЕНЕНИЯ (на уровне SQL это превращается в WITH (UPDLOCK)) накладывает блокировки на считываемые данные.
Разберем ситуацию:
ПартииТоваровКомпании, ваш запрос будет стоять в очереди.Рекомендация: Проверьте актуальность итогов регистра. Если итоги не рассчитаны на текущий месяц, 1С вычисляет остатки путем суммирования всех движений от последней точки актуальности, что при наличии ДЛЯ ИЗМЕНЕНИЯ может заблокировать огромный диапазон данных.
Проанализируем, как можно переписать исходный запрос для повышения стабильности:
УПОРЯДОЧИТЬ ПО:
// Было:
УПОРЯДОЧИТЬ ПО
ПорядокСтатуса УБЫВ,
Партия.Дата УБЫВ,
Партия УБЫВ
// Стало (если Партия пустая или тип позволяет):
УПОРЯДОЧИТЬ ПО
ПорядокСтатуса УБЫВ,
Партия УБЫВ
// 1. Помещаем номенклатуру во временную таблицу
Запрос.Текст = "ВЫБРАТЬ Номенклатура ПОМЕСТИТЬ ВТ_Фильтр ИЗ &СписокНоменклатуры КАК Список;
// 2. Индексируем для быстрого соединения
ИНДЕКСИРОВАТЬ ПО Номенклатура;
// 3. Выбираем остатки с соединением
ВЫБРАТЬ ... ИЗ РегистрНакопления.ПартииТоваровКомпании.Остатки(&Момент,
СкладКомпании = &СкладКомпании) КАК Остатки
ГДЕ Остатки.Номенклатура В (ВЫБРАТЬ Номенклатура ИЗ ВТ_Фильтр)";
SQL Server Profiler или механизмом расширенных событий (Extended Events). Выясним, какое именно событие занимает больше всего времени. Если это Lock:Acquired, значит проблема в блокировках. Если SQL:BatchCompleted с огромным количеством Reads, значит проблема в плане выполнения и индексах.Для решения проблемы «зависания» запроса к остаткам необходимо действовать комплексно:
REBUILD или REORGANIZE индексов таблицы регистра накопления и его итогов.Помните, что ежедневное обновление статистики — это лишь способ «замаскировать» неоптимальный код. Качественная оптимизация структуры запроса позволит системе работать стабильно без постоянного вмешательства администратора СУБД.