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

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

Мне нужна Ваша помощь.
Занялся оптимизацией кода и не могу придумать альтернативу для замены коррелированного подзапроса.

Задача выглядит просто:
Есть таблица с историей начисления бонусных очков покупателю.
Необходимо достать из таблицы количество начисленных бонусов за 5 предыдущих дней, не считая текущего дня.

Как реализовать эту задачу без использования подзапроса?

Спасибо.

Ниже представлен скрипт.

/*Таблица с историей начисления бонусов*/
create table #Bonuses(
	date_b smalldatetime not null
	, buyer varchar(50) not null
	, sum_b int not null
)

/*Заполняем таблицу данными*/
insert into #Bonuses(date_b, buyer, sum_b)
values('20190901', 'Покупатель1', 10)
	, ('20190903', 'Покупатель1', 5)
	, ('20190904', 'Покупатель1', 12)
	, ('20190905', 'Покупатель1', 10)
	, ('20190907', 'Покупатель1', 13)
	, ('20190909', 'Покупатель1', 7)
	, ('20190910', 'Покупатель1', 25)
	, ('20190925', 'Покупатель1', 5)
	, ('20190927', 'Покупатель1', 3)
	, ('20190930', 'Покупатель1', 6)

	, ('20190901', 'Покупатель2', 17)
	, ('20190902', 'Покупатель2', 3)
	, ('20190905', 'Покупатель2', 3)
	, ('20190910', 'Покупатель2', 15)
	, ('20190926', 'Покупатель2', 7)
	, ('20190927', 'Покупатель2', 10)
	, ('20190928', 'Покупатель2', 3)

	, ('20190905', 'Покупатель3', 10)
	, ('20190910', 'Покупатель3', 1)
	, ('20190917', 'Покупатель3', 23)
	, ('20190918', 'Покупатель3', 7)
	, ('20190929', 'Покупатель3', 9)

/*Запрос достает из таблицы количество начисленных бонусов за 5 предыдущих дней, не считая текущего дня*/
select date_b
	, buyer
	, (select sum(sum_b) from #Bonuses 
		where buyer = b.buyer 
			and date_b between dateadd(day, -5, b.date_b) 
			and b.date_b and date_b < b.date_b
	) as sum_b
from #Bonuses b

drop table #Bonuses
30 сен 19, 16:51    [21982986]     Ответить | Цитировать Сообщить модератору
 Re: Оптимизация. Замена коррелированного подзапроса.  [new]
Гавриленко Сергей Алексеевич
Member

Откуда: Moscow
Сообщений: 36905
С чего вы взяли, что проблема в корреклированном подзапросе, а не, к примеру, в неправильной или отсутствующей индексации, или вовсе в архитектуре?
30 сен 19, 17:02    [21982998]     Ответить | Цитировать Сообщить модератору
 Re: Оптимизация. Замена коррелированного подзапроса.  [new]
TaPaK
Member

Откуда: Kiev
Сообщений: 6801
YuriySu,

ну например если растянуть ваши даты до ежедневного, то накопительной суммой на ROWS BETWEEN 5 PRECEDING AND CURRENT ROW
30 сен 19, 17:03    [21982999]     Ответить | Цитировать Сообщить модератору
 Re: Оптимизация. Замена коррелированного подзапроса.  [new]
YuriySu
Member

Откуда:
Сообщений: 19
Гавриленко Сергей Алексеевич
С чего вы взяли, что проблема в корреклированном подзапросе, а не, к примеру, в неправильной или отсутствующей индексации, или вовсе в архитектуре?


Здравствуйте, Гавриленко Сергей Алексеевич.

Я вижу оптимизацию так:
1. Оптимизировать структуру БД под текущую задачу (что-то нормализировать, а что-то напротив денормализировать)
2. Составить оптимальные, с точки зрения оптимизатора SQL, запросы.
3. Добавить необходимые индексы.

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

Я думал над вариантом перенести этот подзапрос в JOIN, но не могу придумать как.

Возможно нужно что-то сделать по 1-му пункту. Но что?

Подскажите, пожалуйста, если у Вас есть какие-то мысли по этому поводу.

Спасибо.
30 сен 19, 17:25    [21983025]     Ответить | Цитировать Сообщить модератору
 Re: Оптимизация. Замена коррелированного подзапроса.  [new]
YuriySu
Member

Откуда:
Сообщений: 19
TaPaK
YuriySu,

ну например если растянуть ваши даты до ежедневного, то накопительной суммой на ROWS BETWEEN 5 PRECEDING AND CURRENT ROW


Спасибо, TaPaK.

Похоже это очень хороший вариант. Супер.

Скажите, есть ли еще какие-то варианты?

Спасибо.
30 сен 19, 17:27    [21983031]     Ответить | Цитировать Сообщить модератору
 Re: Оптимизация. Замена коррелированного подзапроса.  [new]
msLex
Member

Откуда:
Сообщений: 7998
YuriySu
На данный момент я работаю над 2-м пунктом.
И использование кореллированных подзапросов желательно избегать, т.к. этот запрос отрабатывает для каждой строки.

Вы это в плане увидели или просто так решили?
30 сен 19, 17:29    [21983033]     Ответить | Цитировать Сообщить модератору
 Re: Оптимизация. Замена коррелированного подзапроса.  [new]
TaPaK
Member

Откуда: Kiev
Сообщений: 6801
YuriySu,

ну просто в jOIN

select	
	b.date_b,
	b.buyer,
	SUM(ISNULL(a.sum_b,0))
from 
	#Bonuses b
LEFT JOIN 
	#Bonuses a
ON
	a.buyer = b.buyer 
and a.date_b between dateadd(day, -5, b.date_b)  AND b.date_b
and a.date_b < b.date_b
GROUP BY 
	b.date_b,
	b.buyer
30 сен 19, 17:30    [21983035]     Ответить | Цитировать Сообщить модератору
 Re: Оптимизация. Замена коррелированного подзапроса.  [new]
YuriySu
Member

Откуда:
Сообщений: 19
msLex
YuriySu
На данный момент я работаю над 2-м пунктом.
И использование кореллированных подзапросов желательно избегать, т.к. этот запрос отрабатывает для каждой строки.

Вы это в плане увидели или просто так решили?


Здравствуйте, msLex.

Не обязательно заглядывать в план запроса чтобы понять то, что запрос, который записан в перечне выводимых полей (SELECT) будет выполняться для каждой строки. И когда таблица содержит не 30, а 1 млн. записей, то это уже большая проблема.

Разве я заблуждаюсь?

Спасибо.
30 сен 19, 18:40    [21983116]     Ответить | Цитировать Сообщить модератору
 Re: Оптимизация. Замена коррелированного подзапроса.  [new]
YuriySu
Member

Откуда:
Сообщений: 19
TaPaK
YuriySu,

ну просто в jOIN

select	
	b.date_b,
	b.buyer,
	SUM(ISNULL(a.sum_b,0))
from 
	#Bonuses b
LEFT JOIN 
	#Bonuses a
ON
	a.buyer = b.buyer 
and a.date_b between dateadd(day, -5, b.date_b)  AND b.date_b
and a.date_b < b.date_b
GROUP BY 
	b.date_b,
	b.buyer


TaPaK, как всегда на высоте.

Спасибо огромное.
30 сен 19, 18:44    [21983121]     Ответить | Цитировать Сообщить модератору
 Re: Оптимизация. Замена коррелированного подзапроса.  [new]
Гавриленко Сергей Алексеевич
Member

Откуда: Moscow
Сообщений: 36905
YuriySu
Не обязательно заглядывать в план запроса
"Не читал, но осуждаю." (с)
YuriySu
Разве я заблуждаюсь?
Само собой.
30 сен 19, 18:50    [21983129]     Ответить | Цитировать Сообщить модератору
 Re: Оптимизация. Замена коррелированного подзапроса.  [new]
YuriySu
Member

Откуда:
Сообщений: 19
Гавриленко Сергей Алексеевич
YuriySu
Не обязательно заглядывать в план запроса
"Не читал, но осуждаю." (с)
YuriySu
Разве я заблуждаюсь?
Само собой.


Скажите пожалуйста, Гавриленко Сергей Алексеевич.

От Вас может исходить какой-то конструктив?
Я ведь не позиционирую себя как эксперта иначе я бы не задавал этих вопросов.

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

Спасибо.
30 сен 19, 18:59    [21983141]     Ответить | Цитировать Сообщить модератору
 Re: Оптимизация. Замена коррелированного подзапроса.  [new]
Гавриленко Сергей Алексеевич
Member

Откуда: Moscow
Сообщений: 36905
YuriySu,

Оптимизация запроса начинается с изучения его плана. Вы же считаете, что этот пункт не обязателен и сразу надо переписывать все коррелированные подзапросы. Какой еще конструктив до вас донести?
30 сен 19, 19:07    [21983158]     Ответить | Цитировать Сообщить модератору
 Re: Оптимизация. Замена коррелированного подзапроса.  [new]
YuriySu
Member

Откуда:
Сообщений: 19
Гавриленко Сергей Алексеевич
YuriySu,

Оптимизация запроса начинается с изучения его плана. Вы же считаете, что этот пункт не обязателен и сразу надо переписывать все коррелированные подзапросы. Какой еще конструктив до вас донести?


Еще раз, я не считаю, что план запросов - это что-то несущественное. Напротив - это самый важный инструмент для поиска узких мест.
Это я конечно же знаю))

Я прошу Вас написать мне почему SELECT-подзапросы не выполняются для каждой выводимой строки? Ведь Вы же утверждаете, что это не так. Мне интересно почему?

Извините, если я Вас неправильно понял.

Спасибо.
30 сен 19, 19:14    [21983164]     Ответить | Цитировать Сообщить модератору
 Re: Оптимизация. Замена коррелированного подзапроса.  [new]
Гавриленко Сергей Алексеевич
Member

Откуда: Moscow
Сообщений: 36905
YuriySu,

При выполнении запроса с коррелированными подзапросами, точно так же, как и с не коррелированными, никаких других запросов не выполняется. Можете запустить профайлер и убедиться в этом. Более того, одинаковые по логике запросы с коррелированным подзапросом и join'ом могут иметь одинаковый план. (Есть нюансы с функциями, но это явно не ваш случай.)

Поэтому ваш подход к оптимизации мне кажется весьма странным, как-будто он продиктован какими-то детскими травмами и суевериями, типа "уу, коррелированные подзапросы -- это же самое зло, как увидел, переделай".
30 сен 19, 19:27    [21983173]     Ответить | Цитировать Сообщить модератору
 Re: Оптимизация. Замена коррелированного подзапроса.  [new]
YuriySu
Member

Откуда:
Сообщений: 19
Гавриленко Сергей Алексеевич
YuriySu,

При выполнении запроса с коррелированными подзапросами, точно так же, как и с не коррелированными, никаких других запросов не выполняется. Можете запустить профайлер и убедиться в этом. Более того, одинаковые по логике запросы с коррелированным подзапросом и join'ом могут иметь одинаковый план. (Есть нюансы с функциями, но это явно не ваш случай.)

Поэтому ваш подход к оптимизации мне кажется весьма странным, как-будто он продиктован какими-то детскими травмами и суевериями, типа "уу, коррелированные подзапросы -- это же самое зло, как увидел, переделай".


Спасибо за Ваши замечания.

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

Я очень благодарен TaPaK за его решения. Они интересны с точки зрения альтернативных подходов.
Но если применять конкретно к моей задаче, то оба варианта:
первый - с оконными функциями и второй - с JOIN не подходят.

Дело в том, что все упирается в количество строк.
С вариантом "кореллированный подзапрос" количество обрабатываемых строк около 1 млн.
А с "оконными функциями" и с JOIN - число обрабатываемых строк разрастается до 300 млн.

И, соответственно, по стоимости и скорости выполнения вариант с "кореллированными подзапросами" выигрывает с уверенным отрывом.

Эхх...

Буду рад, если кто-нибудь предложит почитать грамотный материал по оптимизации запросов. Дело в том, что информации в интернете очень много и с чего начать изучать данную проблематику не совсем понятно.

Спасибо всем, огромное.
30 сен 19, 20:30    [21983213]     Ответить | Цитировать Сообщить модератору
 Re: Оптимизация. Замена коррелированного подзапроса.  [new]
invm
Member

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

Индекс нужен (buyer , b_date) include (b_sum) - тогда вариант с join будет выигрышней.
30 сен 19, 21:32    [21983275]     Ответить | Цитировать Сообщить модератору
 Re: Оптимизация. Замена коррелированного подзапроса.  [new]
PizzaPizza
Member

Откуда:
Сообщений: 350
YuriySu
Дело в том, что все упирается в количество строк.
С вариантом "кореллированный подзапрос" количество обрабатываемых строк около 1 млн.
А с "оконными функциями" и с JOIN - число обрабатываемых строк разрастается до 300 млн.


непонятно.

Запрос должен выбирать данные одного пользователя или всех сразу?
Если всех, то непонятно откуда разница в результатах в 1 / 300 млн строк.
Если одного, то тоже непонятно откуда разница в результатах в 1 / 300 млн строк.

В вашем запросе с одной единственной таблицей я лично не вижу возможности разницы между join и подзапросом.

Если вам надо вынимать одного пользователя из 300 миллионов записей, это одно. Если при этом у вас один пользователь генерит около 1 млн записей - это другое. Если же надо вынимать всех пользователей и там сотни миллионов записей, это трерье, и при аггрегациях sum(sum_b) онлайн нагрузка будет большая. Возможно лучше преаггрегировать эти данные, тем более, что у вас в условии "не считая текущего дня". Как вариант хранить ваши #Bonuses как простой лог #Bonuses_log, а при вставке делать +sum_b в таблицу #Bonuses (buyer, sum_b)
1 окт 19, 07:33    [21983423]     Ответить | Цитировать Сообщить модератору
Все форумы / Microsoft SQL Server Ответить