inner join vs left join — which is faster for the same result?
If both inner join and left join can achieve the same result, which one is faster and has better performance (especially on large data)?
Any ideas? How can I test their performance on a large data?
3 Answers 3
By adding the WHERE A.PK_A = B.PK_B the query optimizer is smart enough to understand that you wanted an inner join and use that instead. So they will have the same performance since the same execution plan will be created.
So never use a left join with where on the keys when you want an inner join , it will just be frustrating to maintain and understand.
Left join and inner join solves different purposes. You can check the performance of query by executing execution plan. It will tell What all indexes query is using, how many rows it’s scanning etc. There are very good tutorial available for the same on vendor’s website.
First of all, Inner join and left join are not same. INNER JOIN gets all records that are common between both tables
LEFT JOIN gets all records from the LEFT linked table but if you have selected some columns from the RIGHT table, if there is no related records, these columns will contain NULL.
So obviously in terms of performance, Inner Join is faster. Hope it will help you 🙂
INNER JOIN vs LEFT JOIN производительность в SQL Server
Я создал команду SQL, которая использует INNER JOIN для 9 таблиц, в любом случае эта команда занимает очень много времени (более пяти минут). Поэтому мои люди предложили мне изменить INNER JOIN на LEFT JOIN, потому что производительность LEFT JOIN лучше, несмотря на то, что я знаю. После того, как я его изменил, скорость запроса значительно улучшилась.
Я хотел бы знать, почему LEFT JOIN быстрее, чем INNER JOIN?
Моя команда SQL выглядит так: SELECT * FROM A INNER JOIN B ON . INNER JOIN C ON . INNER JOIN D и так далее
Обновление: Это краткое изложение моей схемы.
Вы проецируете какой-либо атрибут из coUOM ? В противном случае вы можете использовать полусоединение. Если да, вы можете использовать UNION как альтернатива. Размещение только вашего FROM пункт неадекватная информация здесь. — onedaywhen
Я так часто задавался этим вопросом (потому что все время вижу). — Paul Draper
Вы пропустили Order By в своей краткой схеме? Недавно я столкнулся с проблемой, когда изменение INNER JOIN на LEFT OUTER JOIN ускоряет запрос с 3 минут до 10 секунд. Если у вас действительно есть Order By в вашем запросе, я объясню дальше в качестве ответа. Похоже, все ответы на самом деле не объясняли случай, с которым я столкнулся. — Phuah Yee Keat
9 ответы
A LEFT JOIN абсолютно не быстрее, чем INNER JOIN . На самом деле это медленнее; по определению внешнее соединение ( LEFT JOIN or RIGHT JOIN ) должен делать всю работу INNER JOIN плюс дополнительная работа по обнулению результатов. Также ожидается возврат большего количества строк, что еще больше увеличит общее время выполнения просто из-за большего размера набора результатов.
(И даже если LEFT JOIN были быстрее в конкретный ситуации из-за некоторого трудновообразимого стечения факторов, он функционально не эквивалентен INNER JOIN , поэтому вы не можете просто заменить все экземпляры одного на другой!)
Скорее всего, ваши проблемы с производительностью кроются в другом месте, например, в том, что ключ кандидата или внешний ключ не проиндексированы должным образом. 9 столов — это довольно много, поэтому замедление может быть практически любым. Если вы опубликуете свою схему, мы сможем предоставить более подробную информацию.
Редактировать:
Размышляя над этим, я мог придумать одно обстоятельство, при котором LEFT JOIN может быть быстрее, чем INNER JOIN , и это когда:
- Некоторые из таблиц очень мелкие (скажем, до 10 рядов);
- Таблицы не имеют достаточных индексов для покрытия запроса.
Рассмотрим этот пример:
Если вы запустите это и просмотрите план выполнения, вы увидите, что INNER JOIN запрос действительно стоит больше, чем LEFT JOIN , потому что он удовлетворяет двум указанным выше критериям. Это потому, что SQL Server хочет выполнить хэш-соответствие для INNER JOIN , но делает вложенные циклы для LEFT JOIN ; бывший нормально намного быстрее, но поскольку количество строк настолько мало и индекса для использования нет, операция хеширования оказывается самой затратной частью запроса.
Вы можете увидеть тот же эффект, написав программу на своем любимом языке программирования для выполнения большого количества поисков в списке с 5 элементами, а не в хеш-таблице с 5 элементами. Из-за размера версия хеш-таблицы на самом деле медленнее. Но увеличьте его до 50 или 5000 элементов, и версия списка замедлится до обхода, потому что это O (N) по сравнению с O (1) для хеш-таблицы.
Но измените этот запрос на ID столбец вместо Name и вы увидите совсем другую историю. В этом случае он выполняет вложенные циклы для обоих запросов, но INNER JOIN версия может заменить одно из сканирований кластеризованного индекса поиском — это означает, что это будет буквально порядок величины быстрее с большим количеством строк.
Итак, вывод примерно такой, о чем я упоминал несколькими абзацами выше; это почти наверняка проблема индексации или покрытия индекса, возможно, в сочетании с одной или несколькими очень маленькими таблицами. Это единственные обстоятельства, при которых SQL Server может быть иногда выбирают худший план исполнения для INNER JOIN чем LEFT JOIN .
ответ дан 28 апр.
Есть еще один сценарий, который может привести к тому, что ВНЕШНЕЕ СОЕДИНЕНИЕ будет работать лучше, чем ВНУТРЕННЕЕ СОЕДИНЕНИЕ. Смотрите мой ответ ниже. — Dbenham
Хочу отметить, что практически отсутствует документация по базе данных, подтверждающая различие в производительности внутренних и внешних соединений. Внешние соединения немного дороже внутренних соединений из-за объема данных и размера набора результатов. Однако лежащие в основе алгоритмы (msdn.microsoft.com/en-us/library/ms191426(v=sql.105).aspx) одинаковы для обоих типов соединений. Производительность должна быть одинаковой, когда они возвращают одинаковые объемы данных. — Гордон Линофф
@Aaronaught. . . На этот ответ ссылались в комментарии, в котором говорилось о том, что «внешние соединения работают значительно хуже, чем внутренние». Я прокомментировал это просто для того, чтобы убедиться, что это неверное толкование не распространится. — Гордон Линофф
Я думаю, что этот ответ вводит в заблуждение в одном важном аспекте: потому что он утверждает, что «ЛЕВОЕ СОЕДИНЕНИЕ абсолютно не быстрее, чем ВНУТРЕННЕЕ СОЕДИНЕНИЕ». Эта строка неверна. это теоретически не быстрее, чем ВНУТРЕННЕЕ СОЕДИНЕНИЕ. это НЕ «абсолютно не быстрее». Вопрос конкретно в производительности. На практике я теперь видел несколько систем (от очень крупных компаний!), В которых INNER JOIN был смехотворно медленным по сравнению с OUTER JOIN. Теория и практика — разные вещи. — Дэвид Френкель
@DavidFrenkel: Это маловероятно. Я бы попросил показать A / B-сравнение с планами исполнения, если вы считаете, что такое несоответствие возможно. Возможно, это связано с кешированными планами запросов / выполнения или неверной статистикой. — Ааронот
Есть один важный сценарий, который может привести к тому, что внешнее соединение будет быстрее, чем внутреннее соединение, но он еще не обсуждался.
При использовании внешнего соединения оптимизатор всегда может удалить внешнюю объединенную таблицу из плана выполнения, если столбцы соединения являются PK внешней таблицы, и ни один из столбцов внешней таблицы не упоминается вне самого внешнего соединения. Например SELECT A.* FROM A LEFT OUTER JOIN B ON A.KEY=B.KEY и B.KEY — PK для B. И Oracle (я полагаю, что использовал выпуск 10), и Sql Server (я использовал 2008 R2) удаляют таблицу B из плана выполнения.
То же самое не обязательно верно для внутреннего соединения: SELECT A.* FROM A INNER JOIN B ON A.KEY=B.KEY может или не может требовать B в плане выполнения в зависимости от того, какие ограничения существуют.
Если A.KEY — внешний ключ, допускающий значение NULL, ссылающийся на B.KEY, тогда оптимизатор не может удалить B из плана, поскольку он должен подтвердить, что строка B существует для каждой строки A.
Если A.KEY является обязательным внешним ключом, ссылающимся на B.KEY, тогда оптимизатор может исключить B из плана, поскольку ограничения гарантируют существование строки. Но то, что оптимизатор может исключить таблицу из плана, не означает, что это произойдет. SQL Server 2008 R2 НЕ исключает B из плана. Oracle 10 ДЕЙСТВИТЕЛЬНО убирает B из плана. Легко увидеть, как в этом случае внешнее соединение будет превосходить внутреннее соединение на SQL Server.
Это тривиальный пример, непрактичный для автономного запроса. Зачем садиться за стол, если в этом нет необходимости?
Но это может быть очень важным соображением при проектировании видов. Часто создается представление «делать все», объединяющее все, что может потребоваться пользователю, связанное с центральной таблицей. (Особенно, если есть наивные пользователи, выполняющие специальные запросы, которые не понимают реляционную модель). Представление может включать все соответствующие столбцы из многих таблиц. Но конечные пользователи могут получить доступ к столбцам только из подмножества таблиц в представлении. Если таблицы соединены внешними соединениями, то оптимизатор может (и удаляет) удалить ненужные таблицы из плана.
Очень важно убедиться, что представление, использующее внешние соединения, дает правильные результаты. Как сказал Ааронаут, вы не можете слепо заменять ВНЕШНЕЕ СОЕДИНЕНИЕ на ВНУТРЕННЕЕ СОЕДИНЕНИЕ и ожидать тех же результатов. Но бывают случаи, когда это может быть полезно по соображениям производительности при использовании представлений.
Последнее замечание — я не тестировал влияние на производительность в свете вышеизложенного, но теоретически кажется, что вы сможете безопасно заменить INNER JOIN на OUTER JOIN, если вы также добавите условие НЕ ЕСТЬ NULL для предложения where.
why is this left join faster than an inner join?
So I’m tuning this query, and I am pretty sure that in this instance, I can replace an inner join with a left join without affecting the data. However, I’m not entirely sure why this is faster. Here is the query:
The bottleneck is at the table-value function join (Fnc_lastfundvalue). My hunch to why changing it to a left join is faster is that it can then reorder the joins and it causes less spillage into tempdb? Here is the query plan before and after changing INNER JOIN dbo.Fnc_lastfundvalue.. to LEFT JOIN dbo.Fnc_lastfundvalue..
NB: Execution plans above are created on a dev box. The production server is still on SQL Server 2008.
1 Answer 1
It looks as though some of the difference is because the slow plan was run first.
The slow plan was clearly operating against a cold cache as it shows additional physical reads and managed to accumulate an additional 15 seconds of PAGEIOLATCH_SH waits compared to the fast case.
This looks as though it affected both the execution of the TVF itself (which took 5.5 seconds compared with 1.35 in the fast case.) and the wider plan using the result of it.
You say in the comments that on second execution it took 11 seconds. This is still 3 times slower than the fast plan ( 3.446 seconds) so doesn’t explain all the performance difference.
The main problem you are experiencing is due to the poor default estimation for multi statement TVFs (fixed guess of 100 rows in compatibility levels 2014/2016 and 1 in earlier versions). In reality your TVF returns 1,715 rows.
In the inner join case because inner joins are associative and commutative it can be re-ordered flexibly and gets moved to the deepest part of the tree. This would make sense if one row was actually returned as it could whittle down the row count early for the other joins in the plan.
The execution plan is below. The pink annotations are «Actual Elapsed Time (ms)» from the XML.
Because of the 1 row estimate it starts off badly by joining onto dbo.ParticipantTrades and selecting a plan with nested loops and lookups. That nested loops has an elapsed time of 13.976 seconds (presumably most of which was spent waiting on the spilling sort immediately upstream to request rows from it, with 3.26 seconds taken up on the lookups themselves and associated IO waits for the physical reads at that operator).
923,646 rows are emitted from the join vs an estimated 1423.18 and this mis-estimate propagates upwards in the plan through 3 sorts and a hash join spilling as it goes (due to the row underestimate)
When you add the LEFT JOIN it can not be as freely re-ordered and the join happens much higher up in the plan (where it would do less damage in the problematic INNER JOIN case). The semantics of outer join help here anyway.
Whilst the estimate for the number of rows coming out of the TVF is still 1 this does not adversely affect the estimates for the join it is directly involved in (SQL Server assumes that the cardinality coming out of that join will be the same as that of the other sub tree to the join and this is in fact what happens — an outer join cannot reduce this number as a non joined row would still pass through — just with NULL for the fvh columns).
There are still cardinality estimation errors in the outer join plan but not of the same magnitude and it requests a sufficient memory grant to avoid spilling anywhere.
This cardinality estimation issue has been resolved in the most recent version with interleaved execution. In the meantime (as you say in the comments it needs to be 2008 compatible) you can manually interleave it by storing the TVF result into a #temp table and joining onto that to allow the count of the intermediate result (and column statistics) to be taken into account.
LEFT JOIN быстрее INNER JOIN?
Продолжаю заниматься оптимизацией запросов и заметил такую штуку — уже на 5-м запросе типа SELECT замена INNER на LEFT ускоряет запрос как миниму в 2 раза, а то и в 10. Почему так происходит?
Вот пример — время выполнения 4 сек.
Меняем INNER на LEFT — результат запроса остается тот же, а время сокращается до 0,005
Но у вас и результат должен быть разный
1-й запрос != 2-му запросу
Не стесняйтесь — показывайте реальные названия таблиц и колонок
Так проще понять и связи и остальное
Потому что первый вообще какой-то «левый»
INNER JOIN table2 AS t2 ON t1.object_id = t2.id AND t1.object_group = ‘com_group’
ЗАчем? если t1.object_group не в видимости t2
Может Вам нужен
LEOnidUKG, Но это никак не объясняет разницы в быстродействии
Дайте угадаю, таблица table2 у вас маленькая?
INNER JOIN обычно быстрее LEFT JOIN, у вас либо не хватает индексов либо хз что. То что вам тут дали картинку, это хорошо, но это не объясняет такого поведения. По картинке, кстати, ясно, что INNER JOIN абсолютно не эквивалент LEFT JOIN.
P.S. Пока писал, появился пост выше, почти такой-же.
table1 самая большая — более 300.000 записей
table2 — примерно 25000
остальные мизер. На всех полях что участвуют в объединении есть индексы. Результат запроса одинаков абсолютли
Попробовал ваш запрос — результат тот же — время выполнения 4,4 сек. т.е. дольше всех
Загрузите на SQL Fiddle схемы, можно будет подумать как оптимизировать.
Я привёл информацию, что это разные инструменты и результаты будут разные. Просто так заменить слова и радоваться не получится.
Я вообще стараюсь избегать таких запросов, лучше несколько простых и перебор, чем вот такие конструкции и потом ищи гадай, что и зачем, почему это сжирает память и ещё по 4-6 секунд выполняется.
тут можно экспериментировать сколько угодно
Например меняем порядок таблиц
LEOnidUKG:
Я привёл информацию, что это разные инструменты и результаты будут разные. Просто так заменить слова и радоваться не получится.
Я вообще стараюсь избегать таких запросов, лучше несколько простых и перебор, чем вот такие конструкции и потом ищи гадай, что и зачем, почему это сжирает память и ещё по 4-6 секунд выполняется.
Та нормальный запрос, То вы, наверное не видели джойнов по 10 таблиц, и вложенных запросов.