Разработчики 1С часто сталкиваются с ситуацией, когда запрос к виртуальной таблице остатков, отлично работающий на тестовой базе с небольшим объемом данных, начинает катастрофически замедляться в реальной эксплуатации. Особенно остро эта проблема проявляется при проведении документов (например, расходных накладных), когда в параметры виртуальной таблицы передается массив из десятков или сотен товаров. В дополнение к низкой скорости выполнения СУБД может неожиданно заблокировать всю таблицу регистра, парализуя работу остальных пользователей системы.
В этой статье мы подробно разберем, почему возникают такие проблемы, проанализируем внутреннее устройство индексов СУБД, рассмотрим эффективные методы оптимизации запросов с помощью временных таблиц и выясним, как победить избыточные блокировки.
Рассмотрим классический запрос, с которого началось обсуждение на форуме:
ВЫБРАТЬ
ОстаткиОстатки.Товар КАК Товар,
ОстаткиОстатки.Склад,
ОстаткиОстатки.Сектор,
ОстаткиОстатки.Ячейка,
ОстаткиОстатки.МеркурийПартия,
ОстаткиОстатки.Сектор.Сортировка КАК Приоритет,
ОстаткиОстатки.ОстатокОстаток - ОстаткиОстатки.РезервОстаток КАК Остаток,
ОстаткиОстатки.БазСуммаОстаток КАК База,
ОстаткиОстатки.ВалСуммаОстаток КАК Вала,
ОстаткиОстатки.ОстатокОстаток КАК Всего
ИЗ
РегистрНакопления.Остатки.Остатки(, Товар В (&Товары)) КАК ОстаткиОстатки
ГДЕ
ОстаткиОстатки.ОстатокОстаток - ОстаткиОстатки.РезервОстаток > 0
На первый взгляд запрос выглядит стандартно. Передача списка товаров в параметры виртуальной таблицы — это правильный шаг (в отличие от наложения фильтра в секции ГДЕ). Однако при увеличении количества элементов в массиве &Товары производительность СУБД резко падает. Выясним причину этого поведения.
Когда платформа 1С транслирует этот запрос на язык SQL, массив параметров преобразуется в конструкцию IN (@P1, @P2, ... @Pn). Для небольшого списка (до 10–20 элементов) оптимизатор MS SQL Server строит план с использованием операции Index Seek (поиск по индексу). СУБД быстро находит нужные записи, так как измерение Товар является первым в кластерном индексе таблицы итогов регистра накопления.
Но если список товаров становится большим (например, более 50-100 наименований), оптимизатор СУБД сталкивается с дилеммой. Стоимость множественных точечных поисков по индексу (Index Seek) в цикле начинает превышать стоимость одного последовательного сканирования всей таблицы. В этот момент оптимизатор принимает решение отказаться от индекса и переходит к операции Index Scan (сканирование индекса) или даже Table Scan (сканирование таблицы). Запрос начинает «читать» всю таблицу итогов целиком, что приводит к росту времени выполнения с долей секунды до нескольких секунд (вплоть до 8–60 секунд при высокой нагрузке).
Чтобы гарантировать использование эффективного поиска по индексу вне зависимости от объема списка товаров, мы должны отказаться от прямой передачи массива в параметры виртуальной таблицы. Вместо этого применим механизм временных таблиц.
Разберем этот алгоритм по шагам:
Товар. Без индексации временной таблицы СУБД не сможет выполнить быстрое соединение.Посмотрим на практический пример реализации такого запроса:
// Шаг 1. Помещаем список товаров во временную таблицу и индексируем её
ВЫБРАТЬ
ТЧ_Товары.Товар КАК Товар
ПОМЕСТИТЬ ВТ_ФильтрТоваров
ИЗ
&ТаблицаТоваров КАК ТЧ_Товары
СГРУППИРОВАТЬ ПО
ТЧ_Товары.Товар
ИНДЕКСИРОВАТЬ ПО
Товар
;
// Шаг 2. Получаем остатки с фильтром по временной таблице
ВЫБРАТЬ
Остатки.Товар КАК Товар,
Остатки.Склад КАК Склад,
Остатки.Сектор КАК Сектор,
Остатки.Ячейка КАК Ячейка,
Остатки.МеркурийПартия КАК МеркурийПартия,
Остатки.ОстатокОстаток - Остатки.РезервОстаток КАК Остаток
ИЗ
РегистрНакопления.Остатки.Остатки(, Товар В (ВЫБРАТЬ ВТ_ФильтрТоваров.Товар ИЗ ВТ_ФильтрТоваров)) КАК Остатки
Обратите внимание на конструкцию Товар В (ВЫБРАТЬ ВТ_ФильтрТоваров.Товар ИЗ ВТ_ФильтрТоваров) внутри параметров виртуальной таблицы. Платформа 1С оптимизирует такое выражение на уровне SQL, заменяя его эффективным внутренним соединением (INNER JOIN) с временной таблицей на уровне базы данных. Поскольку временная таблица проиндексирована, СУБД выполняет операцию Merge Join или Hash Match, что кардинально ускоряет процесс выборки.
В ходе обсуждения в Сообщении 22 прозвучало предложение отказаться от виртуальной таблицы остатков и собирать остатки «вручную» из реальной таблицы движений регистра накопления через группировку и расчет максимального периода (MAX(Период)).
Внимание! Это грубейшая методическая ошибка для регистров накопления. Участник форума перепутал специфику работы регистра сведений и регистра накопления.
Выясним, как устроены эти объекты «под капотом» базы данных:
Остатки без указания даты (или на текущую дату), платформа 1С делает прямой и максимально быстрый запрос только к физической таблице итогов. Ей не нужно пересчитывать историю движений, данные уже свернуты и подготовлены.Поэтому для получения остатков всегда следует использовать только стандартные виртуальные таблицы системы 1С.
В сообщениях 17–19 автор темы столкнулся с пугающим эффектом: даже при отборе по пяти товарам СУБД накладывает блокировку на всю таблицу регистра остатков, хотя в технологическом журнале и SQL-профайлере четко зафиксирован оптимальный план выполнения Clustered Index Seek (поиск по индексу).
Проанализируем причины этой проблемы. Здесь работают два ключевых механизма СУБД и платформы 1С:
1. Автоматический режим блокировок базы данных.
Если конфигурация работает в режиме автоматических блокировок (или транзакция выполняется на СУБД в режиме изоляции Serializable / Repeatable Read), СУБД вынуждена защищать транзакцию от появления так называемых «фантомных строк». При чтении остатков СУБД накладывает S-блокировки (разделяемые блокировки) не только на конкретные считанные строки, но и на целые диапазоны ключей индекса (Key-Range Locks). Если ваши 5 товаров распределены по разным страницам индекса неравномерно, количество заблокированных диапазонов стремительно растет.
2. Эскалация блокировок (Lock Escalation) в MS SQL Server.
Любая блокировка в оперативной памяти сервера СУБД требует ресурсов. Чтобы предотвратить переполнение памяти мелкими блокировками строк и страниц, в MS SQL Server встроен механизм автоматического укрупнения (эскалации) блокировок.
Как только количество отдельных блокировок в рамках одной транзакции на одну таблицу превышает системный порог (по умолчанию — 5000 блокировок), или если объем оперативной памяти под блокировки превышает установленные лимиты, СУБД мгновенно сбрасывает все мелкие блокировки и накладывает одну мощную блокировку на всю таблицу (уровень TABLOCK). Другие пользователи в этот момент не могут записать ни одну строку в этот регистр и встают в очередь ожидания.
Для решения проблемы блокировок существуют два основных пути:
Способ 1. Переход на управляемый режим блокировок (Рекомендуемый)
Это стандартный и самый надежный инструмент современной платформы 1С. При переводе конфигурации (или конкретного регистра) в режим управляемых блокировок платформа переводит сеанс СУБД на уровень изоляции Read Committed (или его аналог Read Committed Snapshot (RCSI)).
При чтении данных СУБД больше вообще не накладывает блокировок чтения на таблицы базы данных! За параллельность процессов теперь отвечает собственный Менеджер управляемых блокировок 1С на сервере приложений. Перед запросом программист явно указывает в коде, какие именно записи (по каким товарам и складам) он планирует изменить или проанализировать:
Блокировка = Новый БлокировкаДанных;
ЭлементБлокировки = Блокировка.Добавить("РегистрНакопления.Остатки");
ЭлементБлокировки.Режим = РежимБлокировкиДанных.Исключительный;
ЭлементБлокировки.ИсточникДанных = ТаблицаТоваров;
ЭлементБлокировки.ИспользоватьИзИсточникаДанных("Товар", "Товар");
ЭлементБлокировки.ИспользоватьИзИсточникаДанных("Склад", "Склад");
Блокировка.Заблокировать();
После вызова метода Заблокировать() платформа заблокирует только указанные элементы. СУБД при этом будет работать абсолютно свободно, без риска эскалации блокировок до уровня таблицы.
Способ 2. Отключение эскалации на уровне СУБД (Временная мера)
Если ваша конфигурация слишком старая и перевод на управляемые блокировки требует масштабного переписывания кода, можно временно запретить СУБД выполнять эскалацию блокировок для конкретной физической таблицы регистра накопления. Для этого на сервере MS SQL Server администратор баз данных может выполнить команду:
ALTER TABLE _AccumRgT[ИмяТаблицыИтогов] SET (LOCK_ESCALATION = DISABLE);
Этот шаг заставит MS SQL Server удерживать блокировки на уровне страниц или строк и не блокировать всю таблицу итогов целиком, снижая количество взаимоблокировок (Deadlocks).
Обратим внимание на финальный фильтр нашего запроса: ГДЕ ОстаткиОстатки.ОстатокОстаток - ОстаткиОстатки.РезервОстаток > 0.
Важно понимать, что СУБД не может применить индексы к вычисляемому полю (разности ресурсов). Этот фильтр накладывается уже после того, как выборка остатков сформирована в оперативной памяти СУБД.
Чтобы минимизировать негативное влияние этого вычисления на производительность, мы обязаны максимально сузить объем данных «на входе» в виртуальную таблицу. Передавая предварительно отфильтрованные списки товаров и складов через проиндексированные временные таблицы в параметры виртуальной таблицы, мы гарантируем, что вычисление Остаток - Резерв > 0 будет выполняться только для единичных строк, а не для сотен тысяч записей регистра. Это делает работу запроса практически мгновенной.