0. w.r. 476 22.08.19 13:20 Сейчас в теме

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

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

Перейти к публикации

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


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

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

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

А это не верное утверждение. OR в целом будет не быстрее.
15. w.r. 476 22.08.19 17:01 Сейчас в теме
(13) не быстрее будет только в одном случае - если при OR план будет использовать index scan, а при Union - index seek во всех ветках. Если хотя бы в одной ветке Union будет использоваться index scan, то OR будет даже немного быстрее. Кстати, если записей в целом выбирается немного план выполнения OR тоже использует поиск по индексу. Тем более clustered index scan сам по себе довольно быстрый
16. Diversus 2024 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. 476 22.08.19 17:28 Сейчас в теме
(16) вы невнимательно читали статью. Селективность при OR объединяется, и когда она достигает большого значения, сервер вместо поиска по индексу (index seek) выбирает полное сканирование индекса (index scan). При Union селекивность не объединяется, так как по сути это несколько выборок, объединённых в общий набор записей
23. Diversus 2024 22.08.19 17:32 Сейчас в теме
(22)
вы невнимательно читали статью. Селективность при OR объединяется, и когда она достигает большого значения, сервер вместо поиска по индексу (index seek) выбирает полное сканирование индекса (index scan). При Union селекивность не объединяется, так как по сути это несколько выборок, объединённых в общий набор записей

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

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

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

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

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


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

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

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

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

Вакансии

Программист, аналитик, эксперт 1С
Санкт-Петербург
По совместительству

Технический лидер, архитектор 1С, руководитель проектов
Санкт-Петербург
зарплата от 150 000 руб.
Полный день

Ведущий 1С консультант по БГУ
Омск
зарплата от 50 000 руб. до 95 000 руб.
Полный день

Специалист внедрения и сопровождения 1С
Омск
зарплата от 25 000 руб. до 50 000 руб.
Полный день

Автор новостных обзоров на тему 1С и бухучета
Санкт-Петербург
По совместительству