В процессе эксплуатации высоконагруженных систем на базе 1С:Предприятие, особенно на старых редакциях вроде УПП 1.3 и СУБД MS SQL Server 2005, разработчики часто сталкиваются с внезапным замедлением отчетов. Одной из наиболее коварных проблем является падение производительности при использовании отборов по характеристикам номенклатуры в Системе Компоновки Данных (СКД). Рассмотрим подробно, почему возникают такие ситуации, как интерпретировать данные SQL-профайлера и какими способами можно вернуть отчету былую скорость.
Рассмотрим ситуацию: отчет на СКД при отборе по конкретной ссылке на характеристику выполняется крайне медленно или не выполняется вовсе. При этом, если изменить условие на ВидПриемки.Код = "1", скорость возрастает в десятки раз. Проанализируем данные SQL Profiler. Мы можем увидеть парадоксальную картину: Estimated Subtree Cost (расчетная стоимость) для медленного запроса составляет, к примеру, 12 единиц, а для быстрого — 300 единиц. Однако реальная длительность (duration) показывает обратное: «дешевый» запрос работает в 100 раз дольше «дорогого».
Выясним причину такого расхождения. Это классическая ошибка Cardinality Estimation (оценки количества строк). Оптимизатор SQL Server 2005 предполагает, что запрос будет быстрым, основываясь на неверных данных о распределении значений. Когда реальный объем данных не соответствует ожиданиям, выбранный план (например, Index Scan вместо Index Seek) приводит к катастрофическим задержкам.
Первое, что необходимо сделать — проверить актуальность статистики. В MS SQL Server по умолчанию включено автоматическое обновление статистики, но оно срабатывает только при изменении 20% данных в таблице плюс 500 строк. Для таблиц с миллионами записей этот порог может не достигаться неделями, в то время как данные существенно меняются.
Проанализируем ситуацию с регламентным заданием. Если статистика обновляется стандартным скриптом, она может использовать SAMPLE (выборку), которая не дает точной картины. Для исправления ситуации выполним обновление с полным сканированием таблицы. Рассмотрим команду:
UPDATE STATISTICS [ИмяТаблицы] WITH FULLSCAN;
Это гарантирует, что оптимизатор получит 100% достоверную гистограмму распределения значений. После этого рекомендуется очистить кэш планов запросов, чтобы сервер принудительно перестроил их, используя свежие данные. Для этого используем команду:
DBCC FREEPROCCACHE
Разберем, почему отбор по характеристике может игнорировать индексы. В типовых конфигурациях характеристики часто хранятся в регистре сведений ЗначенияСвойствОбъектов. Несмотря на то, что измерение Свойство может быть ведущим, SQL Server может предпочесть сканирование всей таблицы, если в запросе отсутствует связь с основной сущностью — номенклатурой.
Посмотрим на эффективный прием: всегда добавляйте в отбор номенклатуру вместе с характеристикой. Это позволяет SQL Server использовать составные индексы и значительно сузить область поиска еще до начала обработки свойств. Если пользователь выбирает конкретную характеристику, программно или через настройки СКД добавьте условие по владельцу этой характеристики.
Если отчет сложный и СКД генерирует тяжелые вложенные запросы с разыменованием полей (через точку), проанализируем возможность изменения структуры самого запроса. СКД имеет свойство «раздувать» запросы, вставляя условия в каждое соединение, что часто путает оптимизатор SQL.
Рассмотрим метод разделения логики на пакеты. Выделим отбор номенклатуры в отдельную временную таблицу (ВТ), используя максимально легкий запрос. Это позволит зафиксировать набор данных для основного расчета.
Разберем пример кода для схемы компоновки данных:
ВЫБРАТЬ
Т.Ссылка КАК ТоварыОтбора
ПОМЕСТИТЬ ВтТоварыОтбора
ИЗ
Справочник.Номенклатура КАК Т
{ГДЕ
(Т.Ссылка В
(ВЫБРАТЬ
Влад.Объект
ИЗ
РегистрСведений.ЗначенияСвойствОбъектов КАК Влад
ГДЕ
Влад.Значение = &ВидПриемки))}
;
////////////////////////////////////////////////////////////////////////////////
ВЫБРАТЬ
Продажи.Номенклатура,
Продажи.КоличествоОборот
ИЗ
РегистрНакопления.Продажи.Обороты КАК Продажи
ГДЕ
Продажи.Номенклатура В (ВЫБРАТЬ Т.ТоварыОтбора ИЗ ВтТоварыОтбора КАК Т)
Важный нюанс: обратите внимание, что во временной таблице поле называется ТоварыОтбора, а не Номенклатура. Это сделано специально для того, чтобы механизм СКД не пытался автоматически наложить пользовательские отборы из настроек на это поле в последующих пакетах запроса, что могло бы снова привести к деградации производительности.
Выясним причину странного поведения, когда ВидПриемки.Код = "1" работает быстро. При обращении к свойству через точку 1С генерирует LEFT JOIN с таблицей справочника. Для SQL Server 2005 явное соединение таблиц часто является подсказкой, заставляющей его пересчитать стоимость операции. Вместо поиска неопределенного GUID в огромной таблице движимых данных, он выполняет поиск по первичному ключу справочника, что почти всегда приводит к Index Seek.
Однако полагаться на этот метод не стоит, так как он создает лишнее соединение. Лучше настроить правильное обслуживание индексов и использовать методику с временными таблицами, описанную выше.
Подводя итог, если ваш отчет в 1С внезапно начал тормозить при работе с характеристиками, выполните следующие шаги:
_Reference (справочники) и _InfoRg (регистры сведений) и обновите её с параметром FULLSCAN.Index Scan там, где ожидается Index Seek.Соблюдение этих правил позволит поддерживать высокую производительность системы даже на устаревшем стеке технологий (SQL 2005 / Платформа 8.2).