Кто круче: "ИЛИ" или "ОБЪЕДИНИТЬ ВСЕ" в запросе?

14.09.19

База данных - HighLoad оптимизация

Реальный пример оптимизации запроса. Статья будет полезна людям, изучающим вопросы оптимизации запросов в 1С и для подготовки к экзамену "1С: Эксперт по технологическим вопросам", поскольку в статье описывается один их методов расследования причин неоптимальных запросов. UPD. Коллеги в комментариях заметили ошибку, она была исправлена и сейчас выложены данные с корректным решением.

Скачать файлы

Наименование Файл Версия Размер
УнифицированнаяФормаТ13
.erf 68,08Kb
0
.erf 68,08Kb Скачать

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

На этот раз посмотрим оптимизацию запроса. Итак, имеется ЗУП версии 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 секунды.

Послесловие

На самом деле это не первый запрос в моей практике, где "ИЛИ" работает лучше чем "ОБЪЕДИНИТЬ ВСЕ".  Почему оптимизатор не использовал индекс, ведь условие подходит? У меня есть предположение, что это зависит от версии СУБД. Но это только предположение, если у кого есть идеи лучше, пишите в комментариях.

Оптимизация запрос профайлер СУБД MS SQL Profiler.

См. также

Оптимизация нагрузки на ЦП сервера СУБД используя типовые индексы

HighLoad оптимизация Платформа 1С v8.3 Конфигурации 1cv8 Бесплатно (free)

Анализ простого плана запроса. Оптимизация нагрузки на ЦП сервера СУБД используя типовые индексы.

13.03.2024    2955    spyke    26    

42

Быстродействие типовой 1С

HighLoad оптимизация Платформа 1С v8.3 Бесплатно (free)

Оказывается, в типовых конфигурациях 1С есть, что улучшить!

13.03.2024    5092    vasilev2015    19    

37

Анализируем SQL сервер глазами 1С-ника

HighLoad оптимизация Инструменты администратора БД Платформа 1С v8.3 Конфигурации 1cv8 Абонемент ($m)

Обработка для простого и удобного анализа настроек, нагрузки и проблем с SQL сервером с упором на использование оного для 1С. Анализ текущих зааросов на sql, ожиданий, конвертация запроса в 1с и рекомендации где может тормозить

1 стартмани

15.02.2024    7624    158    ZAOSTG    67    

96

Удаление строк из таблицы значений различными способами с замером производительности

HighLoad оптимизация Платформа 1С v8.3 Конфигурации 1cv8 Абонемент ($m)

Встал вопрос: как быстро удалить строки из ТЗ? Рассмотрел пять вариантов реализации этой задачи. Сравнил их друг с другом на разных объёмах данных с разным процентом удаляемых строк. Также сравнил с выгрузкой с отбором по структуре.

09.01.2024    5950    doom2good    48    

63

Опыт оптимизации 1С на PostgreSQL

HighLoad оптимизация Бесплатно (free)

При переводе типовой конфигурации 1C ERP/УТ/КА на PostgreSQL придется вложить ресурсы в доработку и оптимизацию запросов. Расскажем, на что обратить внимание при потерях производительности и какие инструменты/подходы помогут расследовать проблемы после перехода.

20.11.2023    8839    ivanov660    6    

76

ТОП проблем/задач у владельцев КОРП лицензий 1С на основе опыта РКЛ

HighLoad оптимизация Бесплатно (free)

Казалось бы, КОРП-системы должны быть устойчивы, быстры и надёжны. Но, работая в рамках РКЛ, мы видим немного другую картину. Об основных болевых точках КОРП-систем и подходах к их решению пойдет речь в статье.

15.11.2023    5093    a.doroshkevich    20    

72

Начните уже использовать хранилище запросов

HighLoad оптимизация Запросы

Очень немногие из тех, кто занимается поддержкой MS SQL, работают с хранилищем запросов. А ведь хранилище запросов – это очень удобный, мощный и, главное, бесплатный инструмент, позволяющий быстро найти и локализовать проблему производительности и потребления ресурсов запросами. В статье расскажем о том, как использовать хранилище запросов в MS SQL и какие плюсы и минусы у него есть.

11.10.2023    16162    skovpin_sa    14    

98
Комментарии
В избранное Подписаться на ответы Сортировка: Древо развёрнутое
Свернуть все
1. json 3297 14.09.19 23:39 Сейчас в теме
Такое условие выглядит странно:
ГДЕ
	ГрафикиРаботыПоВидамВремени.ГрафикРаботы В
	(ВЫБРАТЬ
		Сотрудники.Сотрудник
	ИЗ
		ВТСотрудники КАК Сотрудники) 
2. karpik666 3760 14.09.19 23:44 Сейчас в теме
(1) вообще-то, нормально, так обозначаются индивидуальные графики работы сотрудника, в поле "График работы" в данном случае будет ссылка на сотрудника, у кого используется такой график.
4. json 3297 15.09.19 00:15 Сейчас в теме
(2) никогда не работал с этим регистром, хотя это типовой УППшный регистр (посмотрел ради интереса).
Со стороны кажется нелогично.
Но согласен, что запрос в данном случае корректный
3. json 3297 15.09.19 00:08 Сейчас в теме
В рекомендациях от 1С вроде сравниваются два варианта
1)
ВЫБРАТЬ
	Поле
ИЗ Таблица
ГДЕ (&Условие1) ИЛИ (&Условие2)


2)
ВЫБРАТЬ
	Поле
ИЗ Таблица
ГДЕ (&Условие1)

ОБЪЕДИНИТЬ ВСЕ

ВЫБРАТЬ
	Поле
ИЗ Таблица
ГДЕ (&Условие2)
Показать


И вариант 2) вроде как обычно быстрее чем 1)

В данной статье ОБЪЕДИНИТЬ ВСЕ находится не в основном запросе, а в запросе в условии.
И не совсем корректно ссылаться на рекомендации в данном конкретном случае.
al5prog; azazana; +2 Ответить
5. karpik666 3760 15.09.19 00:19 Сейчас в теме
(0) (3) меня больше смущает другое, это приоритет булевых операций, по-моему автор забыл добавить скобку, потому и такой выигрыш скорости. в первом варианте было
ГДЕ A И B И C

а во втором
ГДЕ A ИЛИ B И С И D
судя структуре регистра, чтобы индекс был покрывающим нужно добавить еще условие по полю "план" и "месяц", также индекс не используется, если используется "НЕ" в условиях запроса.
mivari; azazana; NN2P; MVK80; +4 Ответить
6. json 3297 15.09.19 00:24 Сейчас в теме
(5) да, верное замечание.
Первый запрос в статье не эквивалентен второму по результату.
Не хватает пары скобок, группирующих условия по полю "ГрафикРаботы"
7. karpik666 3760 15.09.19 00:28 Сейчас в теме
(6) это же "гениальная" рекомендация, "если хотите чтобы работало быстрее, то переписывайте запрос на неправильный"
8. tormozit 7136 15.09.19 08:58 Сейчас в теме
(5) Думаю автору помогло бы избежать этой ошибки оформление условия в виде И-ИЛИ дерева . В конструкторе запроса ИР эта опция называется "Иерархич. логика".
Оригинальное условие выглядело бы так
ГДЕ ИСТИНА
	И ГрафикиРаботыПоВидамВремени.ГрафикРаботы В (
			ВЫБРАТЬ
				Сотрудники.Сотрудник КАК Сотрудник
			ИЗ
				ВТСотрудники КАК Сотрудники
			ОБЪЕДИНИТЬ ВСЕ
			ВЫБРАТЬ
				ГрафикиРаботников.ГрафикРаботы КАК Сотрудник
			ИЗ
				ВТГрафикиРаботников КАК ГрафикиРаботников)
	И ГрафикиРаботыПоВидамВремени.Дата МЕЖДУ &НачалоПериода И &КонецПериода
	И ГрафикиРаботыПоВидамВремени.ВидУчетаВремени <> ЗНАЧЕНИЕ(Перечисление.ВидыУчетаВремени.ПоДням)
Показать

Некорректно изменное условие выглядело бы так
ГДЕ ЛОЖЬ
	ИЛИ ГрафикиРаботыПоВидамВремени.ГрафикРаботы В (
			ВЫБРАТЬ
				Сотрудники.Сотрудник КАК Сотрудник
			ИЗ
				ВТСотрудники КАК Сотрудники)
	ИЛИ (ИСТИНА
		И ГрафикиРаботыПоВидамВремени.ГрафикРаботы В (
				ВЫБРАТЬ
					ГрафикиРаботников.ГрафикРаботы КАК ГрафикРаботы
				ИЗ
					ВТГрафикиРаботников КАК ГрафикиРаботников)
		И ГрафикиРаботыПоВидамВремени.Дата МЕЖДУ &НачалоПериода И &КонецПериода
		И ГрафикиРаботыПоВидамВремени.ВидУчетаВремени <> ЗНАЧЕНИЕ(Перечисление.ВидыУчетаВремени.ПоДням))
Показать

А правильно изменное условие бы выглядело так
ГДЕ ИСТИНА
	И (ЛОЖЬ
		ИЛИ ГрафикиРаботыПоВидамВремени.ГрафикРаботы В (
				ВЫБРАТЬ
					Сотрудники.Сотрудник КАК Сотрудник
				ИЗ
					ВТСотрудники КАК Сотрудники)
		ИЛИ ГрафикиРаботыПоВидамВремени.ГрафикРаботы В (
				ВЫБРАТЬ
					ГрафикиРаботников.ГрафикРаботы КАК ГрафикРаботы
				ИЗ
					ВТГрафикиРаботников КАК ГрафикиРаботников))
	И ГрафикиРаботыПоВидамВремени.Дата МЕЖДУ &НачалоПериода И &КонецПериода
	И ГрафикиРаботыПоВидамВремени.ВидУчетаВремени <> ЗНАЧЕНИЕ(Перечисление.ВидыУчетаВремени.ПоДням)
Показать
Прикрепленные файлы:
artkor; mivari; fancy; VasDmi666; karpik666; MVK80; json; +7 Ответить
11. azazana 71 16.09.19 11:45 Сейчас в теме
(5) Да, действительно, спасибо, вы правы, на тестовой базе, когда я воспроизводила эту оптимизацию для статьи, я действительно допустила ошибку. В рабочей как раз все было хорошо.
Чуть позже я выложу замеры и трассировку с исправленной ошибкой. Но да, и в таком варианте выигрыш в скорости на порядок.
16. azazana 71 16.09.19 12:18 Сейчас в теме
(5)
судя структуре регистра, чтобы индекс был покрывающим нужно добавить еще условие по полю "план" и "месяц", также индекс не используется, если используется "НЕ" в условиях запрос

Если бы индекс был покрывающим, это было бы совсем идеально, но не всегда реализуемо. А в данном случае можно сделать так, чтобы индекс использовался хотя бы частично - по первому измерению, остальное, да, пришлось сканировать, но все равно выигрыш в скорости получился на порядок.
18. azazana 71 16.09.19 12:56 Сейчас в теме
(5) Спасибо большое за замечание, ошибка исправлена, новые результаты замера производительности и трассировки в статье.
22. azazana 71 20.09.19 18:19 Сейчас в теме
(3) Согласна. Исправила.
У меня почему-то в голове осталось, что "ОБЪЕДИНИТЬ ВСЕ" всегда лучше чем "ИЛИ"
Кстати, если переписать запрос вот так:
ВЫБРАТЬ РАЗРЕШЕННЫЕ
   ГрафикиРаботыПоВидамВремени.ГрафикРаботы КАК ГрафикРаботы,
   ГрафикиРаботыПоВидамВремени.Дата КАК Дата,
   НАЧАЛОПЕРИОДА(ГрафикиРаботыПоВидамВремени.Дата, МЕСЯЦ) КАК ПериодРегистрации,
   ГрафикиРаботыПоВидамВремени.ОсновноеЗначение,
   ГрафикиРаботыПоВидамВремени.ВидУчетаВремени КАК ВидУчетаВремени,
   ГрафикиРаботыПоВидамВремени.Документ,
   ГрафикиРаботыПоВидамВремени.План КАК План

ПОМЕСТИТЬ ВТГрафикиРаботыПоВидамВремени

ИЗ
      РегистрСведений.ГрафикиРаботыПоВидамВремени КАК ГрафикиРаботыПоВидамВремени

ГДЕ
   ГрафикиРаботыПоВидамВремени.ГрафикРаботы В
      (ВЫБРАТЬ
      Сотрудники.Сотрудник
           ИЗ
                ВТСотрудники КАК Сотрудники)
   И ГрафикиРаботыПоВидамВремени.Дата МЕЖДУ &НачалоПериода И &КонецПериода
   И ГрафикиРаботыПоВидамВремени.ВидУчетаВремени <> ЗНАЧЕНИЕ(Перечисление.ВидыУчетаВремени.ПоДням)

ОБЪЕДИНИТЬ ВСЕ

ВЫБРАТЬ
   ГрафикиРаботыПоВидамВремени.ГрафикРаботы,
   ГрафикиРаботыПоВидамВремени.Дата,
   НАЧАЛОПЕРИОДА(ГрафикиРаботыПоВидамВремени.Дата, МЕСЯЦ),
   ГрафикиРаботыПоВидамВремени.ОсновноеЗначение,
   ГрафикиРаботыПоВидамВремени.ВидУчетаВремени,
   ГрафикиРаботыПоВидамВремени.Документ,
   ГрафикиРаботыПоВидамВремени.План

ИЗ

   РегистрСведений.ГрафикиРаботыПоВидамВремени КАК ГрафикиРаботыПоВидамВремени

ГДЕ
   ГрафикиРаботыПоВидамВремени.ГрафикРаботы В
      (ВЫБРАТЬ
           ГрафикиРаботников.ГрафикРаботы
       ИЗ
           ВТГрафикиРаботников КАК ГрафикиРаботников)
    И ГрафикиРаботыПоВидамВремени.Дата МЕЖДУ &НачалоПериода И &КонецПериода
    И ГрафикиРаботыПоВидамВремени.ВидУчетаВремени <> ЗНАЧЕНИЕ(Перечисление.ВидыУчетаВремени.ПоДням)
Показать


То тоже работает быстро. Так что не все "ОБЪЕДИНИТЬ ВСЕ" одинаково полезны.
9. gzharkoj 502 15.09.19 17:31 Сейчас в теме
После исправления логики условий прирост остался?
12. azazana 71 16.09.19 11:46 Сейчас в теме
(9) Да, остался. Я чуть позже выложу трассировку и замер.
17. azazana 71 16.09.19 12:55 Сейчас в теме
(9) Выложила исправленное решение.
10. Famza 84 16.09.19 11:05 Сейчас в теме
Каков тип измерения "ГрафикРаботы"?
14. azazana 71 16.09.19 12:09 Сейчас в теме
(10) Составной. Справочники Сотрудники, Физические Лица, Графики работы.
19. Famza 84 16.09.19 18:31 Сейчас в теме
(14) В рекомендациях 1С в таких случаях требуется использовать "ВЫРАЗИТЬ". С данной конструкцией измерялся прирост скорости?
23. azazana 71 20.09.19 18:27 Сейчас в теме
(19) Боюсь, что "Выразить" в данном случае не поможет. У этот реквизит может быть как типом значения "сотрудник", так и "график".
ВЫБРАТЬ
                Сотрудники.Сотрудник КАК Сотрудник
            ИЗ
                ВТСотрудники КАК Сотрудники
            ОБЪЕДИНИТЬ ВСЕ
            ВЫБРАТЬ
                ГрафикиРаботников.ГрафикРаботы
            ИЗ
                ВТГрафикиРаботников КАК ГрафикиРаботников
Показать
13. kiruha 388 16.09.19 12:09 Сейчас в теме
А зачем было "ОБЪЕДИНИТЬ ВСЕ" , а не "ОБЪЕДИНИТЬ"
и что будет если условие "ГрафикиРаботыПоВидамВремени.ГрафикРаботы В" заменить на внутреннее соединение (Подзапрос с "Объединить")
pm74; A_Max; +2 Ответить
15. azazana 71 16.09.19 12:14 Сейчас в теме
(13)
А зачем было "ОБЪЕДИНИТЬ ВСЕ" , а не "ОБЪЕДИНИТЬ"

Это вопрос скорее к разработчикам типового решения.
А идею с "ОБЪЕДИНИТЬ" вместо "ОБЪЕДИНИТЬ ВСЕ" попробую. Мне кажется, что ничего не изменится, но я попробую.
21. azazana 71 18.09.19 15:35 Сейчас в теме
(13) Проверила - выполняется так же долго.
20. DrZombi 286 17.09.19 06:49 Сейчас в теме
(0) Крут тот, кто понимает что ему надо и как оно работает.
Все зависит от задачи и структуры данных....
24. capitan 2466 01.10.19 18:06 Сейчас в теме
Я бы для начала вынес подзапрос из условия во временную таблицу.
26. azazana 71 12.10.19 19:28 Сейчас в теме
Я попробовала. Результат 34 секунды. То же самое + индексирование в запросе по полю сотрудник выигрыша в скорости так же не дало.
25. vasilev2015 2686 11.10.19 16:40 Сейчас в теме
Здравствуйте, Анна !

Как правило, вместо проверки включения лучше использовать внутреннее соединение.
27. azazana 71 12.10.19 20:09 Сейчас в теме
(25) Добрый вечер!
Спасибо, это хороший совет. Я проверила - результат выполнения 2 секунды запроса, индекс используется.
Посмотрела план запроса, он очень похож на тот который получился при использовании ИЛИ.
28. ArsenProg 12.07.20 15:34 Сейчас в теме
(27) Здравствуйте а как делать такие замеры !*?
29. olegpochekutov 1 13.01.22 07:26 Сейчас в теме
Оставьте свое сообщение