При сопровождении конфигураций, использующих Библиотеку стандартных подсистем (БСП) версии 2.1, разработчики нередко сталкиваются с ситуацией, когда отчет Права доступа внезапно перестает формироваться и намертво зависает. При этом стандартные регламентные процедуры СУБД (реиндексация, обновление статистик и очистка кэша планов выполнения) не дают никакого эффекта. Запрос в процедуре ВидыОграниченийПравОбъектовМетаданных может выполняться часами (более 14 000 секунд), потребляя ресурсы сервера даже при относительно небольшом объеме регистра сведений НаборыЗначенийДоступа порядка 100 000–150 000 записей.
Разберем по шагам, почему возникает эта проблема на уровне платформы «1С:Предприятие» и MS SQL Server, а также рассмотрим практические способы переписывания запроса, сокращающие время его работы до одной секунды.
Проанализируем структуру проблемного запроса из БСП 2.1. Запрос формирует таблицу ПеременныеВидыОграничений, выполняя четыре секции с оператором ОБЪЕДИНИТЬ ВСЕ (по одной на каждое право: Чтение, Добавление, Изменение, Удаление). В каждой секции происходит соединение таблицы регистра сведений самой с собой через подзапрос:
ВНУТРЕННЕЕ СОЕДИНЕНИЕ (ВЫБРАТЬ
Наборы.Объект КАК Объект,
Наборы.НомерНабора КАК НомерНабора
ИЗ
РегистрСведений.НаборыЗначенийДоступа КАК Наборы
ГДЕ
Наборы.Чтение) КАК НомераНаборовПрава
ПО НаборыЗначенийДоступа.Объект = НомераНаборовПрава.Объект
И НаборыЗначенийДоступа.НомерНабора = НомераНаборовПрава.НомерНабора
Критическое падение быстродействия здесь обусловлено совокупностью следующих факторов:
Объект регистра НаборыЗначенийДоступа имеет составной тип. В таблицах MS SQL Server такое поле хранится в трех раздельных колонках: код типа данных (_TYPE), номер таблицы метаданных (_RTRef) и идентификатор ссылки (_RRRef). Конструкция языка запросов ТИПЗНАЧЕНИЯ(НаборыЗначенийДоступа.Объект) = ТИПЗНАЧЕНИЯ(...) транслируется лишь в сравнение колонок типа и номера таблицы. Из-за отсутствия точного значения ссылки (_RRRef) СУБД физически не может применить точечный поиск по кластерному индексу (Index Seek) и переходит к полному сканированию индекса или таблицы (Index Scan).Nested Loops) или слияние (Merge Join) со сбросом промежуточных данных во временные таблицы TempDB (Table Spool). В результате число логических чтений вырастает до сотен миллионов.Выясним, зачем изначально разработчики БСП использовали соединение с подзапросом по номерам наборов. Логика наборов доступа предполагает, что право (например, на чтение) предоставляется номеру набора целиком. Если в наборе хотя бы у одной строки флаг права взведен, то все значения доступа этого набора участвуют в формировании правил.
Однако для целей данного отчета достаточно проверить сам факт: существуют ли для данного метаописания (типа объекта) наборы с установленным правом. Если мы заменим тяжелое соединение регистра самого с собой на условие отбора через коррелированный подзапрос 1 В (ВЫБРАТЬ ПЕРВЫЕ 1 ...), платформа транслирует его в SQL Server как полусоединение EXISTS. При нахождении первого же совпадения сканирование для текущей строки прерывается.
Рассмотрим оптимизированный текст запроса:
ВЫБРАТЬ
ПостоянныеВидыОграничений.Таблица,
ПостоянныеВидыОграничений.Право,
ПостоянныеВидыОграничений.ВидДоступа,
ПостоянныеВидыОграничений.ТаблицаОбъекта
ПОМЕСТИТЬ ПостоянныеВидыОграничений
ИЗ
&ПостоянныеВидыОграничений КАК ПостоянныеВидыОграничений
;
////////////////////////////////////////////////////////////////////////////////
ВЫБРАТЬ РАЗЛИЧНЫЕ
ПостоянныеВидыОграничений.Таблица,
"Чтение" КАК Право,
НаборыЗначенийДоступа.ВидДоступа
ПОМЕСТИТЬ ПеременныеВидыОграничений
ИЗ
РегистрСведений.НаборыЗначенийДоступа КАК НаборыЗначенийДоступа
ВНУТРЕННЕЕ СОЕДИНЕНИЕ Справочник.ИдентификаторыОбъектовМетаданных КАК Идентификаторы
ВНУТРЕННЕЕ СОЕДИНЕНИЕ ПостоянныеВидыОграничений КАК ПостоянныеВидыОграничений
ПО Идентификаторы.Ссылка = ПостоянныеВидыОграничений.ТаблицаОбъекта
И (ПостоянныеВидыОграничений.Право = "Чтение")
И (ПостоянныеВидыОграничений.ВидДоступа = ЗНАЧЕНИЕ(ПланВидовХарактеристик.ВидыДоступа.ПустаяСсылка))
ПО (ТИПЗНАЧЕНИЯ(НаборыЗначенийДоступа.Объект) = ТИПЗНАЧЕНИЯ(Идентификаторы.ЗначениеПустойСсылки))
ГДЕ
1 В
(ВЫБРАТЬ ПЕРВЫЕ 1
1
ИЗ
РегистрСведений.НаборыЗначенийДоступа КАК Наборы
ГДЕ
ТИПЗНАЧЕНИЯ(Наборы.Объект) = ТИПЗНАЧЕНИЯ(Идентификаторы.ЗначениеПустойСсылки)
И Наборы.Чтение)
ОБЪЕДИНИТЬ ВСЕ
ВЫБРАТЬ
ПостоянныеВидыОграничений.Таблица,
"Добавление",
НаборыЗначенийДоступа.ВидДоступа
ИЗ
РегистрСведений.НаборыЗначенийДоступа КАК НаборыЗначенийДоступа
ВНУТРЕННЕЕ СОЕДИНЕНИЕ Справочник.ИдентификаторыОбъектовМетаданных КАК Идентификаторы
ВНУТРЕННЕЕ СОЕДИНЕНИЕ ПостоянныеВидыОграничений КАК ПостоянныеВидыОграничений
ПО Идентификаторы.Ссылка = ПостоянныеВидыОграничений.ТаблицаОбъекта
И (ПостоянныеВидыОграничений.Право = "Добавление")
И (ПостоянныеВидыОграничений.ВидДоступа = ЗНАЧЕНИЕ(ПланВидовХарактеристик.ВидыДоступа.ПустаяСсылка))
ПО (ТИПЗНАЧЕНИЯ(НаборыЗначенийДоступа.Объект) = ТИПЗНАЧЕНИЯ(Идентификаторы.ЗначениеПустойСсылки))
ГДЕ
1 В
(ВЫБРАТЬ ПЕРВЫЕ 1
1
ИЗ
РегистрСведений.НаборыЗначенийДоступа КАК Наборы
ГДЕ
ТИПЗНАЧЕНИЯ(Наборы.Объект) = ТИПЗНАЧЕНИЯ(Идентификаторы.ЗначениеПустойСсылки)
И Наборы.Добавление)
ОБЪЕДИНИТЬ ВСЕ
ВЫБРАТЬ
ПостоянныеВидыОграничений.Таблица,
"Изменение",
НаборыЗначенийДоступа.ВидДоступа
ИЗ
РегистрСведений.НаборыЗначенийДоступа КАК НаборыЗначенийДоступа
ВНУТРЕННЕЕ СОЕДИНЕНИЕ Справочник.ИдентификаторыОбъектовМетаданных КАК Идентификаторы
ВНУТРЕННЕЕ СОЕДИНЕНИЕ ПостоянныеВидыОграничений КАК ПостоянныеВидыОграничений
ПО Идентификаторы.Ссылка = ПостоянныеВидыОграничений.ТаблицаОбъекта
И (ПостоянныеВидыОграничений.Право = "Изменение")
И (ПостоянныеВидыОграничений.ВидДоступа = ЗНАЧЕНИЕ(ПланВидовХарактеристик.ВидыДоступа.ПустаяСсылка))
ПО (ТИПЗНАЧЕНИЯ(НаборыЗначенийДоступа.Объект) = ТИПЗНАЧЕНИЯ(Идентификаторы.ЗначениеПустойСсылки))
ГДЕ
1 В
(ВЫБРАТЬ ПЕРВЫЕ 1
1
ИЗ
РегистрСведений.НаборыЗначенийДоступа КАК Наборы
ГДЕ
ТИПЗНАЧЕНИЯ(Наборы.Объект) = ТИПЗНАЧЕНИЯ(Идентификаторы.ЗначениеПустойСсылки)
И Наборы.Изменение)
ОБЪЕДИНИТЬ ВСЕ
ВЫБРАТЬ
ПостоянныеВидыОграничений.Таблица,
"Удаление",
НаборыЗначенийДоступа.ВидДоступа
ИЗ
РегистрСведений.НаборыЗначенийДоступа КАК НаборыЗначенийДоступа
ВНУТРЕННЕЕ СОЕДИНЕНИЕ Справочник.ИдентификаторыОбъектовМетаданных КАК Идентификаторы
ВНУТРЕННЕЕ СОЕДИНЕНИЕ ПостоянныеВидыОграничений КАК ПостоянныеВидыОграничений
ПО Идентификаторы.Ссылка = ПостоянныеВидыОграничений.ТаблицаОбъекта
И (ПостоянныеВидыОграничений.Право = "Удаление")
И (ПостоянныеВидыОграничений.ВидДоступа = ЗНАЧЕНИЕ(ПланВидовХарактеристик.ВидыДоступа.ПустаяСсылка))
ПО (ТИПЗНАЧЕНИЯ(НаборыЗначенийДоступа.Объект) = ТИПЗНАЧЕНИЯ(Идентификаторы.ЗначениеПустойСсылки))
ГДЕ
1 В
(ВЫБРАТЬ ПЕРВЫЕ 1
1
ИЗ
РегистрСведений.НаборыЗначенийДоступа КАК Наборы
ГДЕ
ТИПЗНАЧЕНИЯ(Наборы.Объект) = ТИПЗНАЧЕНИЯ(Идентификаторы.ЗначениеПустойСсылки)
И Наборы.Удаление)
;
////////////////////////////////////////////////////////////////////////////////
ВЫБРАТЬ
ПостоянныеВидыОграничений.Таблица,
ПостоянныеВидыОграничений.Право,
ПостоянныеВидыОграничений.ВидДоступа
ИЗ
ПостоянныеВидыОграничений КАК ПостоянныеВидыОграничений
ГДЕ
ПостоянныеВидыОграничений.ВидДоступа <> ЗНАЧЕНИЕ(ПланВидовХарактеристик.ВидыДоступа.ПустаяСсылка)
ОБЪЕДИНИТЬ
ВЫБРАТЬ
ПеременныеВидыОграничений.Таблица,
ПеременныеВидыОграничений.Право,
ПеременныеВидыОграничений.ВидДоступа
ИЗ
ПеременныеВидыОграничений КАК ПеременныеВидыОграничений
ГДЕ
ПеременныеВидыОграничений.ВидДоступа <> ЗНАЧЕНИЕ(ПланВидовХарактеристик.ВидыДоступа.ПустаяСсылка)
И ПеременныеВидыОграничений.ВидДоступа <> ЗНАЧЕНИЕ(ПланВидовХарактеристик.ВидыДоступа.ПравоЧтения)
И ПеременныеВидыОграничений.ВидДоступа <> ЗНАЧЕНИЕ(ПланВидовХарактеристик.ВидыДоступа.ПравоДобавления)
И ПеременныеВидыОграничений.ВидДоступа <> ЗНАЧЕНИЕ(ПланВидовХарактеристик.ВидыДоступа.ПравоИзменения)
Такая модификация дает идентичный результат при формировании отчета, устраняя зависание и сокращая время исполнения запроса с 4 часов до 1 секунды.
Если внесение изменений в код конфигурации ограничено или требует согласования, проанализируем настройки СУБД и сервисные механизмы платформы, которые снижают остроту проблемы:
ALTER DATABASE SCOPED CONFIGURATION SET LEGACY_CARDINALITY_ESTIMATION = ON;
НаборыЗначенийДоступа могут появляться «битые» ссылки в измерении Объект. Это искажает статистики СУБД. Следует запустить информационную базу в режиме предприятия с параметром /C ЗапуститьОбновлениеИнформационнойБазы для актуализации данных справочника ИдентификаторыОбъектовМетаданных и регламентных заданий подсистемы управления доступом.ВидДоступа было исключено из регистра НаборыЗначенийДоступа, а расчет прав переведен на предварительно подготовленные кэшированные параметры сеанса. В более свежих релизах отчет «Права доступа» уже не читает регистр целиком на лету.