Добро пожаловать в форум, Guest  >>   Войти | Регистрация | Поиск | Правила | В избранное | Подписаться
Все форумы / Microsoft SQL Server Новый топик    Ответить
Топик располагается на нескольких страницах: [1] 2   вперед  Ctrl      все
 Упреждающая или неявная фильтрация LEFT JOIN  [new]
nicxxx
Member

Откуда: Москва
Сообщений: 58
Добрый день, коллеги.
Прошу помощи. Как называется поведение SQL сервера, описанное ниже, и где можно почитать об этом подробнее?
Если коротко - в плане запросе появляется дополнительное условие, которого нет в LEFT JOIN изначально.

Теперь подробнее.
Есть запрос в динамическом списке 1С, который выбирает документы и добавляет обороты по взаиморасчетам. Обороты добавляются левым соединением.
На языке 1С это выглядит так:

ВЫБРАТЬ
ДокументЗаказПоставщику.Ссылка КАК Ссылка,
ВЫБОР
КОГДА ДокументЗаказПоставщику.Проведен
И ДокументЗаказПоставщику.Статус <> ЗНАЧЕНИЕ(Перечисление.СтатусыЗаказовПоставщикам.НеСогласован)
И ДокументЗаказПоставщику.СуммаДокумента > 0
ТОГДА ВЫБОР
КОГДА ЕСТЬNULL(РасчетыСПоставщикамиОстатки.СуммаКонечныйОстаток, 0) > 0
ТОГДА ВЫРАЗИТЬ((ЕСТЬNULL(РасчетыСПоставщикамиОстатки.СуммаКонечныйОстаток, 0) * 100 / ДокументЗаказПоставщику.СуммаДокумента) КАК ЧИСЛО(15, 0))
ИНАЧЕ ВЫРАЗИТЬ((ЕСТЬNULL(-РасчетыСПоставщикамиОстатки.СуммаКонечныйОстаток, 0) * 100 / ДокументЗаказПоставщику.СуммаДокумента) КАК ЧИСЛО(15, 0))
КОНЕЦ
ИНАЧЕ 0
КОНЕЦ КАК ПроцентДолга

ИЗ
Документ.ЗаказПоставщику КАК ДокументЗаказПоставщику
ЛЕВОЕ СОЕДИНЕНИЕ РегистрНакопления.РасчетыСПоставщиками.ОстаткиИОбороты КАК РасчетыСПоставщикамиОстатки
ПО ДокументЗаказПоставщику.Ссылка = РасчетыСПоставщикамиОстатки.ЗаказПоставщику

На SQL сервер уходит примерно такой запрос:

SELECT TOP 45 T1._idrref, 
              T1._marked, 
              T1._number, 
              T1._date_time, 
              T1._posted
FROM
	dbo._document231 T1 WITH(nolock) 
		LEFT OUTER JOIN 
		(
		SELECT 
			T4.fld12108_type  AS Fld12108_TYPE, 
            T4.fld12108_rtref AS Fld12108_RTRef, 
            T4.fld12108_rrref  AS Fld12108_RRRef 
            Cast(Sum(T4.fld12112receipt_) AS NUMERIC(27, 2)) AS Fld12112Receipt_,
			Cast(Sum(T4.fld12112expense_) AS NUMERIC(27, 2)) AS Fld12112Expense_
		FROM
			dbo._accumrg12106 T4 WITH(nolock)
		GROUP BY
			T4.fld12108_type,
            T4.fld12108_rtref,
            T4.fld12108_rrref
		) T3 ON 
			( 0x08 = T3.fld12108_type 
				AND 0x000000E7 = T3.fld12108_rtref 
				AND T1._idrref = T3.fld12108_rrref )  


Ниже - один узел плана запроса. В нем появилось условие [T5].[_Fld12108_RRRef]=[TGNUT11].[dbo].[_Document231].[_IDRRef] as [T1].[_IDRRef], которого нет в исходном тексте.

OBJECT:([TGNUT11].[dbo].[_AccumRg12106].[_AccumRg12106_ByDims12120_RTRN] AS [T5]), 
SEEK:(
[T5].[_Fld601]=[@P82] AND -- в этой базе всегда 0 (ноль)
[T5].[_Fld12108_TYPE]=0x08 AND 
[T5].[_Fld12108_RTRef]=0x000000E7 AND 
[T5].[_Fld12108_RRRef]=[TGNUT11].[dbo].[_Document231].[_IDRRef] as [T1].[_IDRRef]
) ORDERED FORWARD 


PS. В Postgres такого не происходит и, как следствие, запрос выполняется от 5 до 10 раз дольше на этой базе.
3 апр 19, 07:02    [21850817]     Ответить | Цитировать Сообщить модератору
 Re: Упреждающая или неявная фильтрация LEFT JOIN  [new]
aleks222
Member

Откуда:
Сообщений: 952
Что может сделать сервер с запросом, написанным больным сознанием?

ЗАЧЕМ?!! там LEFT JOIN?
ГДЕ?!! в запросе [T5]?
3 апр 19, 07:15    [21850821]     Ответить | Цитировать Сообщить модератору
 Re: Упреждающая или неявная фильтрация LEFT JOIN  [new]
alexeyvg
Member

Откуда: Moscow
Сообщений: 31355
nicxxx
На SQL сервер уходит примерно такой запрос:
Надо не "примерно", а скопировать.
nicxxx
В нем появилось условие [T5].[_Fld12108_RRRef]=[TGNUT11].[dbo].[_Document231].[_IDRRef] as [T1].[_IDRRef], которого нет в исходном тексте.
Скорее всего, это условие "AND T1._idrref = T3.fld12108_rrref", но это только предположение.

Лучше выложите в формате sqlplan, тогда всё прояснится.
3 апр 19, 13:49    [21851275]     Ответить | Цитировать Сообщить модератору
 Re: Упреждающая или неявная фильтрация LEFT JOIN  [new]
alexeyvg
Member

Откуда: Moscow
Сообщений: 31355
nicxxx
На SQL сервер уходит примерно такой запрос:
И зачем в запросе LEFT JOIN, если ни одного поля от джойна не используется???
3 апр 19, 13:50    [21851276]     Ответить | Цитировать Сообщить модератору
 Re: Упреждающая или неявная фильтрация LEFT JOIN  [new]
nicxxx
Member

Откуда: Москва
Сообщений: 58
автор
И зачем в запросе LEFT JOIN, если ни одного поля от джойна не используется???

Там все используется. Я лишь выделил ключевой момент, непосредственно относящийся к вопросу.
автор
Надо не "примерно", а скопировать.

Вам действительно интересно читать 1500 строк запроса? Не имея под рукой исходной БД.
автор
Скорее всего, это условие "AND T1._idrref = T3.fld12108_rrref", но это только предположение.

Да, так может показаться на первый взгляд, но это условие соединения, а не выборки из Т3. Логично предположить, что MSSQL знает, что будет происходить с Т3 и заранее ограничивает выборку из нее только строками, входящими в Т1. Однако явно в исходном запросе не указано.
В Postgres такого условия не появляется.
Файлы приложил, все в одном архиве. В плане MSSQL можно посмотреть node 49.

К сообщению приложен файл (PG_QUERIES.zip - 137Kb) cкачать
4 апр 19, 05:34    [21851953]     Ответить | Цитировать Сообщить модератору
 Re: Упреждающая или неявная фильтрация LEFT JOIN  [new]
aleks222
Member

Откуда:
Сообщений: 952
nicxxx
Вам действительно интересно читать 1500 строк запроса? Не имея под рукой исходной БД.

Шо тут поделаешь? Телепузики нонича повывелись.

ЗЫ. Читать я, канешно, ничего не буду. Занафига мне это.
Но совет бесплатный дам: вместо изучения громадья планов запроса - напиши этот запрос "по-человечески".

ЗЗЫ. Ну и ежели постгресс тебя устраивает - зачем ты сюда приперся?
4 апр 19, 06:00    [21851954]     Ответить | Цитировать Сообщить модератору
 Re: Упреждающая или неявная фильтрация LEFT JOIN  [new]
invm
Member

Откуда: Москва
Сообщений: 9345
nicxxx,

Философствования на тему какой хороший PG и плохой MSSQL - бесполезная трата времени.
Лучше поразмышляйте над статистикой ожиданий. Ее можно посмотреть в свойствах корневого элемента плана.
4 апр 19, 10:04    [21852118]     Ответить | Цитировать Сообщить модератору
 Re: Упреждающая или неявная фильтрация LEFT JOIN  [new]
vi0
Member

Откуда:
Сообщений: 231
nicxxx
Ниже - один узел плана запроса. В нем появилось условие [T5].[_Fld12108_RRRef]=[TGNUT11].[dbo].[_Document231].[_IDRRef] as [T1].[_IDRRef], которого нет в исходном тексте.

Это не оно разве: T1._idrref = T3.fld12108_rrref
?
4 апр 19, 10:06    [21852124]     Ответить | Цитировать Сообщить модератору
 Re: Упреждающая или неявная фильтрация LEFT JOIN  [new]
vi0
Member

Откуда:
Сообщений: 231
nicxxx
PS. В Postgres такого не происходит и, как следствие, запрос выполняется от 5 до 10 раз дольше на этой базе.

Почему ты решил что причина медленного выполнения в sql именно в этой строчке?
4 апр 19, 10:07    [21852127]     Ответить | Цитировать Сообщить модератору
 Re: Упреждающая или неявная фильтрация LEFT JOIN  [new]
nicxxx
Member

Откуда: Москва
Сообщений: 58
vi0,

Потому что Postgres выбирает 220 000 строк, а MSSQL - 34.

К сообщению приложен файл. Размер - 22Kb
4 апр 19, 13:29    [21852583]     Ответить | Цитировать Сообщить модератору
 Re: Упреждающая или неявная фильтрация LEFT JOIN  [new]
alexeyvg
Member

Откуда: Moscow
Сообщений: 31355
nicxxx
автор
Скорее всего, это условие "AND T1._idrref = T3.fld12108_rrref", но это только предположение.

Да, так может показаться на первый взгляд, но это условие соединения, а не выборки из Т3. Логично предположить, что MSSQL знает, что будет происходить с Т3 и заранее ограничивает выборку из нее только строками, входящими в Т1. Однако явно в исходном запросе не указано.
Ну да, вот в тексте запроса видно, что это T1._idrref = T3.fld12108_rrref.

"Условие соединения" тождественно "условие выборки". Что такое соединение, как не выборка записей, удовлетворяющих условию соединения?

MSSQL как то преобразует запрос, да.
nicxxx
В Postgres такого условия не появляется.
Ну, они по разному преобразуют.
В данном случае MSSQL ошибся, и запрос стал тормозить.
Можно попробовать обновить статистику, поставить хинты, переписать запрос.
4 апр 19, 21:40    [21853279]     Ответить | Цитировать Сообщить модератору
 Re: Упреждающая или неявная фильтрация LEFT JOIN  [new]
invm
Member

Откуда: Москва
Сообщений: 9345
alexeyvg
В данном случае MSSQL ошибся, и запрос стал тормозить.
Можно попробовать обновить статистику, поставить хинты, переписать запрос.
Ну ТС же план показал. Актуальный. Там нечему тормозить.

Зато есть это:
CPUTime - 55, ElapsedTime - 1464
WaitStats: PAGEIOLATCH_SH - 1412

У него с дисковой проблемы или памяти мало. Или и то, и другое. А он все с PG сравнивает...
4 апр 19, 22:00    [21853290]     Ответить | Цитировать Сообщить модератору
 Re: Упреждающая или неявная фильтрация LEFT JOIN  [new]
alexeyvg
Member

Откуда: Moscow
Сообщений: 31355
invm
alexeyvg
В данном случае MSSQL ошибся, и запрос стал тормозить.
Можно попробовать обновить статистику, поставить хинты, переписать запрос.
Ну ТС же план показал. Актуальный. Там нечему тормозить.

Зато есть это:
CPUTime - 55, ElapsedTime - 1464
WaitStats: PAGEIOLATCH_SH - 1412

У него с дисковой проблемы или памяти мало. Или и то, и другое. А он все с PG сравнивает...
Да, я уж в детали не вникал, может, и не тормозит.
Потом, может, на Постгресе CPU у него 2 мс, а не 55?

Я в принципе написал о том, как к этому вопросу подходить:
1) Любая СУБД будет выполнять запрос не так, как он написан пользователем на SQL, просто потому, что SQL - функциональный язык.
2) Если запрос выполняется слишком медленно (что тоже надо суметь определить), нужно изучать конкретную причину, и пытаться её устранить. При этом выполнение "не так, как написано на SQL", разумеется, не является такой причиной.
5 апр 19, 00:48    [21853380]     Ответить | Цитировать Сообщить модератору
 Re: Упреждающая или неявная фильтрация LEFT JOIN  [new]
nicxxx
Member

Откуда: Москва
Сообщений: 58
alexeyvg,

Вы неверно прочитали мой первый пост. Тормозит не MSSQL, а Postgres. MSSQL не ошибся, он заранее исключил из присоединяемой таблицы лишние записи, которых нет в Т1. Результирующая выборка составляет в среднем около 100 строк. В отличие от него, Postgres такой фильтрации не делает, выбирает 200 000 строк.

автор
"Условие соединения" тождественно "условие выборки".
Это с какого момента? У меня не INNER JOIN. В присоединяемом запросе, который начинается здесь:
LEFT OUTER JOIN 
		(
		SELECT 

нет секции WHERE вообще. А в плане запрсое появился указанный ранее предикат
[T5].[_Fld12108_RRRef]=[TGNUT11].[dbo].[_Document231].[_IDRRef] as [T1].[_IDRRef]
5 апр 19, 05:47    [21853444]     Ответить | Цитировать Сообщить модератору
 Re: Упреждающая или неявная фильтрация LEFT JOIN  [new]
nicxxx
Member

Откуда: Москва
Сообщений: 58
invm,

автор
У него с дисковой проблемы или памяти мало. Или и то, и другое. А он все с PG сравнивает...

Проблемы с памятью или дисками тут роли не играют. Замеры с ноутбука, шпиндель 5400, SQL серверу отдано 4GB памяти.
Проблема в том, что Postgres ведет себя отличным от MSSQL образом. Прочитайте мой первый пост еще раз. Я прошу не запрос ускорять, а показать, где описано такое поведение MSSQL -
автор
Прошу помощи. Как называется поведение SQL сервера, описанное ниже, и где можно почитать об этом подробнее?
5 апр 19, 05:55    [21853447]     Ответить | Цитировать Сообщить модератору
 Re: Упреждающая или неявная фильтрация LEFT JOIN  [new]
nicxxx
Member

Откуда: Москва
Сообщений: 58
alexeyvg, вы какой-то софистикой занимаетесь.

2) Если запрос выполняется слишком медленно (что тоже надо суметь определить), нужно изучать конкретную причину, и пытаться её устранить. При этом выполнение "не так, как написано на SQL", разумеется, не является такой причиной.


Прочитайте мой первый пост еще раз. Я запустил запрос на PG и получил время 10 секунд. Сравнил с MSSQL - получил там 1 секунду. Посмотрел, чем отличаются планы, увидел что в присоединяемом подзапросе разное количество строк и нашел, почему так. Спросил, где можно почитать про это поведение оптимизатора MSSQL.
Про что, что запрос выполняется слишком медленно и как это устранить, я не писал.
5 апр 19, 06:01    [21853448]     Ответить | Цитировать Сообщить модератору
 Re: Упреждающая или неявная фильтрация LEFT JOIN  [new]
aleks222
Member

Откуда:
Сообщений: 952
nicxxx
Тормозит не MSSQL, а Postgres. MSSQL не ошибся, он заранее исключил из присоединяемой таблицы лишние записи, которых нет в Т1.


Аффтар просто глумится над здешними аборигенами.
Оказывается проблема в том, что MS SQL обскакал постгресс.
5 апр 19, 06:10    [21853449]     Ответить | Цитировать Сообщить модератору
 Re: Упреждающая или неявная фильтрация LEFT JOIN  [new]
Павел Воронцов
Member

Откуда: Новосибирск
Сообщений: 2384
Блог
nicxxx
автор
Прошу помощи. Как называется поведение SQL сервера, описанное ниже, и где можно почитать об этом подробнее?


Это называется "проталкивание предиката" (predicate pushdown) У postgre тоже что-то такое есть, копайте.
5 апр 19, 06:42    [21853455]     Ответить | Цитировать Сообщить модератору
 Re: Упреждающая или неявная фильтрация LEFT JOIN  [new]
nicxxx
Member

Откуда: Москва
Сообщений: 58
Павел Воронцов
nicxxx
пропущено...


Это называется "проталкивание предиката" (predicate pushdown) У postgre тоже что-то такое есть, копайте.

Большое спасибо.
5 апр 19, 07:22    [21853465]     Ответить | Цитировать Сообщить модератору
 Re: Упреждающая или неявная фильтрация LEFT JOIN  [new]
alexeyvg
Member

Откуда: Moscow
Сообщений: 31355
nicxxx
Вы неверно прочитали мой первый пост. Тормозит не MSSQL, а Postgres.
А, ну ок, значит, он всё правильно сделал.

nicxxx
Прочитайте мой первый пост еще раз. Я запустил запрос на PG и получил время 10 секунд. Сравнил с MSSQL - получил там 1 секунду. Посмотрел, чем отличаются планы, увидел что в присоединяемом подзапросе разное количество строк и нашел, почему так. Спросил, где можно почитать про это поведение оптимизатора MSSQL.
Про что, что запрос выполняется слишком медленно и как это устранить, я не писал.
Ну, я ваш пост понял по другому: "MSSQL извратил мой запрос, выполняет его неправильно, поэтому там медленнее. Как мне сделать так, что бы было как в Постгресс?"
5 апр 19, 09:44    [21853585]     Ответить | Цитировать Сообщить модератору
 Re: Упреждающая или неявная фильтрация LEFT JOIN  [new]
StarikNavy
Member

Откуда: Москва
Сообщений: 2394
alexeyvg
]Ну, я ваш пост понял по другому: "MSSQL извратил мой запрос, выполняет его неправильно, поэтому там медленнее. Как мне сделать так, что бы было как в Постгресс?"

тут все именно так и поняли тс))
5 апр 19, 09:59    [21853605]     Ответить | Цитировать Сообщить модератору
 Re: Упреждающая или неявная фильтрация LEFT JOIN  [new]
alexeyvg
Member

Откуда: Moscow
Сообщений: 31355
StarikNavy
alexeyvg
]Ну, я ваш пост понял по другому: "MSSQL извратил мой запрос, выполняет его неправильно, поэтому там медленнее. Как мне сделать так, что бы было как в Постгресс?"

тут все именно так и поняли тс))
Ага, я вижу по комментариям.
5 апр 19, 10:00    [21853606]     Ответить | Цитировать Сообщить модератору
 Re: Упреждающая или неявная фильтрация LEFT JOIN  [new]
alexeyvg
Member

Откуда: Moscow
Сообщений: 31355
nicxxx
Спросил, где можно почитать про это поведение оптимизатора MSSQL.
Помню, что он всегда так делал; в общем, это же очевидное поведение какого-никакого оптимизатора. Вот, выше написали, что и у Постгреса так же. Но как называется, не знал :-(
5 апр 19, 10:06    [21853613]     Ответить | Цитировать Сообщить модератору
 Re: Упреждающая или неявная фильтрация LEFT JOIN  [new]
nicxxx
Member

Откуда: Москва
Сообщений: 58
alexeyvg,

А вот и не так же у Постгресса и это удручает.
5 апр 19, 11:14    [21853715]     Ответить | Цитировать Сообщить модератору
 Re: Упреждающая или неявная фильтрация LEFT JOIN  [new]
aleks222
Member

Откуда:
Сообщений: 952
nicxxx
alexeyvg,
А вот и не так же у Постгресса и это удручает.

Аффторь, очевидно, не догадывается, что "протолкнуть предикат" можно и врукопашную.
Тупо написав правильно запрос.


ЗЫ. Любая "оптимизация" - это внутреннее дело конкретной реализации сервера.
Оракал, говорят, тоже кое-что делает быстрее MS SQL.
Се ля ви.
5 апр 19, 13:15    [21853960]     Ответить | Цитировать Сообщить модератору
Топик располагается на нескольких страницах: [1] 2   вперед  Ctrl      все
Все форумы / Microsoft SQL Server Ответить