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

Откуда:
Сообщений: 95
Привет всем!

Вопрос по плану запроса.
Есть запрос с соединением 3х таблиц:

SELECT TOP 100
CASE
WHEN T1._Fld777_TYPE = 0x08 AND T1._Fld777_RTRef = @P1
THEN T2._Code
WHEN T1._Fld777_TYPE = 0x08 AND T1._Fld777_RTRef = @P2
THEN T3._Code
ELSE CAST(NULL AS NVARCHAR(9))
END
FROM _InfoRg750 T1 WITH(NOLOCK)
LEFT OUTER JOIN _Reference7 T2 WITH(NOLOCK)
ON T1._Fld777_TYPE = 0x08 AND T1._Fld777_RTRef = @P3
AND T1._Fld777_RRRef = T2._IDRRef
LEFT OUTER JOIN _Reference11 T3 WITH(NOLOCK)
ON T1._Fld777_TYPE = 0x08 AND T1._Fld777_RTRef = @P4
AND T1._Fld777_RRRef = T3._IDRRef

Запрос выполняется из 1С. Текст запроса с параметрами есть в приложении.
При выполнении соединения с таблицей Reference11 выбирается план с Nested Loops. В ведущей таблице 100 строк, ведомой таблицей является Reference11 и в ней всего 4 записи. Следовательно Reference11 внутри цикла опрашивается 100 раз, но вот чего я не понимаю почему при этом возвращается 400 строк?

Ведь в таблице всего 4 строки, зачем нужно возвращать 400?

Ссылка на базу, план запроса и текст запроса для воспроизведения находятся во вложении.

Заранее большое спасибо за ответы.

К сообщению приложен файл (Query and Plan.ZIP - 2Kb) cкачать
5 май 15, 22:21    [17604566]     Ответить | Цитировать Сообщить модератору
 Re: Вопрос по плану запроса. Nested Loops возвращает лишние строки  [new]
invm
Member

Откуда: Москва
Сообщений: 9785
Это кумулятивное значение - общее число строк, которое вернул оператор: 100 раз по 4 строки = 400
5 май 15, 23:06    [17604711]     Ответить | Цитировать Сообщить модератору
 Re: Вопрос по плану запроса. Nested Loops возвращает лишние строки  [new]
Андрей_Батькович
Member

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

Это я понимаю, я не понимаю зачем он это сделал.
6 май 15, 00:08    [17604935]     Ответить | Цитировать Сообщить модератору
 Re: Вопрос по плану запроса. Nested Loops возвращает лишние строки  [new]
invm
Member

Откуда: Москва
Сообщений: 9785
Кто "он" и что "это"?
6 май 15, 00:11    [17604939]     Ответить | Цитировать Сообщить модератору
 Re: Вопрос по плану запроса. Nested Loops возвращает лишние строки  [new]
Андрей_Батькович
Member

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

Он - оптимизатор
Это - зачем возвращать 400 строк когда можно 1 раз вернуть 4 строки.
6 май 15, 00:15    [17604944]     Ответить | Цитировать Сообщить модератору
 Re: Вопрос по плану запроса. Nested Loops возвращает лишние строки  [new]
invm
Member

Откуда: Москва
Сообщений: 9785
Андрей_Батькович
можно 1 раз вернуть 4 строки.
Вернуть куда? Вы представляете как работает NL?
6 май 15, 00:37    [17604987]     Ответить | Цитировать Сообщить модератору
 Re: Вопрос по плану запроса. Nested Loops возвращает лишние строки  [new]
Андрей_Батькович
Member

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

То что NL выполняет оператор скана таблицы 100 раз это все понятно, тут вопросов нет.
Вопрос по фактическому количеству строк.

Насколько я знаю фактическое количество строк, это то количество которое удовлетворяет условию, в данном случае условию соединения. Может быть это не всегда так?

При соединении со справочником _Reference11 условию удовлетворяет всего 1 строка, она выводится под номером 29 в результате запроса.
Так почему тогда фактическое количество строк равно 400 а не 1?
6 май 15, 01:40    [17605089]     Ответить | Цитировать Сообщить модератору
 Re: Вопрос по плану запроса. Nested Loops возвращает лишние строки  [new]
Mind
Member

Откуда: Лучший город на Земле
Сообщений: 2322
Андрей_Батькович
Насколько я знаю фактическое количество строк, это то количество которое удовлетворяет условию, в данном случае условию соединения. Может быть это не всегда так?
Нет.

Андрей_Батькович
При соединении со справочником _Reference11 условию удовлетворяет всего 1 строка, она выводится под номером 29 в результате запроса.
Вы понимаете что чтобы что-то соединить нужно сначала это что-то прочитать из таблицы? И для каждой строки из внешней таблицы сервер читает все 4 строки из внутренней, потому что используется сканирование индекса, а не поиск по индексу, то есть лишние строки отбрасываются в операторе Nested Loops, а не Clustered Index Scan.
Андрей_Батькович
Так почему тогда фактическое количество строк равно 400 а не 1?
Потому что фактическое количество строк прочитанное оператором Clustered Index Scan и переданное в Nested Loops равно 400 строк. Оптимизатор решил сделать фильтрацию в самом Nested Loops, а не при чтении из таблицы.
6 май 15, 02:41    [17605109]     Ответить | Цитировать Сообщить модератору
 Re: Вопрос по плану запроса. Nested Loops возвращает лишние строки  [new]
Андрей_Батькович
Member

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

Спасибо за ответ.

> Оптимизатор решил сделать фильтрацию в самом Nested Loops, а не при чтении из таблицы.

Понятно, а оптимизатор в принципе умеет фильтровать данные при чтении таблицы, а не в самом NL?
6 май 15, 12:03    [17606436]     Ответить | Цитировать Сообщить модератору
 Re: Вопрос по плану запроса. Nested Loops возвращает лишние строки  [new]
o-o
Guest
Андрей_Батькович
а оптимизатор в принципе умеет фильтровать данные при чтении таблицы, а не в самом NL?

конечно умеет.
у меня PK по custid,
и выбираю я orders с конкретным custid,
разумеется, он еще до NL customers отфильтрует:

К сообщению приложен файл. Размер - 66Kb
6 май 15, 12:17    [17606521]     Ответить | Цитировать Сообщить модератору
 Re: Вопрос по плану запроса. Nested Loops возвращает лишние строки  [new]
Андрей_Батькович
Member

Откуда:
Сообщений: 95
o-o,

Спасибо за ответ.

Ну вот и я предполагал что умеет, но тогда почему в моем случае этого не происходит?
6 май 15, 12:20    [17606542]     Ответить | Цитировать Сообщить модератору
 Re: Вопрос по плану запроса. Nested Loops возвращает лишние строки  [new]
a_voronin
Member

Откуда: Москва
Сообщений: 4846
Андрей_Батькович
Следовательно Reference11 внутри цикла опрашивается 100 раз, но вот чего я не понимаю почему при этом возвращается 400 строк?

Ведь в таблице всего 4 строки, зачем нужно возвращать 400?


Мне кажется мы по второму раунду пошли обсуждать вот эту тему.

https://www.sql.ru/forum/1155029-a/razacharovan

Потому что LEFT JOIN 100 строк на 4 даст 400 строк -- так работает JOIN (INNER, LEFT, RIGHT, FULL) все дадут 400
6 май 15, 12:22    [17606552]     Ответить | Цитировать Сообщить модератору
 Re: Вопрос по плану запроса. Nested Loops возвращает лишние строки  [new]
Glory
Member

Откуда:
Сообщений: 104751
Андрей_Батькович
Ну вот и я предполагал что умеет, но тогда почему в моем случае этого не происходит?

Потому что у вас запрос, в котором это не нужно делать
6 май 15, 12:27    [17606587]     Ответить | Цитировать Сообщить модератору
 Re: Вопрос по плану запроса. Nested Loops возвращает лишние строки  [new]
o-o
Guest
Андрей_Батькович
Ну вот и я предполагал что умеет, но тогда почему в моем случае этого не происходит?

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

К сообщению приложен файл. Размер - 66Kb
6 май 15, 12:28    [17606590]     Ответить | Цитировать Сообщить модератору
 Re: Вопрос по плану запроса. Nested Loops возвращает лишние строки  [new]
o-o
Guest
a_voronin
Мне кажется мы по второму раунду пошли обсуждать вот эту тему.
https://www.sql.ru/forum/1155029-a/razacharovan
Потому что LEFT JOIN 100 строк на 4 даст 400 строк -- так работает JOIN (INNER, LEFT, RIGHT, FULL) все дадут 400

вам только кажется.
Андрей_Батькович не требует "выкинуть лишние таблицы".
он интересуется, почему у него сканы вместо seek-ов.

вот вам ради бога пример с left join.
хочу этого кастомера с его orders или без них, если их нет.
все равно оптимизатор выбрал seek,
т.е. фильтрует до NL

К сообщению приложен файл. Размер - 85Kb
6 май 15, 12:35    [17606614]     Ответить | Цитировать Сообщить модератору
 Re: Вопрос по плану запроса. Nested Loops возвращает лишние строки  [new]
SomewhereSomehow
Member

Откуда: Moscow
Сообщений: 2480
Блог
Андрей_Батькович
Понятно, а оптимизатор в принципе умеет фильтровать данные при чтении таблицы, а не в самом NL?

Умеет.
NL имеет две основные стратегии:
- Nested Loops Join
- Nested Loops Apply (Nested Loops Trivial)


+ Nested Loops Join
Картинка с другого сайта.

- В основном используется когда предикат не может быть помещен/не выгодно помещать на уровень доступа к внутренней таблице
- Часто вызывает операцию spool для оптимизации доступа к базовой таблице
- В случае этого метода вы видите свойство Predicate в операторе плана

+ Nested Loops Apply
Картинка с другого сайта.

В качестве функции F(x) могут выступать:
- Пользовательская табличная функция
- DMV
- Поиск по индексу (index NL join)
- Поиск по индексу построенному на-лету (Index Spool – temporary index NL join)
- Сканирование и другие (в том числе сложные) подзапросы
- В случае этого метода вы видите свойство Outer Reference в операторе плана

Решение использовать один или другой подход принимается либо на основании стоимости, либо на основании семантики (не нарушить смысл запроса).
В вашем случае, причина - чисто стоймостная.
Если допишете хинт forceseek и сравните:
+
exec sp_executesql N'SELECT TOP(100)
CASE WHEN T1._Fld777_TYPE = 0x08 AND T1._Fld777_RTRef = @P1 THEN T2._Code WHEN T1._Fld777_TYPE = 0x08 AND T1._Fld777_RTRef = @P2 THEN T3._Code ELSE CAST(NULL AS NVARCHAR(9)) END
FROM _InfoRg750 T1 WITH(NOLOCK)
LEFT OUTER JOIN _Reference7 T2 WITH(NOLOCK)
ON T1._Fld777_TYPE = 0x08 AND T1._Fld777_RTRef = @P3 AND T1._Fld777_RRRef = T2._IDRRef
LEFT OUTER JOIN _Reference11 T3 WITH(NOLOCK, forceseek)
ON T1._Fld777_TYPE = 0x08 AND T1._Fld777_RTRef = @P4 AND T1._Fld777_RRRef = T3._IDRRef
option(loop join)
',N'@P1 varbinary(4),@P2 varbinary(4),@P3 varbinary(4),@P4 varbinary(4)',0x00000007,0x0000000B,0x00000007,0x0000000B

exec sp_executesql N'SELECT TOP(100)
CASE WHEN T1._Fld777_TYPE = 0x08 AND T1._Fld777_RTRef = @P1 THEN T2._Code WHEN T1._Fld777_TYPE = 0x08 AND T1._Fld777_RTRef = @P2 THEN T3._Code ELSE CAST(NULL AS NVARCHAR(9)) END
FROM _InfoRg750 T1 WITH(NOLOCK)
LEFT OUTER JOIN _Reference7 T2 WITH(NOLOCK)
ON T1._Fld777_TYPE = 0x08 AND T1._Fld777_RTRef = @P3 AND T1._Fld777_RRRef = T2._IDRRef
LEFT OUTER JOIN _Reference11 T3 WITH(NOLOCK)
ON T1._Fld777_TYPE = 0x08 AND T1._Fld777_RTRef = @P4 AND T1._Fld777_RRRef = T3._IDRRef
option(loop join)
',N'@P1 varbinary(4),@P2 varbinary(4),@P3 varbinary(4),@P4 varbinary(4)',0x00000007,0x0000000B,0x00000007,0x0000000B

То увидите, что первый запрос, с хинтом, стоит дороже. Оптимизатор может использовать NL Apply + поиск + startup filter (как он это делает в ветке плана выше), но он считает что это дороже.
Если бы таблица была побольше он бы выбрал другую стратеги.
Если это тестовая база - можете проверить:
update statistics _Reference11 with pagecount = 1000, rowcount = 100000;
dbcc freeproccache; -- Warning Free Cache

Он выбирает такой план без всяких хинтов.
6 май 15, 12:56    [17606753]     Ответить | Цитировать Сообщить модератору
 Re: Вопрос по плану запроса. Nested Loops возвращает лишние строки  [new]
Андрей_Батькович
Member

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

Большое Вам спасибо, за столь ясный и подробный ответ. Теперь все встало на свои места.
Тему можно закрывать.
6 май 15, 13:37    [17607059]     Ответить | Цитировать Сообщить модератору
Все форумы / Microsoft SQL Server Ответить