Добро пожаловать в форум, Guest  >>   Войти | Регистрация | Поиск | Правила | В избранное | Подписаться
Все форумы / Microsoft SQL Server Новый топик    Ответить
 Помогите разобраться с Nested Loop.  [new]
Павел-П
Member

Откуда:
Сообщений: 234
Привет.

1. Имеем начальный запрос initial_query.sql (см. присоединенный архив files.zip).
Его execution plan FirstPlan.sqlplan
Если посмотреть на план то можно увидеть Nested loop между таблицей CASES и CASENAME. Причем в outer таблице вроде бы как Actual Number of rows 19900 (Estimated number of rows 2193). При этом в inner таблице Actual Number of rows 2 (estimated number of rows 1)
Для меня это выглядит немного странно, ибо казалось, что лучше иметь outer таблицу с маленьким числом строк.

2. Переписываем запрос, меняя EXIST на CROSS APPLY.
Т.е. заставляем поменять местами inner и outer таблицу.
cross_apply_query.sql - Переписанный запрос.
Его execution plan - SecondPlan.sqlplan

Запрос начинает работать в 2 раза быстрее. Число logical reads вокруг таблицы CASES уменьшается в разы.

Пытаюсь понять, что не так с первым запросом. Почему EXISTS statement себя так ведет.

p.s.
1. option (recompile) поведение не меняет.
2. статистики обновлены.
3. Microsoft SQL Server 2008 R2 (SP1) - 10.50.2500.0 (X64) Jun 17 2011 00:54:03 Copyright (c) Microsoft Corporation Standard Edition (64-bit) on Windows NT 6.1 <X64> (Build 7601: Service Pack 1) (Hypervisor)

К сообщению приложен файл (files.zip - 9Kb) cкачать
13 сен 15, 20:34    [18144578]     Ответить | Цитировать Сообщить модератору
 Re: Помогите разобраться с Nested Loop.  [new]
SomewhereSomehow
Member

Откуда: Moscow
Сообщений: 2480
Блог
Павел-П,

Добрый день.

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

Дело не в самом операторе Exists а в том, как он раскрывается в данном конкретном случае. Оптимизатор умеет «раскрывать» некоторые подзапросы. В первом случае сработало правило LSJNtoDistOnJN (Left Semi Join To Distinct on Join). Т.е. полусоединение преобразуется во внутреннее соединение с последующим дистинктом над его результатами. Он ожидает 2193 строки из каждой таблицы, а в результате соединения должно было остаться 76. Стоимость плана 6.39214 юнита.

На деле получается, 19904 строки из внешней таблицы, условия поиска не один Seek ключ, а 64, ну и глубина индекса не знаю какая у вас, минимум 2, наверное. Получается 19904*64*2 = 2547712 чтений. Приблизительно стролько логических чтений должно быть в итоге.

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

Оптимизатор не умеет «раскрывать» подзапосы с TOP, это влияет на выбор порядка соединений. В данном случае в лучшую сторону. Стоимость плана 8.23405. Т.е. этот план дороже, чем первый, к тому же этой стоимости хватает, чтобы рассмотреть и выбрать параллельный план.

Переписав запрос, за счет TOP вы лишили оптимизатор возможности раскрыть подзапрос и изменили пространство вариантов перестановки соединений, что повлияло в лучшую сторону в данном случае (не факт, что в другом случае, будет что-то подобное, на самом деле, может быть и обратный эффект, так что не нужно это брать за правило).

Резюмируя, на мой взгляд, это недостаток оптимизатора, который, в идеале, без всякого переписывания должен быть получить план похожий на второй, только вместо top какой-нибудь stream aggregate или distinct sort. Сам недостаток кроется где-то в модели оценки и стоимости.
14 сен 15, 12:23    [18146097]     Ответить | Цитировать Сообщить модератору
 Re: Помогите разобраться с Nested Loop.  [new]
Павел-П
Member

Откуда:
Сообщений: 234
SomewhereSomehow,

Спасибо большое.

"не факт, что в другом случае, будет что-то подобное, на самом деле, может быть и обратный эффект, так что не нужно это брать за правило)".
Это как раз мой случай, поэтому и не получается взять метод за правило.

Для меня просто прикольно то, что вроде бы Estimated Number of rows в запросе показано так, что ну блин очевидно, что inner и outer таблицу надо менять местами. И тем не менее.
Ваш ответ очень полезно почитать.
14 сен 15, 12:48    [18146258]     Ответить | Цитировать Сообщить модератору
 Re: Помогите разобраться с Nested Loop.  [new]
SomewhereSomehow
Member

Откуда: Moscow
Сообщений: 2480
Блог
Павел-П,

Не за что.

Для inner части не одна предполагаемая строка. Для inner части Nested Loops нужно умножать число строк на кол-во выполнений, чтобы получить Estimated Number of Rows.
Вот, например, как показывает предполагаемое число строк Plan Explorer.
Картинка с другого сайта.
Ожидается по 2193 строки из каждой таблицы.
Где-то тут он ошибается, есть предположение, что сильную сумятицу в оценки вносит предикат с Like, т.к. его трудно оценить.

В любом случае, жаль, что оптимизатор не строит до более оптимальный план самостоятельно, без переписывания. Так и рождаются мифы типа "exist медленее join" и наоборот =)
14 сен 15, 13:01    [18146369]     Ответить | Цитировать Сообщить модератору
 Re: Помогите разобраться с Nested Loop.  [new]
Павел-П
Member

Откуда:
Сообщений: 234
SomewhereSomehow,

И еще раз спасибо.

Скажите Plan Explorer - это вот эта штука
http://sqlmag.com/database-administration/free-tool-sql-sentrys-plan-explorer

?
14 сен 15, 13:27    [18146545]     Ответить | Цитировать Сообщить модератору
 Re: Помогите разобраться с Nested Loop.  [new]
SomewhereSomehow
Member

Откуда: Moscow
Сообщений: 2480
Блог
Павел-П,

Да, вот их сайт, где можно скачать бесплатную версию: Plan Explorer
14 сен 15, 13:51    [18146667]     Ответить | Цитировать Сообщить модератору
Все форумы / Microsoft SQL Server Ответить