Поменял INNER join на LEFT join и все заработало

[shadow7256]

Уважаемые,

хотелось бы понять почему такое произошло. Есть таблица Parent, в ней около миллиона записей. Есть 5 дочерних таблиц, связанных с родительской через FK. Покажу только основные поля, в таблицах понятно дело куча других полей. На таблице Parent есть non clustered index на поле CreatedDate (просто для информации).

Code: Select all

TABLE Parent
-------------------------------------
id                  INT
CreatedDate  DATETIME


TABLE Child_1
--------------------------------------
id                   INT
ParentID         INT

Есть запрос, который выбирает TOP XX записей из таблицы Parent и соотвественно другие поля из дочерних таблиц.

Code: Select all

SELECT TOP (xxx)  FROM Parent p INNER JOIN Child_1 c1 ON p.id = c1.ParentID
                                                   INNER JOIN Child_2 c2 ON p.id = c2.ParentID

Выше приведенный запрос выполняется мгновенно. НО .. Если я добавлю в конец запроса условие ORDER BY CreatedDate DESC (или любой другое поле для сортировки) то запрос зависает на долгое время.

Code: Select all

SELECT TOP (xxx)  FROM Parent p INNER JOIN Child_1 c1 ON p.id = c1.ParentID
                                                   INNER JOIN Child_2 c2 ON p.id = c2.ParentID
                                                   ORDER BY CreatedDate DESC

НО... Если я поменяю условия INNER на LEFT и оставлю OrderBy то запрос опять выполняется ОЧЕНЬ быстро

Code: Select all

SELECT TOP (xxx)  FROM Parent p LEFT JOIN Child_1 c1 ON p.id = c1.ParentID
                                                   LEFT JOIN Child_2 c2 ON p.id = c2.ParentID
                                                   ORDER BY CreatedDate DESC

Понятно что наверное нужно видеьт структуру всех таблиц и индексов, но может быть навскидку объясните почему такое имеет место быть.

[Dmitry67]

В execution plan сила заключена большая, падаван юный.

[shadow7256]

В execution plan сила заключена большая, падаван юный.

Согласен Вот более подробная информация. Таблица Transactions (в ней 500 тысяч записей):

TABLE Transactions
--------------------------------
id INT PRIMARY KEY
SourceID INT (тут стоит non clustered index и также FK на таблицу Sources)
CreatedDate DATETIME (тут стоит non clustered index)


TABLE Sources
---------------------------------
id INT PRIMARY KEY
Name VARCHAR(200)


Выборка первая:

Code: Select all

SELECT top 100 t.TransactionId
FROM   dbo.Transactions AS T INNER JOIN Sources S ON T.SourceId = S.SourceId  
 	   order by T.CreatedDate desc

План

1.png

Второй запрос:

Code: Select all

SELECT top 100 t.TransactionId
FROM   dbo.Transactions AS T LEFT JOIN Sources S ON T.SourceId = S.SourceId  
 	   order by T.CreatedDate desc

План:

2.png

В первом запросе Index seek возвращает все 500 тысяч записей.. потом они сортируются..

во втором запросе возвращаются только 100 записей.

Век живи .. век учись.

Хотелось бы почитать полезную литературку которая бы объясняла подобные вещи

[Dmitry67]

А все понятно
Table sources was optimized away
Потому что не важно, есть ли там записи

[shadow7256]

Тогда другой запрос. Вместо таблицы Sources используется таблица Clients (тоже дочерняя таблица для Transactions). Использую INNER JOIN

Code: Select all

SELECT top 100 t.TransactionId
FROM   dbo.Transactions AS t INNER JOIN  dbo.Clients AS c ON c.ClientId = t .ClientId 
 	   order by t.CreatedDate desc

План:

3.png

И опять все нормально. Изначально используется non clustered index чтобы выбрать только 100 записей из таблицы Transactions и уже потом идет работа с ними.

Так почему же с INNER JOIN Source такого нет

[Dmitry67]

Потому что Source имеет JOIN по sourceid, и он думает что раз уж sourceid читается, то почитаем его из соответсвующего индекса
Проапдейтите все статистики с FULLSCAN. Чтото поменяется?
Какая кстати версия SQL?

[shadow7256]

Потому что Source имеет JOIN по sourceid, и он думает что раз уж sourceid читается, то почитаем его из соответсвующего индекса
Проапдейтите все статистики с FULLSCAN. Чтото поменяется?
Какая кстати версия SQL?

вообщем примерно понятно. INNER JOIN намного более строгий, чем LEFT JOIN. Когда LEFT JOIN стоит то просто по CreatedDate индексу выбираются 100 последних записей и все. Никаких 500 тыщ записей и дальнейшей сортировки не надо.

мы работаем с SQL Server 2012

[dema501]

Потому что Source имеет JOIN по sourceid, и он думает что раз уж sourceid читается, то почитаем его из соответсвующего индекса
Проапдейтите все статистики с FULLSCAN. Чтото поменяется?
Какая кстати версия SQL?

вообщем примерно понятно. INNER JOIN намного более строгий, чем LEFT JOIN. Когда LEFT JOIN стоит то просто по CreatedDate индексу выбираются 100 последних записей и все. Никаких 500 тыщ записей и дальнейшей сортировки не надо.

мы работаем с SQL Server 2012

Строгий не совсем правильное слово, посмотрите картинку

[iDesperado]

все просто, если ты требуешь лефт джоин то сервер может смело забить на проверку парент таблиц. по барабану, есть ссылка на парент таблицу или в ссылке нулл.
другое дело если ты требуешь инер джоин, забить на проверку уже нельзя, те что с нулл не должны в топ 100 попасть, отсюда и реальные джоины в плане + тормоза.
что касается случая с Clients то ты не верно читаешь план. реально там читается нахрен весь индекс t.CreatedDate. не 100 записей, а целиком. от начала до конца. потом для все дат выясняется PK от транзакции и потом по еще и долбиться в Clients

[shadow7256]

все просто, если ты требуешь лефт джоин то сервер может смело забить на проверку парент таблиц. по барабану, есть ссылка на парент таблицу или в ссылке нулл.
другое дело если ты требуешь инер джоин, забить на проверку уже нельзя, те что с нулл не должны в топ 100 попасть, отсюда и реальные джоины в плане + тормоза.
что касается случая с Clients то ты не верно читаешь план. реально там читается нахрен весь индекс t.CreatedDate. не 100 записей, а целиком. от начала до конца. потом для все дат выясняется PK от транзакции и потом по еще и долбиться в Clients

Когда я говорил про 100 записей то я имел в виду что после скана индекса по CreatedDate возвращаются только 100 записей. Если на стрелочку навести, то покажет что вернулось 100 записей. И никакой сортировки нет.

А вот видишь там выше с Source когда было то стрелка жирная там? там возвращались все 500 тысяч записей плюс потом сортировка была.

[iDesperado]

Когда я говорил про 100 записей то я имел в виду что после скана индекса по CreatedDate возвращаются только 100 записей. Если на стрелочку навести, то покажет что вернулось 100 записей. И никакой сортировки нет.

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

[shadow7256]

В плане который идет с Clients видно, что хоть в условии стоит ORDER BY CreatedDate, но на самом деле никакого Sort не происходит. А так как в таблице Transactions стоит non clustered index на поле CreatedDate, то мне кажется, что скан по этому индексу каким то образом выбирает нужные 100 записей в убывающем порядке.

Дима я прав?

[Dmitry67]

Да, он получает данные уже в нужном порядке

[iDesperado]

Да, он получает данные уже в нужном порядке

а можно понять в плане с clients чтение по idx_creationdate это full index scan или range scan ? что толстенькие/тоненькие стрелочки означают в плане ?

[Dmitry67]

Ширина стрелочек означает колочество записей в них
На стрелочки вы тоже можете мышку подвести и она покажет

Если мы про одну и ту же картинку, то там full scan, который вычитывает только часть записей, потому что стоит под top. То есть он начинает - и top говорит ему - хватит. Сравните толщину стрелочек до и после top.