Одна из причин медленной работы табеля (ЗУП 2.5, клиент-сервер, MS SQL Server)

Началось все с того, что я решил проанализировать рабочий MS SQL-сервер на предмет избыточных и недостающих индексов в базах 1С (навеяно статьей "Для чего НЕ нужны индексы").

Для анализа использовал запросы-шаблоны из этой статьи SQL Server: Базы данных и индексы.

"Прогнал" на одной из самых больших баз ЗУП запрос "Missing Indexes in current database by Index Advantage":

Получилась вот такая интересная картинка:

В первых трех строках фигурирует одна и та же таблица, но index_advantage (выигрыш от создания индекса) для первой строки на два порядка больше двух последующих. Сосредоточимся именно на первой строке, с самой большой стоимостью.

Судя по last_user_seek, проблема актуальна - запрос к данной таблице выполнялся всего несколько минут назад (анализ индексов выполнялся в 12:00 18.01.2016). А судя по значениям unique_compiles и user_seeks, отбор по колонкам из equality_columns данной таблицы выполняется довольно часто.

Попробуем выяснить, что это за регистр сведений и "кто" его так интенсивно и неэффективно использует. Найдем этот регистр в описании структуры базы данных, используя одну из предназначенных для этих целей обработок:

Выяснилось, что это регистр "Графики работы по видам времени". Поля_Fld2990, _Fld6561 и _Fld2988Rref, по которым требуется создать недостающий индекс (equality_columns), соответствуют измерениям регистра Месяц, План и ВидУчетаВремени. Проверим, существует ли индекс, удовлетворяющий такому набору полей:

В существующем индексе по измерениям регистра ByDims, все необходимые для требуемого индекса поля присутствуют, но проблема в порядке полей - они идут после поля ГрафикРаботы, которое в данном случае по каким-то причинам не используется, и данный индекс задействован быть не может. Остальные индексы также не удовлетворяют условиям.

Штатными средствами 1С создать требуемый индекс, не меняя порядок измерений в регистре, к сожалению, не удастся. Да, и прежде, чем что-то делать с индексами, лучше сначала проанализировать источник проблемы, запрос 1С, не использующий имеющиеся в СУБД индексы. 

Необходимо найти в конфигурации ЗУП запрос, выполняющий отбор по колонкам Месяц + План + ВидУчетаВремени регистра "Графики работы по видам времени" без отбора по полю ГрафикРаботы. Для этого я использовал глобальный поиск по конфигурации (искал по тексту "РегистрСведений.ГрафикиРаботыПоВидамВремени"). Для сужения области поиска учитывал информацию из колонки included_columns, в которой перечисляются получаемые в искомом запросе поля. 

Наиболее вероятный кандидат на проблемный запрос был обнаружен в модуле формы документа "Табель учета рабочего времени", в функции ПолучитьНормуВремениПоДню, вызываемой при вводе в ячейку табеля:

В нем есть все искомые поля и отборы. Но есть проблема - в запросе явно используется отбор по полю ГрафикРаботы, которого по условиям нашего поиска быть как раз не должно. Очевидно, что здесь должен быть задействован уже существующий индекс по всем измерениям. Зачем тут нужен какой-то другой индекс и почему все-таки был выбран именно этот запрос?

Я остановил свое внимание на этом запросе только потому, что отбор по ГрафикРаботы здесь организован через ГДЕ ... В (ВЫБРАТЬ ... ИЗ ...). Ранее я уже неоднократно сталкивался с тем, что при таком написании условий отбора оптимизатор SQL срабатывает некорректно и не использует уже имеющиеся индексы (см. Ускоряем заполнение документа "Формирование записей книги покупок"). К тому же, именно на табель, а, точнее, на его интерактивную часть, указывали некоторые косвенные признаки, специфические для организации, в которой ведется учет.

Замер времени выполнения данного запроса показал длительность ~1,5 сек., что очень существенно для функции, срабатывающей при вводе в ячейку табеля. Это косвенно подтверждает, что найден тот самый проблемный запрос.

Надо отметить, что при работе с табелем так долго выполняется запрос только при первом изменении одной из ячеек строки по сотруднику. При очередных изменениях, при "горизонтальном" редактировании строки, задействуется кэширование. Но проблема в том, что основной сценарий работы с табелем, это как раз "вертикальное" заполнение ячеек за один день по разным сотрудникам. Таким образом задержки будут возникать практически при каждом изменении.

Итак, проверим мое предположение о некорректной отработке условия "ГДЕ ... В ()". Перепишем запрос на более предсказуемое внутреннее соединение:

Замер оптимизированной версии запроса показал ~0.08 секунд, то есть время выполнения уменьшилось почти в 20 раз.

В идеале, конечно, для подтверждения моих догадок, надо было заглянуть в исходный и результирующих план запроса через Profiler, но, уж простите, поленился. Данная "особенность" отработки "ГДЕ ... В (...)" на MS SQL уже набила оскомину и я был на 99,99% уверен, что увидел бы там какой-нибудь медленный Index Scan, который после оптимизации превратился бы в быстрый Index Seek. Я просто подождал один день после исправления проблемного запроса и снова выполнил анализ индексов - проблема, как я и ожидал, стала неактуальной.

В результате данной оптимизации работа с табелем стала гораздо комфортнее. Какой будет эффект на Вашей базе и будет ли он вообще, сказать сложно. Но если этот участок учета критичен в Вашей организации и по нему есть нарекания от пользователей по части производительности, то попробовать применить данную практику все-таки стоит.

ЗУП 2.5.97.1 (8.2.19.83), SQL Server 2005 (9.00.4053.00, 64-bit)

См. также:

Оптимизированная версия Т-13