Использование Union вместо OR

22.08.19

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

Предлагаю вашему вниманию перевод статьи Derek Dieter "Using Union Instead of OR". Оригинал доступен по ссылке http://sqlserverplanet.com/optimization/using-union-instead-of-or.

Иногда медленные запросы можно исправить, немного изменив запрос. Один из таких примеров может быть проиллюстрирован, когда несколько значений сравниваются в предложении WHERE с помощью оператора OR или IN. Часто OR может вызывать сканирование индекса или таблицы, которая может не быть предпочтительным планом выполнения с точки зрения потребления ввода-вывода или общей скорости запросов.

Многие переменные вступают в игру, когда оптимизатор запросов создает план выполнения. Эти переменные включают в себя множество характеристик оборудования, настроек экземпляра, настроек базы данных, статистики (таблица, индекс, auto-generated), а также способ написания запроса. Здесь мы меняем способ написания запроса. Каким бы неожиданным это ни казалось, даже если два разных запроса могут возвращать одни и те же результаты, путь, по которому они идут, может быть совершенно разным в зависимости от формата запроса.
 

UNION vs OR


В большей части моего опыта работы с SQL Server, OR обычно менее эффективен, чем UNION. То, что обычно происходит с OR, это то, что он чаще вызывает сканирование. Это порой может быть лучший путь для некоторых случаев, и я оставлю это отдельной статье, но в целом я обнаружил, что когда затрагивается большое количество записей — это является основной причиной медлительности. Итак, давайте начнем наше сравнение.

Вот наш оператор OR:
 

SELECT SalesOrderID, *
FROM sales.SalesOrderDetail
WHERE ProductID = 750 OR ProductID = 953




Из этого плана выполнения мы видим, что мы выполняем сканирование 121 000 строк. (Вы не можете видеть количество строк, но это так).

Теперь выполним тот же запрос, но написанный с использованием UNION вместо OR:
 

SELECT [SalesOrderID], *
FROM sales.SalesOrderDetail
WHERE ProductID = 750
UNION
SELECT [SalesOrderID], *
FROM sales.SalesOrderDetail
WHERE ProductID = 953




Здесь мы видим две ветви операций. Одна ветвь затрагивает 358 строк, а другая — 346 строк. Обе ветви встречаются для выполнения операции конкатенации, объединяющей оба набора результатов. У нас есть два отдельных поиска, но у нас также есть поиск ключей для получения необходимого списка SELECT. Это не было необходимо для операции сканирования, потому что мы все равно затрагивали все строки в операции сканирования, таким образом, данные были получены во время сканирования, а не после. Это связано с индексом и нужными нам строками, а не с UNION или OR. Однако я скажу, что выборка (select) также является фактором выбора поиска против сканирования (seek vs scan), но мы проигнорируем это в этой статье.
 

Объяснение


Почему UNION вызывает больше поисков вместо сканирований, потому что каждая операция должна удовлетворять определенному требованию селективности, чтобы претендовать на поиск. (Селективность — это уникальность конкретного фильтруемого столбца). OR происходит в одной операции, так что, когда селективность для каждого столбца объединяется и она превышает определенный процент, то сканирование считается более эффективным.

Поскольку UNION по умолчанию выполняет отдельную операцию для каждого оператора, селективность каждого столбца не объединяется, давая ему больше шансов на выполнение поиска. Теперь, поскольку UNION выполняет две операции, они должны сопоставить свои результирующие наборы, используя вышеописанную операцию конкатенации. Как правило, это не дорогостоящая операция.

Следует также отметить, что предложение OR работает так же, как оператор IN.

Надеюсь, этот совет поможет. Я считаю, что это очень ценно при работе с системами, требующими высокого параллелизма.

Источник Хабр

sql server query planning

См. также

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

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

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

13.03.2024    2963    spyke    26    

42

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

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

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

13.03.2024    5099    vasilev2015    19    

37

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

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

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

1 стартмани

15.02.2024    7625    158    ZAOSTG    67    

96

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

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

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

09.01.2024    5966    doom2good    48    

63

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

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

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

20.11.2023    8850    ivanov660    6    

76

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

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

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

15.11.2023    5097    a.doroshkevich    20    

72

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

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

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

11.10.2023    16166    skovpin_sa    14    

98
Комментарии
В избранное Подписаться на ответы Сортировка: Древо развёрнутое
Свернуть все
1. DJDUH 17 22.08.19 13:42 Сейчас в теме
OR происходит в одной операции, так что, когда селективность для каждого столбца объединяется и она превышает определенный процент, то сканирование считается более эффективным.


То я так и не понял "OR" - плохо или нет?
3. w.r. 643 22.08.19 13:48 Сейчас в теме
(1) вообще, по своему опыту могу сказать, что почти без разницы. С OR - один запрос, UNION - 2 запроса с объединением выборок. Поэтому в целом быстрее будет именно OR. Нужны специфические условия, чтобы с UNION юбыл план с index seek, а с OR - index scan и при том выборка была достаточно большой, чтобы UNION был значительно быстрее. Но, в любом случае, нужно смотреть планы конкретных запросов и их анализировать
9. Diversus 2306 22.08.19 14:40 Сейчас в теме
(3)
С OR - один запрос, UNION - 2 запроса с объединением выборок. Поэтому в целом быстрее будет именно OR.

В статье написано, что с OR один скан 121000 строк, а с UNION "Одна ветвь затрагивает 358 строк, а другая — 346 строк".
Так что надо использовать UNION, так будет быстрее в случае автора. Да и 1С дает точно такую же рекомендацию в системе стандартов и методик разработки (см скриншот).

Источник: Глава "Использование логического ИЛИ в условиях"
Прикрепленные файлы:
12. w.r. 643 22.08.19 16:39 Сейчас в теме
(9) так получилось, потому что в плане в первом случае index scan, во второму index seek
13. Diversus 2306 22.08.19 16:41 Сейчас в теме
(12) Я про то, что в (3) вы пишите:
вообще, по своему опыту могу сказать, что почти без разницы. С OR - один запрос, UNION - 2 запроса с объединением выборок. Поэтому в целом быстрее будет именно OR.

А это не верное утверждение. OR в целом будет не быстрее.
15. w.r. 643 22.08.19 17:01 Сейчас в теме
(13) не быстрее будет только в одном случае - если при OR план будет использовать index scan, а при Union - index seek во всех ветках. Если хотя бы в одной ветке Union будет использоваться index scan, то OR будет даже немного быстрее. Кстати, если записей в целом выбирается немного план выполнения OR тоже использует поиск по индексу. Тем более clustered index scan сам по себе довольно быстрый
16. Diversus 2306 22.08.19 17:06 Сейчас в теме
(15)
не быстрее будет только в одном случае - если при OR план будет использовать index scan, а при Union - index seek во всех ветках. Если хотя бы в одной ветке Union будет использоваться index scan, то OR будет даже немного быстрее. Кстати, если записей в целом выбирается немного план выполнения OR тоже использует поиск по индексу. Тем более clustered index scan сам по себе довольно быстрый


А как так может получиться, что при OR будет index scan, а при Union - index seek? Если учитывать, что условия по одним и тем же полям?
Мне кажется, то о чем вы говорите быть даже теоретически не может.
На сайте 1С классный пример на эту тему:
Через ИЛИ
ВЫБРАТЬ Товар.Наименование ИЗ Справочник.Товары КАК Товар ГДЕ Артикул = "001" ИЛИ Артикул = "002"

Через ОБЪЕДИНИТЬ ВСЕ
ВЫБРАТЬ Товар.Наименование ИЗ Справочник.Товары КАК Товар ГДЕ Артикул = "001"
 |ОБЪЕДИНИТЬ ВСЕ
 |ВЫБРАТЬ Товар.Наименование ИЗ Справочник.Товары КАК Товар ГДЕ Артикул = "002"


Обратите внимание, речь идет в данном случае о том, что есть ОДИНАКОВОЕ поле для отбора.
22. w.r. 643 22.08.19 17:28 Сейчас в теме
(16) вы невнимательно читали статью. Селективность при OR объединяется, и когда она достигает большого значения, сервер вместо поиска по индексу (index seek) выбирает полное сканирование индекса (index scan). При Union селекивность не объединяется, так как по сути это несколько выборок, объединённых в общий набор записей
23. Diversus 2306 22.08.19 17:32 Сейчас в теме
(22)
вы невнимательно читали статью. Селективность при OR объединяется, и когда она достигает большого значения, сервер вместо поиска по индексу (index seek) выбирает полное сканирование индекса (index scan). При Union селекивность не объединяется, так как по сути это несколько выборок, объединённых в общий набор записей

Вы написали, что OR в целом будет быстрее чем UNION в подобных запросах. Я ответил, что нет и привел ссылку на сайт 1С и на систему стандартов и методик.
Так какой ответ верный по вашему мнению?
24. w.r. 643 22.08.19 17:36 Сейчас в теме
(23) проблема в том, что и при Union может быть большая селективность или в OR очень маленькая. И так чаще всего и происходит. Проверял это на реальных данных - анализировал планы запроса
25. Diversus 2306 22.08.19 17:38 Сейчас в теме
(24)
Проверял это на реальных данных - анализировал планы запроса

Вы так и не ответили на вопрос.
Рекомендации 1С использовать всегда UNION в подобных запросах ошибочна или нет?
27. w.r. 643 22.08.19 17:44 Сейчас в теме
(25) вы опять невнимательно читали свою же ссылку. Там написано, что ИЛИ не рекомендуется использовать в условиях соединения запросов, то есть при СОЕДИНЕНИЕ...ПО (JOIN...ON), а не в ГДЕ (WHERE)
28. Diversus 2306 22.08.19 17:50 Сейчас в теме
(27)
вы опять невнимательно читали свою же ссылку. Там написано, что ИЛИ не рекомендуется использовать в условиях соединения запросов, то есть при СОЕДИНЕНИЕ...ПО (JOIN...ON), а не в ГДЕ (WHERE)

В смысле не внимательно? Это вы не внимательны.
Цитата из статьи 1С:
Не следует использовать ИЛИ в секции ГДЕ запроса.

И статья про это же самое, даже примеры совпадающие на сайте 1С и в переводе.
29. w.r. 643 22.08.19 18:02 Сейчас в теме
(28) извиняюсь сейчас не с компьютера и статья открылась не полностью ваша. По поводу этой выдержки из вашей ссылки

Не следует использовать ИЛИ в секции ГДЕ запроса. Это может привести к тому, что СУБД не сможет использовать индексы таблиц и будет выполнять сканирование, что увеличит время работы запроса и вероянтность возникновения блокировок. Вместо этого следует разбить один запрос на несколько и объединить результаты.


Во первых, переведённая статья совсем не о том. Посмотрите в планы запроса на скриншотах. Индексы там везде используются, только идёт или поиск по индексу или сканирование индекса. Во вторых, рекомендации возможно устарели. И так было лет 20 назад чтобы индексы не использовались при условии OR в запросах SQL Server
32. triviumfan 92 26.08.19 16:53 Сейчас в теме
(25) Не всегда, а лишь рекомендация. В большинстве случаев OR работает быстрее.
34. Ndochp 103 27.08.19 11:05 Сейчас в теме
(32) Аще-то или работает медленнее, но оно понятнее человекам. Так как большинство запросов для людей, то нужно писать ИЛИ, а в нагруженных местах ставить Объединить.
35. triviumfan 92 27.08.19 14:05 Сейчас в теме
(34) Спасибо за глупый комментарий "человеков-архитекторов".
18. herfis 498 22.08.19 17:11 Сейчас в теме
(15)
Тем более clustered index scan сам по себе довольно быстрый

Смотря с чем сравнивать. Если есть покрывающий обычный индекс, или объем извлекаемых данных не из индекса невелик, то по обычному индексу будет быстрее чем по кластерному.
6. user-z99999 67 22.08.19 14:11 Сейчас в теме
(1)
То я так и не понял "OR" - плохо или нет?

"OR" - плохо для индексов.
Поэтому лучше писать через UNION запросы.
11. herfis 498 22.08.19 14:52 Сейчас в теме
(1) OR - хорошо. Но UNION зачастую лучше, если нужна оптимизация.
14. w.r. 643 22.08.19 16:45 Сейчас в теме
(11) не всегда. Например реальный случай, если выборка одного из условий очень большая. Тогда план запроса одной из веток при Union все-равно будет построен с использованием index scan, то есть будет просканирован весь индекс. А это нивелирует вторую ветку с поиском по индексу (index seek). Можете проверить, если хотите
17. herfis 498 22.08.19 17:08 Сейчас в теме
(14)
не всегда.

Дык "зачастую" <> "всегда". "Зачастую" = "часто"
А еще чаще UNION рулит, когда избавляешься от OR в условии соединения.
26. w.r. 643 22.08.19 17:38 Сейчас в теме
(17) не согласен. По моим наблюдениям все наоборот. В условиях соединений - может быть, нужно смотреть опять же план запроса
2. Cерый 25 22.08.19 13:42 Сейчас в теме
Благодарю за статью, тем не менее, читабельность упала вдвое (вам ехать или шашечки?)
5. w.r. 643 22.08.19 13:53 Сейчас в теме
(2) в данном случае да. Но когда человек пишет 10 условий OR, тогда читабельность выше наверно у UNION. В таких случаях лучше использовать IN. Так как IN работает эквивалентно OR, при этом воспринимается намного удобнее
4. acanta 22.08.19 13:49 Сейчас в теме
В любом случае, если 1сник в запросе напишет
ИЛИ, то платформа 1с не превратит этот запрос в UNION.
33. w.r. 643 26.08.19 17:20 Сейчас в теме
(25) поэтому и говорю, что каждый конкретный случай нужно рассматривать и анализировать план запроса
7. Cерый 25 22.08.19 14:12 Сейчас в теме
До сих пор полагал, что СУБД для каждой записи вычислит WHERE, причем за один проход?
Судя по оригиналу, речь о Microsoft SQL Server ...
8. acanta 22.08.19 14:14 Сейчас в теме
А то, что любое СКД основано на условиях в отборах компоновки в списке, в иерархии или не в списке, не в иерархии, это тоже плохо для индексов или там используется какой-то другой механизм ?
10. herfis 498 22.08.19 14:51 Сейчас в теме
Спасибо за перевод (если свой). Хорошее объяснение, почему конкретно UNION как правило эффективней.
Так-то это давно известный способ оптимизации тяжелых запросов.
Yashazz; w.r.; Fox-trot; +3 Ответить
19. skv_79 353 22.08.19 17:14 Сейчас в теме
Как показывает практика использование OR лучше отказываться в пользу UNION. Даже если на первый взгляд разницы нет, если количество выборки будет увеличиваться, а со временем обычно так и происходит, то UNION начнет в скорости превосходить.
20. herfis 498 22.08.19 17:20 Сейчас в теме
(19) Такое... Я бы назвал это преждевременной оптимизацией. Читабельность и простоту кода уже ухудшили, а пригодится ли эта оптимизация - неизвестно. Ессно, когда известно заранее - тогда стоит сразу заложиться. Тут без вопросов.
21. w.r. 643 22.08.19 17:24 Сейчас в теме
(19) проверял на таблице в несколько миллионов строк OR vs Union. OR немного быстрее, так как в плане, из-за большого количества записей по отборам, и там и там использовалось полное сканирование индекса (index scan)
30. Yashazz 4709 22.08.19 18:22 Сейчас в теме
Методика давно известна, холивар давно наскучил, а вот за труды по переводу, если сами переводили, плюсую.
skv_79; w.r.; +2 Ответить
31. w.r. 643 22.08.19 18:31 Сейчас в теме
(30) тут и не может быть в принципе холивара. Холивары разводят как раз любители разных методик. Каждый отдельный запрос должен анализироваться, исходя из конкретной ситуации, и выдаваться рекомендации
Оставьте свое сообщение