UPD. Коллеги в комментариях заметили ошибку, она была исправлена и сейчас выложены данные с корректным решением.
Учитывая, сколько нового я узнала из комментариев к предыдущей моей публикации, не могу не продолжить свой цикл статей по оптимизации. Критика и комментарии приветствуются.
На этот раз посмотрим оптимизацию запроса. Итак, имеется ЗУП версии 2.5.319.1 СУБД — MS SQL Платформа версии 8.3.14.1565 режим совместимости 8.2.13
От пользователей поступили жалобы на долгое формирование печатной формы Т13 из документа "Табель учета рабочего времени". Проверяем работу на тестовой базе под полными правами.
Включаем замер производительности, и в топ вылезает выполнение запроса.
Аж 34 секунды! Этот запрос растянулся больше, чем на 1000 строк, и понять без профайлера, где проблема, невозможно.
Данные, собранные подсистемой оценки производительности:
Как видно, apdex болтается где-то на дне. (Какая милая опечатка "дукумент"!) А среднее время выполнения операции на рабочей базе 25 секунд.
Итак, для настройки профайлера узнаем ID базы:
Use [database]
Go
Select DB_ID()
Настроим профайлер, выбрав необходимые события и установив отборы по длительности и ID базы:
На этот раз запрос выполнился быстрее, за 9 секунд. Находим самый тяжелый запрос:
Находим его в базе 1с:
ВЫБРАТЬ РАЗРЕШЕННЫЕ
ГрафикиРаботыПоВидамВремени.ГрафикРаботы КАК ГрафикРаботы,
ГрафикиРаботыПоВидамВремени.Дата КАК Дата,
НАЧАЛОПЕРИОДА(ГрафикиРаботыПоВидамВремени.Дата, МЕСЯЦ) КАК ПериодРегистрации,
ГрафикиРаботыПоВидамВремени.ОсновноеЗначение,
ГрафикиРаботыПоВидамВремени.ВидУчетаВремени КАК ВидУчетаВремени,
ГрафикиРаботыПоВидамВремени.Документ,
ГрафикиРаботыПоВидамВремени.План КАК План
ПОМЕСТИТЬ ВТГрафикиРаботыПоВидамВремени
ИЗ
РегистрСведений.ГрафикиРаботыПоВидамВремени КАК ГрафикиРаботыПоВидамВремени
ГДЕ
ГрафикиРаботыПоВидамВремени.ГрафикРаботы В
(ВЫБРАТЬ
Сотрудники.Сотрудник
ИЗ
ВТСотрудники КАК Сотрудники
ОБЪЕДИНИТЬ ВСЕ
ВЫБРАТЬ
ГрафикиРаботников.ГрафикРаботы
ИЗ
ВТГрафикиРаботников КАК ГрафикиРаботников)
И ГрафикиРаботыПоВидамВремени.Дата МЕЖДУ &НачалоПериода И &КонецПериода
И ГрафикиРаботыПоВидамВремени.ВидУчетаВремени <> ЗНАЧЕНИЕ(Перечисление.ВидыУчетаВремени.ПоДням)
Посмотрим структуру регистра сведений. Непериодический, независимый со следующими измерениями:
Казалось бы, как оптимизировать этот запрос? Он написан согласно рекомендациям от 1С, но индекс почему-то не используется, хотя стоят условия по первому измерению. Но ради эксперимента, давайте перепишем запрос, используя "ИЛИ":
ВЫБРАТЬ РАЗРЕШЕННЫЕ
ГрафикиРаботыПоВидамВремени.ГрафикРаботы КАК ГрафикРаботы,
ГрафикиРаботыПоВидамВремени.Дата КАК Дата,
НАЧАЛОПЕРИОДА(ГрафикиРаботыПоВидамВремени.Дата, МЕСЯЦ) КАК ПериодРегистрации,
ГрафикиРаботыПоВидамВремени.ОсновноеЗначение,
ГрафикиРаботыПоВидамВремени.ВидУчетаВремени КАК ВидУчетаВремени,
ГрафикиРаботыПоВидамВремени.Документ,
ГрафикиРаботыПоВидамВремени.План КАК План
ПОМЕСТИТЬ ВТГрафикиРаботыПоВидамВремени
ИЗ
РегистрСведений.ГрафикиРаботыПоВидамВремени КАК ГрафикиРаботыПоВидамВремени
ГДЕ
ГрафикиРаботыПоВидамВремени.ГрафикРаботы В
((ВЫБРАТЬ
Сотрудники.Сотрудник
ИЗ
ВТСотрудники КАК Сотрудники) или
ГрафикиРаботыПоВидамВремени.ГрафикРаботы В(ВЫБРАТЬ
ГрафикиРаботников.ГрафикРаботы
ИЗ
ВТГрафикиРаботников КАК ГрафикиРаботников))
И ГрафикиРаботыПоВидамВремени.Дата МЕЖДУ &НачалоПериода И &КонецПериода
И ГрафикиРаботыПоВидамВремени.ВидУчетаВремени <> ЗНАЧЕНИЕ(Перечисление.ВидыУчетаВремени.ПоДням)
И что же мы видим?
Выполнение запроса меньше чем за секунду.
Текст запроса на языке SQL:
Индекс используется:
После обновления в рабочей базы:
Аpdex по этой операции вырос до 0,914. Среднее время выполнения операции — 3 секунды.
Послесловие
На самом деле это не первый запрос в моей практике, где "ИЛИ" работает лучше чем "ОБЪЕДИНИТЬ ВСЕ". Почему оптимизатор не использовал индекс, ведь условие подходит? У меня есть предположение, что это зависит от версии СУБД. Но это только предположение, если у кого есть идеи лучше, пишите в комментариях.
Related Posts
Восстановление последовательности документов при закрытии месяца в Бухгалтерия 3.0 не завершается
Заполнение табличных частей
Формирование сводных актов выполненных работ
Ввод поступления в переработку на основании передачи сырья (между организациями)- Конспект по установке сервера 1С на linux
Получение имени компьютера и его IP локально и в терминале
Такое условие выглядит странно:
(1) вообще-то, нормально, так обозначаются индивидуальные графики работы сотрудника, в поле «График работы» в данном случае будет ссылка на сотрудника, у кого используется такой график.
В рекомендациях от 1С вроде сравниваются два варианта
1)
2)
Показать
И вариант 2) вроде как обычно быстрее чем 1)
В данной статье ОБЪЕДИНИТЬ ВСЕ находится не в основном запросе, а в запросе в условии.
И не совсем корректно ссылаться на рекомендации в данном конкретном случае.
(2) никогда не работал с этим регистром, хотя это типовой УППшный регистр (посмотрел ради интереса).
Со стороны кажется нелогично.
Но согласен, что запрос в данном случае корректный
(0) (3) меня больше смущает другое, это приоритет булевых операций, по-моему автор забыл добавить скобку, потому и такой выигрыш скорости. в первом варианте было
а во втором
судя структуре регистра, чтобы индекс был покрывающим нужно добавить еще условие по полю «план» и «месяц», также индекс не используется, если используется «НЕ» в условиях запроса.
(5) да, верное замечание.
Первый запрос в статье не эквивалентен второму по результату.
Не хватает пары скобок, группирующих условия по полю «ГрафикРаботы»
(6) это же «гениальная» рекомендация, «если хотите чтобы работало быстрее, то переписывайте запрос на неправильный»
(5) Думаю автору помогло бы избежать этой ошибки оформление условия в виде . В конструкторе запроса ИР эта опция называется «Иерархич. логика».
Оригинальное условие выглядело бы так
Показать
Некорректно изменное условие выглядело бы так
Показать
А правильно изменное условие бы выглядело так
Показать
После исправления логики условий прирост остался?
Каков тип измерения «ГрафикРаботы»?
(5) Да, действительно, спасибо, вы правы, на тестовой базе, когда я воспроизводила эту оптимизацию для статьи, я действительно допустила ошибку. В рабочей как раз все было хорошо.
Чуть позже я выложу замеры и трассировку с исправленной ошибкой. Но да, и в таком варианте выигрыш в скорости на порядок.
(9) Да, остался. Я чуть позже выложу трассировку и замер.
(10) Составной. Справочники Сотрудники, Физические Лица, Графики работы.
А зачем было «ОБЪЕДИНИТЬ ВСЕ» , а не «ОБЪЕДИНИТЬ»
и что будет если условие «ГрафикиРаботыПоВидамВремени.ГрафикРаботы В» заменить на внутреннее соединение (Подзапрос с «Объединить»)
(13)
Это вопрос скорее к разработчикам типового решения.
А идею с «ОБЪЕДИНИТЬ» вместо «ОБЪЕДИНИТЬ ВСЕ» попробую. Мне кажется, что ничего не изменится, но я попробую.
(5)
Если бы индекс был покрывающим, это было бы совсем идеально, но не всегда реализуемо. А в данном случае можно сделать так, чтобы индекс использовался хотя бы частично — по первому измерению, остальное, да, пришлось сканировать, но все равно выигрыш в скорости получился на порядок.
(9) Выложила исправленное решение.
(5) Спасибо большое за замечание, ошибка исправлена, новые результаты замера производительности и трассировки в статье.
(0) Крут тот, кто понимает что ему надо и как оно работает.
Все зависит от задачи и структуры данных….
(13) Проверила — выполняется так же долго.
(3) Согласна. Исправила.
У меня почему-то в голове осталось, что «ОБЪЕДИНИТЬ ВСЕ» всегда лучше чем «ИЛИ»
Кстати, если переписать запрос вот так:
Показать
То тоже работает быстро. Так что не все «ОБЪЕДИНИТЬ ВСЕ» одинаково полезны.
(19) Боюсь, что «Выразить» в данном случае не поможет. У этот реквизит может быть как типом значения «сотрудник», так и «график».
Показать
Я бы для начала вынес подзапрос из условия во временную таблицу.
Здравствуйте, Анна !
Как правило, вместо проверки включения лучше использовать внутреннее соединение.
Я попробовала. Результат 34 секунды. То же самое + индексирование в запросе по полю сотрудник выигрыша в скорости так же не дало.
(25) Добрый вечер!
Спасибо, это хороший совет. Я проверила — результат выполнения 2 секунды запроса, индекс используется.
Посмотрела план запроса, он очень похож на тот который получился при использовании ИЛИ.