ReSQL.ru
     
powered by simpleCommunicator - 2.0.60     © 2026 Programmizd 02
Мобильная версия    Контакт    Правила    FAQ    Помощь
Целевая тема:
Создать новую тему:
Автор:
Закрыть
Цитировать
Доб. в избранное | Игнор. тему | Прикреп. тему | Пометить прочит. / непрочит. | Фильтр
Форумы / Microsoft SQL Server [игнор отключен] [закрыт для гостей] / Оптимизация. Замена коррелированного подзапроса.
17 сообщений из 17, страница 1 из 1
Оптимизация. Замена коррелированного подзапроса.
    #39869331
YuriySu
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
YuriySu
Гость
Привет всем.

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

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

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

Спасибо.

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

Код: sql
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
20.
21.
22.
23.
24.
25.
26.
27.
28.
29.
30.
31.
32.
33.
34.
35.
36.
37.
38.
39.
40.
41.
42.
43.
44.
45.
/*Таблица с историей начисления бонусов*/
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
...
Рейтинг: 0 / 0
30.09.2019, 16:51
| Ответить | Цитировать | Написать  
Оптимизация. Замена коррелированного подзапроса.
    #39869339
Гавриленко Сергей Алексеевич
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Гавриленко Сергей Алексеевич
Участник
С чего вы взяли, что проблема в корреклированном подзапросе, а не, к примеру, в неправильной или отсутствующей индексации, или вовсе в архитектуре?
...
Рейтинг: 0 / 0
30.09.2019, 17:02
| Ответить | Цитировать | Написать  
Оптимизация. Замена коррелированного подзапроса.
    #39869340
TaPaK
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
TaPaK
Участник
YuriySu,

ну например если растянуть ваши даты до ежедневного, то накопительной суммой на ROWS BETWEEN 5 PRECEDING AND CURRENT ROW
...
Рейтинг: 0 / 0
30.09.2019, 17:03
| Ответить | Цитировать | Написать  
Оптимизация. Замена коррелированного подзапроса.
    #39869353
YuriySu
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
YuriySu
Гость
Гавриленко Сергей АлексеевичС чего вы взяли, что проблема в корреклированном подзапросе, а не, к примеру, в неправильной или отсутствующей индексации, или вовсе в архитектуре?

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

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

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

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

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

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

Спасибо.
...
Рейтинг: 0 / 0
30.09.2019, 17:25
| Ответить | Цитировать | Написать  
Оптимизация. Замена коррелированного подзапроса.
    #39869357
YuriySu
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
YuriySu
Гость
TaPaKYuriySu,

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

Спасибо, TaPaK.

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

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

Спасибо.
...
Рейтинг: 0 / 0
30.09.2019, 17:27
| Ответить | Цитировать | Написать  
Оптимизация. Замена коррелированного подзапроса.
    #39869359
msLex
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
msLex
Участник
YuriySuНа данный момент я работаю над 2-м пунктом.
И использование кореллированных подзапросов желательно избегать, т.к. этот запрос отрабатывает для каждой строки.
Вы это в плане увидели или просто так решили?
...
Рейтинг: 0 / 0
30.09.2019, 17:29
| Ответить | Цитировать | Написать  
Оптимизация. Замена коррелированного подзапроса.
    #39869360
TaPaK
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
TaPaK
Участник
YuriySu,

ну просто в jOIN

Код: sql
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
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
...
Рейтинг: 0 / 0
30.09.2019, 17:30
| Ответить | Цитировать | Написать  
Оптимизация. Замена коррелированного подзапроса.
    #39869431
YuriySu
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
YuriySu
Гость
msLexYuriySuНа данный момент я работаю над 2-м пунктом.
И использование кореллированных подзапросов желательно избегать, т.к. этот запрос отрабатывает для каждой строки.
Вы это в плане увидели или просто так решили?

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

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

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

Спасибо.
...
Рейтинг: 0 / 0
30.09.2019, 18:40
| Ответить | Цитировать | Написать  
Оптимизация. Замена коррелированного подзапроса.
    #39869436
YuriySu
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
YuriySu
Гость
TaPaKYuriySu,

ну просто в jOIN

Код: sql
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
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, как всегда на высоте.

Спасибо огромное.
...
Рейтинг: 0 / 0
30.09.2019, 18:44
| Ответить | Цитировать | Написать  
Оптимизация. Замена коррелированного подзапроса.
    #39869443
Гавриленко Сергей Алексеевич
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Гавриленко Сергей Алексеевич
Участник
YuriySuНе обязательно заглядывать в план запроса"Не читал, но осуждаю." (с)
YuriySuРазве я заблуждаюсь?Само собой.
...
Рейтинг: 0 / 0
30.09.2019, 18:50
| Ответить | Цитировать | Написать  
Оптимизация. Замена коррелированного подзапроса.
    #39869454
YuriySu
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
YuriySu
Гость
Гавриленко Сергей АлексеевичYuriySuНе обязательно заглядывать в план запроса"Не читал, но осуждаю." (с)
YuriySuРазве я заблуждаюсь?Само собой.

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

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

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

Спасибо.
...
Рейтинг: 0 / 0
30.09.2019, 18:59
| Ответить | Цитировать | Написать  
Оптимизация. Замена коррелированного подзапроса.
    #39869464
Гавриленко Сергей Алексеевич
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Гавриленко Сергей Алексеевич
Участник
YuriySu,

Оптимизация запроса начинается с изучения его плана. Вы же считаете, что этот пункт не обязателен и сразу надо переписывать все коррелированные подзапросы. Какой еще конструктив до вас донести?
...
Рейтинг: 0 / 0
30.09.2019, 19:07
| Ответить | Цитировать | Написать  
Оптимизация. Замена коррелированного подзапроса.
    #39869469
YuriySu
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
YuriySu
Гость
Гавриленко Сергей АлексеевичYuriySu,

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

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

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

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

Спасибо.
...
Рейтинг: 0 / 0
30.09.2019, 19:14
| Ответить | Цитировать | Написать  
Оптимизация. Замена коррелированного подзапроса.
    #39869476
Гавриленко Сергей Алексеевич
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Гавриленко Сергей Алексеевич
Участник
YuriySu,

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

Поэтому ваш подход к оптимизации мне кажется весьма странным, как-будто он продиктован какими-то детскими травмами и суевериями, типа "уу, коррелированные подзапросы -- это же самое зло, как увидел, переделай".
...
Рейтинг: 0 / 0
30.09.2019, 19:27
| Ответить | Цитировать | Написать  
Оптимизация. Замена коррелированного подзапроса.
    #39869508
YuriySu
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
YuriySu
Гость
Гавриленко Сергей АлексеевичYuriySu,

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

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

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

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

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

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

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

Эхх...

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

Спасибо всем, огромное.
...
Рейтинг: 0 / 0
30.09.2019, 20:30
| Ответить | Цитировать | Написать  
Оптимизация. Замена коррелированного подзапроса.
    #39869558
invm
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
invm
Участник
YuriySu,

Индекс нужен (buyer , b_date) include (b_sum) - тогда вариант с join будет выигрышней.
...
Рейтинг: 0 / 0
30.09.2019, 21:32
| Ответить | Цитировать | Написать  
Оптимизация. Замена коррелированного подзапроса.
    #39869665
PizzaPizza
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
PizzaPizza
Участник
YuriySuДело в том, что все упирается в количество строк.
С вариантом "кореллированный подзапрос" количество обрабатываемых строк около 1 млн.
А с "оконными функциями" и с JOIN - число обрабатываемых строк разрастается до 300 млн.


непонятно.

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

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

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


Просмотр
0 / 0
Close
Debug Console [Select Text]