Внутреннее соединение против производительности левого соединения в SQL Server

Question

Внутреннее соединение против производительности левого соединения в SQL Server

Я создал команду SQL, которая использует внутреннее соединение для 9 таблиц, в любом случае эта команда занимает очень много времени (более пяти минут). Поэтому мои люди предлагают мне изменить внутреннее соединение на левое соединение, потому что производительность левого соединения лучше, в первый раз, несмотря на то, что я знаю. После того, как я изменил, скорость запроса значительно улучшилась.

Я хотел бы знать, почему LEFT JOIN быстрее, чем INNER JOIN?

моя команда SQL выглядит следующим образом:
SELECT * FROM A INNER JOIN B ON ... INNER JOIN C ON ... INNER JOIN D и так на

обновление:
Это краткое изложение моей схемы.

FROM sidisaleshdrmly a -- NOT HAVE PK AND FK

    INNER JOIN sidisalesdetmly b -- THIS TABLE ALSO HAVE NO PK AND FK

        ON a.CompanyCd = b.CompanyCd 

           AND a.SPRNo = b.SPRNo 

           AND a.SuffixNo = b.SuffixNo 

           AND a.dnno = b.dnno

    INNER JOIN exFSlipDet h -- PK = CompanyCd, FSlipNo, FSlipSuffix, FSlipLine

        ON a.CompanyCd = h.CompanyCd

           AND a.sprno = h.AcctSPRNo

    INNER JOIN exFSlipHdr c -- PK = CompanyCd, FSlipNo, FSlipSuffix

        ON c.CompanyCd = h.CompanyCd

           AND c.FSlipNo = h.FSlipNo 

           AND c.FSlipSuffix = h.FSlipSuffix 

    INNER JOIN coMappingExpParty d -- NO PK AND FK

        ON c.CompanyCd = d.CompanyCd

           AND c.CountryCd = d.CountryCd 

    INNER JOIN coProduct e -- PK = CompanyCd, ProductSalesCd

        ON b.CompanyCd = e.CompanyCd

           AND b.ProductSalesCd = e.ProductSalesCd 

    LEFT JOIN coUOM i -- PK = UOMId

        ON h.UOMId = i.UOMId 

    INNER JOIN coProductOldInformation j -- PK = CompanyCd, BFStatus, SpecCd

        ON a.CompanyCd = j.CompanyCd

            AND b.BFStatus = j.BFStatus

            AND b.ProductSalesCd = j.ProductSalesCd

    INNER JOIN coProductGroup1 g1 -- PK = CompanyCd, ProductCategoryCd, UsedDepartment, ProductGroup1Cd

        ON e.ProductGroup1Cd  = g1.ProductGroup1Cd

    INNER JOIN coProductGroup2 g2 -- PK = CompanyCd, ProductCategoryCd, UsedDepartment, ProductGroup2Cd

        ON e.ProductGroup1Cd  = g2.ProductGroup1Cd

713 8

performance sql-server sql

8 ответов:

Comments

Ничего не найдено.

Aaronaught · Accepted Answer · 2010-04-28 07:30:24

A LEFT JOIN абсолютно не быстрее, чем INNER JOIN. На самом деле, это медленнее; по определению, внешнее соединение (LEFT JOIN или RIGHT JOIN) должен сделать всю работу INNER JOIN плюс дополнительная работа нулевого расширения результатов. Также ожидается, что будет возвращено больше строк, что еще больше увеличит общее время выполнения просто из-за большего размера результирующего набора.

(и даже если a LEFT JOINбыли быстрее конкретные ситуаций из-за некоторых трудно представить себе слияние факторов, оно функционально не эквивалентноINNER JOIN, Так что вы не можете просто пойти заменить все экземпляры одного с другим!)

скорее всего, ваши проблемы с производительностью лежат в другом месте, например, не имея ключа-кандидата или внешнего ключа, индексированного должным образом. 9 таблиц довольно много, чтобы присоединиться, так что замедление может быть буквально почти в любом месте. Если вы опубликуете свою схему, мы сможем предоставить больше подробности.

Edit:

размышляя далее об этом, я мог бы подумать об одном обстоятельстве, при котором LEFT JOIN может быть быстрее, чем INNER JOIN и:

некоторые из таблиц очень маленький (скажем, под 10 строк);

таблицы не имеют достаточных индексов для покрытия запроса.

Рассмотрим пример:
CREATE TABLE #Test1
(
    ID int NOT NULL PRIMARY KEY,
    Name varchar(50) NOT NULL
)
INSERT #Test1 (ID, Name) VALUES (1, 'One')
INSERT #Test1 (ID, Name) VALUES (2, 'Two')
INSERT #Test1 (ID, Name) VALUES (3, 'Three')
INSERT #Test1 (ID, Name) VALUES (4, 'Four')
INSERT #Test1 (ID, Name) VALUES (5, 'Five')

CREATE TABLE #Test2
(
    ID int NOT NULL PRIMARY KEY,
    Name varchar(50) NOT NULL
)
INSERT #Test2 (ID, Name) VALUES (1, 'One')
INSERT #Test2 (ID, Name) VALUES (2, 'Two')
INSERT #Test2 (ID, Name) VALUES (3, 'Three')
INSERT #Test2 (ID, Name) VALUES (4, 'Four')
INSERT #Test2 (ID, Name) VALUES (5, 'Five')

SELECT *
FROM #Test1 t1
INNER JOIN #Test2 t2
ON t2.Name = t1.Name

SELECT *
FROM #Test1 t1
LEFT JOIN #Test2 t2
ON t2.Name = t1.Name

DROP TABLE #Test1
DROP TABLE #Test2
если вы запустите этот и посмотреть план выполнения, вы увидите, что INNER JOIN запрос действительно стоит больше, чем LEFT JOIN, потому что он удовлетворяет двум вышеуказанным критериям. Это потому, что SQL Server хочет сделать хэш-матч для INNER JOIN, но не вложенные циклы для LEFT JOIN; бывший обычно гораздо быстрее, но так как количество строк так мало и там нет индекса для использования, операция хэширования оказывается самой дорогой частью запроса.

вы можно увидеть тот же эффект, написав программу на вашем любимом языке программирования, чтобы выполнить большое количество поисков в списке с 5 элементами, по сравнению с хэш-таблицей с 5 элементами. Из-за размера версия хэш-таблицы на самом деле медленнее. Но увеличьте его до 50 элементов или 5000 элементов, и версия списка замедляется до обхода, потому что это O(N) против O(1) для хэш-таблицы.

но изменить этот запрос, чтобы быть на вместо Name и вы увидите очень другая история. В этом случае он делает вложенные циклы для обоих запросов, но INNER JOIN версия может заменить один из кластеризованных индексов сканирования с поиском-это означает, что это будет буквально порядок быстрее с большим количеством строк.

таким образом, вывод более или менее то, что я упомянул несколько абзацев выше; это почти наверняка проблема индексации или индексного покрытия, возможно, в сочетании с одной или несколькими очень маленькими таблицами. Это единственные обстоятельства, при которых SQL Server может иногда выбирают худший план выполнения для INNER JOIN чем a LEFT JOIN.

dbenham · Accepted Answer · 2012-12-04 22:42:26

при использовании внешнего соединения оптимизатор всегда может удалить внешнюю объединенную таблицу из плана выполнения, если столбцы соединения являются PK внешней таблицы, и ни один из столбцов не выбран из внешней таблицы. Например SELECT A.* FROM A LEFT OUTER JOIN B ON A.KEY=B.KEY и B. ключ-это PK для B. Как Oracle (я считаю, что использовал выпуск 10), так и Sql Server (я использовал 2008 Р2) чернослив таблицу в соответствии с планом выполнения.

то же самое не обязательно верно для внутреннего соединения: SELECT A.* FROM A INNER JOIN B ON A.KEY=B.KEY может или не может требовать B в плане выполнения в зависимости от того, какие ограничения существуют.

Если A. KEY является нулевым внешним ключом, ссылающимся на B. KEY, то оптимизатор не может удалить B из плана, потому что он должен подтвердить, что строка B существует для каждой строки A.

Если A. KEY является обязательным внешним ключом, ссылающимся на B. KEY, то оптимизатор может удалить B из план, потому что ограничения гарантируют существование строки. Но только потому, что оптимизатор может удалить таблицу из плана, это не значит, что это будет. SQL Server 2008 R2 не удаляет B из плана. Oracle 10 действительно выбрасывает B из плана. Легко увидеть, как внешнее соединение будет выполнять внутреннее соединение на SQL Server в этом случае.

это тривиальный пример, а не практичный для автономного запроса. Зачем присоединяться к столу, Если вам это не нужно?

но это может быть очень важным соображением при проектировании представлений. Часто создается представление "сделать все", которое объединяет все, что может понадобиться пользователю, связанное с центральной таблицей. (Особенно если есть наивные пользователи, выполняющие специальные запросы, которые не понимают реляционную модель) представление может включать все соответствующие столбцы из многих таблиц. Но конечные пользователи могут обращаться только к столбцам из подмножества таблиц в представлении. Если таблицы соединены с внешними соединениями, то оптимизатор может (и делает) удалить ненужные таблицы из плана.

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

последнее замечание-я не проверял влияние на производительность в свете вышеизложенного, но теоретически кажется, что вы должны быть в состоянии безопасно замените внутреннее соединение внешним соединением, если вы также добавите условие не является NULL в предложение where.

Kvasi · Accepted Answer · 2014-01-03 12:03:24

Если все работает так, как должно, это не должно, но мы все знаем, что все работает не так, как должно, особенно когда дело доходит до оптимизатора запросов, кэширования планов запросов и статистики.

сначала я бы предложил перестроить индекс и статистику, а затем очистить кэш плана запроса, чтобы убедиться, что это не испортит ситуацию. Однако я испытал проблемы, даже когда это сделано.

Я испытывал некоторые случаи, когда левое соединение было быстрее, чем внутреннее соединение.

основная причина заключается в следующем: Если у вас есть две таблицы и вы соединяете столбец с индексом (на обеих таблицах). Внутреннее соединение будет производить тот же результат независимо от того, если вы зацикливаетесь на записях в индексе в таблице один и совпадаете с индексом в таблице два, как если бы вы сделали обратное: зацикливаетесь на записях в индексе в таблице два и совпадаете с индексом в таблице один. Проблема в том, что когда у вас есть вводящая в заблуждение статистика, оптимизатор запросов будет использовать статистику индекс, чтобы найти таблицу с наименьшими совпадающими записями (на основе других критериев). Если у вас есть две таблицы с 1 миллионом в каждой, в таблице один у вас есть 10 строк соответствия и в таблице два у вас есть 100000 строк соответствия. Лучшим способом было бы выполнить сканирование индекса в таблице один и сопоставление 10 раз в таблице два. Обратное было бы индексным сканированием, которое зацикливается на 100000 строках и пытается соответствовать 100000 раз и только 10 успешно. Поэтому, если статистика не верна, оптимизатор может выбрать неправильный таблица и индекс для перебора.

если оптимизатор решит оптимизировать левое соединение в том порядке, в котором оно написано, оно будет работать лучше, чем внутреннее соединение.

но оптимизатор может также оптимизировать левое соединение субоптимально как левое полусоединение. Чтобы сделать его выбрать тот, который вы хотите, вы можете использовать подсказку force order.

Francisco Pires · Accepted Answer · 2013-06-15 22:32:11

попробуйте оба запроса (один с внутренним и левым соединением) с OPTION (FORCE ORDER) в конце и опубликовать результаты. OPTION (FORCE ORDER) - это подсказка запроса, которая заставляет оптимизатор построить план выполнения с порядком соединения, указанным в запросе.

если INNER JOIN начинает работать так же быстро, как LEFT JOIN, это потому, что:

в запросе, составленном полностью INNER JOINs, порядок соединения не имеет значения. Это дает оптимизатору запросов свободу упорядочивать соединения по своему усмотрению, таким образом, проблема может зависеть от оптимизатора.

С LEFT JOIN, это не так, потому что изменение порядка соединения изменит результаты запроса. Это означает, что движок должен следовать порядку соединения, указанному в запросе, который может быть лучше, чем оптимизированный.

Не знаю, отвечает ли это на ваш вопрос, но я когда-то был в проекте, в котором были очень сложные запросы, делающие вычисления, которые полностью испортили оптимизатор. У нас были дела где FORCE ORDER позволит сократить время выполнения запроса от 5 минут до 10 секунд.

J.O. · Accepted Answer · 2013-06-19 19:20:32

провели ряд сравнений между левыми внешними и внутренними соединениями и не смогли найти согласованную разницу. Есть много переменных. Я работаю над базой данных отчетов с тысячами таблиц, многие из которых имеют большое количество полей, многие изменения с течением времени (версии поставщиков и локальный рабочий процесс) . Невозможно создать все комбинации индексов покрытия для удовлетворения потребностей такого широкого круга запросов и обработки исторических данных. Видел внутренние запросы убейте производительность сервера, потому что две большие (от миллионов до десятков миллионов строк) таблицы соединены внутри и вытягивают большое количество полей, а индекс покрытия не существует.

самая большая проблема, хотя, кажется, не появляется в обсуждениях выше. Возможно, ваша база данных хорошо разработана с триггерами и хорошо продуманной обработкой транзакций для обеспечения хороших данных. Мой часто имеет нулевые значения, где они не ожидаются. Да, определения таблиц могут применять no-null, но это не вариант в моем окружении.

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

вы можете быть очень быстро, как получить 90% необходимых данных и не обнаружить внутренние соединения молча удалили информацию. Иногда внутренние соединения могут быть быстрее, но я не верю, что кто-то делает это предположение, если они не рассмотрели план выполнения. Скорость важна, но точность важнее.

eddiegroves · Accepted Answer · 2010-04-28 06:49:35

ваши проблемы с производительностью, скорее всего, связаны с количеством выполняемых соединений и независимо от того, имеют ли столбцы, к которым вы присоединяетесь, индексы или нет.

в худшем случае вы можете легко сделать 9 целых сканирований таблицы для каждого соединения.

MarredCheese · Accepted Answer · 2017-03-01 23:29:52

внешние соединения могут обеспечить превосходную производительность при использовании в представлениях.

скажем, у вас есть запрос, который включает представление, и это представление состоит из 10 таблиц, соединенных вместе. Скажем, ваш запрос использует только столбцы из 3 из этих 10 таблиц.

Если бы эти 10 таблиц были внутреннее соединение вместе, то оптимизатор запросов должен был бы объединить их все, даже если ваш запрос сам по себе не нуждается в 7 из 10 таблиц. Это потому что внутреннее объединения сами по себе могут фильтровать данные, что делает их необходимыми для вычисления.

Если бы эти 10 таблиц были внешняя-присоединился вместе, то оптимизатор запросов будет только в те, которые были необходимы: 3 из 10 из них в этом случае. Это связано с тем, что сами соединения больше не фильтруют данные, и поэтому неиспользуемые соединения могут быть пропущенный.

источник: http://www.sqlservercentral.com/blogs/sql_coach/2010/07/29/poor-little-misunderstood-views/

Bazzzzzzz · Accepted Answer · 2018-03-14 13:47:50

Я нашел что-то интересное в SQL server при проверке, если внутренние соединения быстрее, чем левые соединения.

Если вы не включаете элементы левой объединенной таблицы, в инструкции select левое соединение будет быстрее, чем тот же запрос с внутренним соединением.

Если вы включаете левую объединенную таблицу в инструкцию select, внутреннее соединение с тем же запросом было равно или быстрее, чем левое соединение.