gifts2017

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

Опубликовал Александр Карасев (KAPACEB.AA) в раздел Администрирование - Статистика базы данных

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

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

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

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

 Запрос анализа недостающих индексов SQL

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

Результат запроса


В первых трех строках фигурирует одна и та же таблица, но 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

См. также

Подписаться Добавить вознаграждение
Комментарии
1. Марина Чирина (chmv) 19.01.16 11:36
Спасибо. Табель на 100 человек проводится довольно долго, а если достигает 3000-4000 то очень долго. Сейчас быстрее. Насчет правильности пока не знаю
2. Александр Карасев (KAPACEB.AA) 19.01.16 12:10
(1) chmv,
Затронутая в статье проблема к проведению табеля отношения не имеет. Доработка потенциально устраняет только интерфейсные "тормоза" при работе с ячейками табеля.
chmv; echo77; +2 Ответить
3. roman Лявин (31337) 19.01.16 16:51
Спасибо, очень ясно описал и решение не сложное.
4. Пимшин 1 (Pim) 19.01.16 18:47
1. Такой вариант оптимизации работает только для MS SQL или в остальных случаях тоже помогает?
2. Не быстрее будет вместо внутреннего соединения получить массив сотрудников (отдельным запросом), а уже затем использовать этот массив в качестве параметра в секции ГДЕ (на файловых базах в подобных случаях помогало).
5. Александр Карасев (KAPACEB.AA) 19.01.16 21:00
(4) Pim,
1. В остальных случаях не проверял )
2. Возможно сработает предложенный вариант, не пробовал. По моему опыту, соединение даёт более предсказуемый результат.
6. rasswet (rasswet) 20.01.16 07:40
спасибо, очень подробно и ясно написано. Приятно читать!
7. Maxim Kolkin (the1) 20.01.16 14:04
(4) Pim, скорее всего прироста не будет, т.к. будет использоваться тот же оператор В(&МассивСотрудников)
8. Сергей Буланкин (bulas) 20.01.16 14:36
Время формирования Табеля уменьшилось (на количество строк по сотрудникам - 2300), так что прием действенный.
9. И Б (hornet_X) 22.01.16 08:29
(5) KAPACEB.AA, Оптимизация прошла успешно SQL Server 10.50.6220.0 ОС 6.1.7601. Спасибо за статью)
10. John Bolshakov (soulsteps) 22.01.16 10:17
Почему не вынесли подзапрос во временную таблицу с последующим индексированием поля "График работы", следующим же пакетом получали оставшиеся данные. Интересно, как бы изменилось время выполнения...
11. Александр Карасев (KAPACEB.AA) 22.01.16 10:21
(10) soulsteps,
Пробовал, на скорость не повлияло. В данном случае гораздо важнее задействовать индекс по физической таблице. Создание и индексирование временной таблицы, вероятно, добавит только дополнительные издержки. Хотя для надежности работы запроса, возможно, стоит сделать именно так, как Вы предложили.
12. Владимир Епифанов (kovgard) 15.02.16 18:08
Хорошая аналитическая работа. Проверил на файловой версии БД - реальное ускорение. Спасибо!
13. Александр Карасев (KAPACEB.AA) 16.02.16 10:09
(12) kovgard,
Спасибо за обратную связь.
Интересно, что на файловой сработало - честно говоря, были сомнения.
14. Марина Чирина (chmv) 22.06.16 11:05
А ПРИ ПРОВЕДЕНИИ КАК УСКОРИТЬ?
15. Александр Карасев (KAPACEB.AA) 22.06.16 16:08
(14) chmv,
До проведения пока руки не дошли.
Зато на днях удалось оптимизировать Т-13 (см. http://infostart.ru/public/532649/)