Гость
Войти | Профиль | Очистить
Целевая тема:
Создать новую тему:
Автор:
Действия ...
Форумы / Microsoft SQL Server [игнор отключен] [закрыт для гостей] / оптимизация запроса / 24 сообщений из 24, страница 1 из 1
06.11.2018, 18:32
    #39728773
Makvetsyan Artur
Makvetsyan Artur
Гость
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
оптимизация запроса
Доброе время суток

Борюсь с одним запросом, в данный момент выполняется 10 сек, мне нужно добиться высокой производительности.

Есть три компании(А,Б,В) и у каждого свое наличие на складах (Details). У каждого свои разные наценки(Disconts), в том числе наценка на стоимость (SurchargeCost).

Компания В хочет видеть свое наличие и наличие у своих партнеров А и В. Соответственно нужно подготовить продажную цену каждой позиции для компании В, и плюс добавить свои наценки. Собственно пришлось применить табличные функции и union

Вот примерно сам запрос
Код: 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.
CREATE FUNCTION [Detail].[fnSearchSparePartsFromSource]
(
	@applicationId uniqueidentifier,
	@profileId uniqueidentifier,
	@supplierId int
)
returns table
as 
return 
(
	select
		--										[Supplier Settings]
		@supplierId								[SupplierId],
		setting.TimeDeliveryMin					[SupplierTimeMin],
		setting.TimeDeliveryMax					[SupplierTimeMax],
		--										[Position Info]
		det.ArticleId							[ArticleId],
		det.BrandId								[BrandId],
		det.Description							[Description],
		det.Count								[Count],
		--										[CostInfo]
		det.Cost * ISNULL(setting.Discount, 0) / 100
				 * (1 + ISNULL(cost.Surcharge, 0) / 100)	[Cost]
		from Detail det
		inner join Supplier sup on sup.Id = det.SupplierId

		inner join Brand bra on bra.Id = det.BrandId

		inner join Settings setting on setting.ApplicationId = @applicationId and setting.SupplierId = sup.Id
		-- Left
		left join SurchargeProfile surProf on surProf.SettingsId = setting.Id and surProf.ProfileId = @profileId

		left join SurchargeCost cost on cost.SurchargeProfileId = surProf.Id 
												and cost.RangeStart <= (det.Cost * ISNULL(setting.Discount, 0) / 100) 
												and cost.RangeEnd > (det.Cost * ISNULL(setting.Discount, 0) / 100)
		where 
			setting.IsDeleted = 0 and
			setting.IsEnable = 1 and 
			ISNULL(surProf.IsEnabled, 1) = 1
				
)



и применение
Код: 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.
46.
47.
48.
49.
50.
51.
52.
53.
54.
55.
56.
57.
58.
59.
60.
61.
62.
63.
64.
65.
66.
67.
68.
69.
70.
71.
72.
73.
declare 
	@categoryId tinyint = 4,
	@applicationId uniqueidentifier='B',
	@profileId uniqueidentifier='1'

SELECT 
		--										[Good Info]
		good.Id							        [Id],
		good.Article							[Article],		
		good.Brand                              [Brand],
		good.ArticleId							[ArticleId],
		--										[Supplier Settings]
										 detail.[SupplierCode],
										 detail.[SupplierColor],
										 detail.[SupplierId],
										 detail.[SupplierName],
										 detail.[SupplierTimeMax],
		--										[Position Info]
										 detail.[BrandId],
										 detail.[Description],
										 detail.[Quantity],
										 detail.[SupplierTimeMin], 
										 detail.[CostSale] [CostSale]
FROM Goods good 
inner join 
	(select
			--																	[Supplier Settings]
		setting.Code															[SupplierCode],
		setting.Color															[SupplierColor],
		setting.SupplierId														[SupplierId],
		setting.Name															[SupplierName],
		isnull(setting.TimeDeliveryMin, 0) + isnull(det.SupplierTimeMin, 0)		[SupplierTimeMin],
		isnull(setting.TimeDeliveryMax, 0) + isnull(det.SupplierTimeMax, 0)		[SupplierTimeMax],
		--																		[Position Info]
		det.ArticleId															[ArticleId],
		bra.Name																[Brand],
		bra.Id																	[BrandId],
		det.Description															[Description],
		det.Count																[Quantity],
		--																		[Cost Info]

		det.Cost * ISNULL(setting.Discount, 0) / 100
				 * (1 + ISNULL(cost.Surcharge, 0) / 100)						[CostSale]		
				

	FROM( 
				select * from dbo.[fnSearchSparePartsFromSource]('A')
					UNION
				select * from dbo.[fnSearchSparePartsFromSource]('B')
					UNION
				select* from Detail
				 ) det
      inner join Supplier sup on sup.Id = det.SupplierId

	  inner join Brand bra on bra.Id = det.BrandId

	  inner join Settings setting on setting.ApplicationId = @applicationId 
												and setting.SupplierId = sup.Id
												and setting.IsDeleted = 0 
												and setting.IsEnable = 1
	  -- Left
	  left join SurchargeProfile surProf on surProf.SettingsId = setting.Id 
														   and surProf.ProfileId = @profileId

	  left join SurchargeCost cost on cost.SurchargeProfileId = surProf.Id 
												     and cost.RangeStart <= (det.Cost * ISNULL(setting.Discount, 0) / 100) 
													and cost.RangeEnd > (det.Cost * ISNULL(setting.Discount, 0) / 100)

	where ISNULL(surProf.IsEnabled, 1) = 1
	) detail on good.ArticleId = detail.ArticleId 
where good.CategoryId = @categoryId and good.ArticleId is not null 

--DBCC FREEPROCCACHE



Стоимость объединения discinct sort очень дорогая, ребята сможете подсказать другие варианты?
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
06.11.2018, 18:36
    #39728776
Владислав Колосов
Владислав Колосов
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
оптимизация запроса
Makvetsyan Artur,

discinct sort вызывает union, уверены, что нельзя использовать union all?
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
06.11.2018, 19:16
    #39728817
PizzaPizza
PizzaPizza
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
оптимизация запроса
Makvetsyan Artur,

Очень большие таблицы? Может быть вьюшку сделать по всем компаниям с уже посчитанными ценами (SCHEMABINDING?) и из неё выбирать?
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
06.11.2018, 23:31
    #39728928
Makvetsyan Artur
Makvetsyan Artur
Гость
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
оптимизация запроса
Владислав Колосов, пробовал, да и по идею union all должен шустрее работать, но работает больше 5 мин
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
06.11.2018, 23:52
    #39728938
Makvetsyan Artur
Makvetsyan Artur
Гость
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
оптимизация запроса
PizzaPizza,
Не знаю как это поможет, мне нужно получить готовую цену от компании А и Б со своими наценками, а потом от этой цены прибавить наценки В. Грубо говоря А и Б являются поставщиками В
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
07.11.2018, 00:31
    #39728951
PizzaPizza
PizzaPizza
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
оптимизация запроса
Makvetsyan Artur,

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

Вы джойните таблицу Supplier в функции, которая выполняется два раза, и потом еще раз джойните в основном запросе. Я бы сравнил планы выполнения ваших запросов, т.к. возможно, что, если не использовать функции, оптимизатор сможет уменьшить количество проходов по таблицам.
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
07.11.2018, 01:39
    #39728964
Makvetsyan Artur
Makvetsyan Artur
Гость
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
оптимизация запроса
PizzaPizza,
я вас понял кажется, попробую
наверное лучше покажу пример что мне нужно
у компании А в таблице Details:
Артикул|Кол-во|цена
333305 | 4 | 400
333304 | 5 | 500

Б :
333306 | 4 | 700
333307 | 5 | 1500

В:
sp1047 | 4 | 1400
oc90 | 5 | 300

(Наценка на стоимость учитывается после общей наценки)
Общая наценки | наценка на стоимость
А - 10% | от 500 - 1000 руб - 5 %
Б - 20% | от 1000 - 1500 руб - 5 %
В - 30% | от 1500 - 2000 руб - 5 %

Результатом должен служить предложения от компании В, у которого есть наличие своего магазина и наличие его поставщиков А и В:
цена закупки от А для В
333305 | 4 | 400 * 10% = 440
333304 | 5 | 500 * 10% * 5% = 577.5

цена закупки от Б для В:
333306 | 4 | 700 * 20% = 840
333307 | 5 | 1500 * 20% * 5% = 1890

Итого должно быть на выходе В:
333305 | 4 | 440 * 30% = 572
333304 | 5 | 577.5 * 30% = 750.75
333306 | 4 | 840 * 30% = 1092
333307 | 5 | 1890 * 30% * 5% = 2579.85
sp1047 | 4 | 1400 * 30% * 5%= 1911
oc90 | 5 | 300 * 30% = 390

Вот собственно чего я хотел добиться
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
07.11.2018, 03:05
    #39728967
PizzaPizza
PizzaPizza
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
оптимизация запроса
Makvetsyan Artur,

я бы посмотрел сюда внимательно, тк не очень понимаю что тут имелось ввиду:

Код: plaintext
1.
2.
3.
4.
select * from dbo.[fnSearchSparePartsFromSource]('A')
					UNION
				select * from dbo.[fnSearchSparePartsFromSource]('B')
					UNION
				select* from Detail

у вас функции делают выборки из таблицы Detail с фильтром по supplierId, и потом еще раз вся таблица Detail присоединяется к этим выборкам.
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
07.11.2018, 06:08
    #39728982
aleks222
aleks222
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
оптимизация запроса
PizzaPizzaMakvetsyan Artur,

я бы посмотрел сюда внимательно, тк не очень понимаю что тут имелось ввиду:

Код: plaintext
1.
2.
3.
4.
select * from dbo.[fnSearchSparePartsFromSource]('A')
					UNION
				select * from dbo.[fnSearchSparePartsFromSource]('B')
					UNION
				select* from Detail

у вас функции делают выборки из таблицы Detail с фильтром по supplierId, и потом еще раз вся таблица Detail присоединяется к этим выборкам.

Это шоб быстрее работало. Очевидно ж.

ЗЫ. Тредстартер чудак на известную букву.
5 (пять) раз выполняет одно и то же соединение и еще жалуется.

1. Выкинуть функцию.
2. Написать ОДИН раз соединение.
3. Раз в пять должно быть быстрее.
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
07.11.2018, 07:49
    #39728999
aleks222
aleks222
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
оптимизация запроса
Makvetsyan Artur
Код: sql
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
CREATE FUNCTION [Detail].[fnSearchSparePartsFromSource]
(
	@applicationId uniqueidentifier,
	@profileId uniqueidentifier,
	@supplierId int
)

-- я ужо молчу, шо тредстартер брешет яко сивый мерин
-- или путается в показаниях?

	FROM( 
				select * from dbo.[fnSearchSparePartsFromSource]('A')
					UNION
				select * from dbo.[fnSearchSparePartsFromSource]('B')
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
07.11.2018, 08:21
    #39729005
Makvetsyan Artur
Makvetsyan Artur
Гость
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
оптимизация запроса
aleks222,

Да извините, это я как бы примерный запрос написал, чтоб понять суть. Прошу это не обращать внимания на кол-во параметров в функции. Насчет представления как раз попробую, а потом объединить.
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
07.11.2018, 08:24
    #39729006
Makvetsyan Artur
Makvetsyan Artur
Гость
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
оптимизация запроса
aleks222ЗЫ. Тредстартер чудак на известную букву.
5 (пять) раз выполняет одно и то же соединение и еще жалуется.

1. Выкинуть функцию.
2. Написать ОДИН раз соединение.
3. Раз в пять должно быть быстрее.

Это конечно хорошо один раз соединение. Не знаю может я туплю или вы таки не поняли суть задачи
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
07.11.2018, 10:28
    #39729087
Maxx
Maxx
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
оптимизация запроса
мдя.. ТС..ето шутка юмора ? Какая оптимизация если пример отродясь не рабочий


Код: sql
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
 declare 
	@categoryId tinyint = 4,
	@applicationId uniqueidentifier='B',
	@profileId uniqueidentifier='1'
	select 
	@categoryId ,
	@applicationId ,
	@profileId 

Msg 8169, Level 16, State 2, Line 1
Conversion failed when converting from a character string to uniqueidentifier.
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
07.11.2018, 10:57
    #39729112
Makvetsyan Artur
Makvetsyan Artur
Гость
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
оптимизация запроса
Maxx, Ребята я извиняюсь что с ошибкой опубликовал код, но суть не в этом. Мне просто нужна подсказка, варианты объединения или каких-то хитростей. Я же писал код выполняется 10 сек, и код рабочий, а тут немного скорректировал, показать только саму структуру запроса
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
07.11.2018, 11:04
    #39729119
Maxx
Maxx
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
оптимизация запроса
Makvetsyan ArturMaxx, Ребята я извиняюсь что с ошибкой опубликовал код, но суть не в этом. Мне просто нужна подсказка, варианты объединения или каких-то хитростей. Я же писал код выполняется 10 сек, и код рабочий, а тут немного скорректировал, показать только саму структуру запроса
Да Вам же уже ответили

Код: sql
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
[src]	select
		--										
		det.SupplierId							[SupplierId],
        ---
		from Detail det
		inner join Supplier sup on sup.Id = det.SupplierId

		inner join Brand bra on bra.Id = det.BrandId

		inner join Settings setting on  setting.SupplierId = sup.Id
		.......
		where setting.ApplicationId in ('A', 'B', 'C' )--@applicationId

[/SRC]
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
07.11.2018, 11:15
    #39729131
Makvetsyan Artur
Makvetsyan Artur
Гость
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
оптимизация запроса
Maxx,

Кажется я непонятно изложил вопрос. Вы так и не поняли суть задачи. Ответ простой для простой задачи. Все равно все спасибо за помощь
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
07.11.2018, 11:18
    #39729136
Maxx
Maxx
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
оптимизация запроса
Makvetsyan Artur,

ну сорри учитесь задавать вопросы....
вам сказали что у вас 3 раза делаеться один и ото же селект ...
какой ответ вы хотите ? Иили избавить от ф-ции религия не возволяет ?
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
07.11.2018, 11:36
    #39729150
Makvetsyan Artur
Makvetsyan Artur
Гость
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
оптимизация запроса
Maxx,
авторну сорри учитесь задавать вопросы....
Жаль что нельзя редактировать тему. Но я указал пример исходных данных, и что должно быть на выходе, чтобы поняли какие зависимости
У меня самого нет желания делать лишних селектов и лишних обращений к таблице
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
07.11.2018, 11:56
    #39729169
invm
invm
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
оптимизация запроса
Makvetsyan Artur,

Запрос нужно переписать без функций и примерно в таком ключе:
Код: 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.
begin tran;

select
 supplier_id, article_id, price
into
 #SupplierPrices
from
 (values ('A', 1, 10), ('A', 2, 10), ('B', 3, 15), ('B', 4, 15), ('C', 5, 20), ('C', 6, 20)) t(supplier_id, article_id, price);

select
 coefficient_id, value
into
 #Coefficients
from
 (values (1, 1.5), (2, 2)) t(coefficient_id, value);

declare @t table (supplier_id varchar(10), coefficient_id int);
insert into @t
values
 ('A', 1), ('B', 2), ('C', null);

select
 sp.supplier_id, sp.article_id, sp.price,
 coalesce(c.value * sp.price, sp.price) as calculated_price
from
 @t t join
 #SupplierPrices sp on sp.supplier_id = t.supplier_id left join
 #Coefficients c on c.coefficient_id = t.coefficient_id;

rollback;
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
07.11.2018, 13:07
    #39729234
Makvetsyan Artur
Makvetsyan Artur
Гость
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
оптимизация запроса
invm, Вашу задумку понял, собираете отдельно коэффициенты наценок а потом применить в результирующий запрос. Что то в этом ключе попробую.
Почему я так не делал отдельно, потому что мне нужно знать цены, по которой компания В может купить у поставщиков, а потом применить коэффициенты В.
Пример:
Цена товара поставщика А известна его закупочная 500 руб. Этот же товар А предлагает В по цене 700 руб (наценки А для В).
А затем чтобы компания В могла продавать своим клиентам, нужно применить наценку 30%, плюс 5 % если (товар * 30%) находится в диапазоне от 1500 до 2000 руб.
Ладно ребята, спасибо за помощь.
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
07.11.2018, 14:47
    #39729334
aleks222
aleks222
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
оптимизация запроса
Makvetsyan Arturinvm, Вашу задумку понял, собираете отдельно коэффициенты наценок а потом применить в результирующий запрос. Что то в этом ключе попробую.
Почему я так не делал отдельно, потому что мне нужно знать цены, по которой компания В может купить у поставщиков, а потом применить коэффициенты В.
Пример:
Цена товара поставщика А известна его закупочная 500 руб. Этот же товар А предлагает В по цене 700 руб (наценки А для В).
А затем чтобы компания В могла продавать своим клиентам, нужно применить наценку 30%, плюс 5 % если (товар * 30%) находится в диапазоне от 1500 до 2000 руб.
Ладно ребята, спасибо за помощь.

Пойми, страдалец, всем лень вникать в тонкости твоих расчетов.

НО! Большинство понимает, что считать для А и В цены "особым образом" можно в ОДНОМ селекте.
Как только ты это осознаешь - тебе дивно полегчает.
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
07.11.2018, 15:36
    #39729365
Makvetsyan Artur
Makvetsyan Artur
Гость
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
оптимизация запроса
aleks222,

Ребята, не пинайте меня одной подсказской. Это я понял. Я это экспериментировал и с одним union, результата нет.
Да знаю что тут я напихал много текста.
Я не страдалец, вы какие-то грубые. Я пришел за подсказской, а вы все одно и тоже. Но invm отдельно спасибо, как-то старались помочь а не пинать как вы меня
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
07.11.2018, 19:03
    #39729525
Владислав Колосов
Владислав Колосов
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
оптимизация запроса
Makvetsyan Artur,

Здесь надо ориентироваться на то, что сервер пытается сделать все сразу и при этом почти вслепую. Все равно как из мешка среди кучи грецких орехов пытаться вытащить горсть фундука одной рукой не заглядывая в мешок по описанию ощущений другим человеком.
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
07.11.2018, 22:09
    #39729596
PizzaPizza
PizzaPizza
Участник
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
оптимизация запроса
Makvetsyan ArturЯ пришел за подсказской, а вы все одно и тоже.

Вам подсказка не нужна. Вам стоит начать разбивать запрос на шаги начиная с самого глубокого подзапроса и задавать себе вопросы "почему" и "зачем". Например: зачем вы делаете выборку из таблицы с помощью функции и потом присоединяете всю эту же таблицу к результатам выборки?

Возможно у вас там более сложный алгоритм и структура таблиц, так как очевидно, что приведенный вами код сильно модифицирован, и не видя это все возможно только гадать.
...
Рейтинг: 0 / 0
| Ответить | Цитировать | Написать  
Форумы / Microsoft SQL Server [игнор отключен] [закрыт для гостей] / оптимизация запроса / 24 сообщений из 24, страница 1 из 1
Найденые пользователи ...
Разблокировать пользователей ...
Читали тему (0):
Читали форум (0):
Пользователи онлайн (0):
x
x
Закрыть


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