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

Откуда:
Сообщений: 11
Доброе время суток

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

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

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

Вот примерно сам запрос
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
				
)


и применение
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 очень дорогая, ребята сможете подсказать другие варианты?
6 ноя 18, 18:32    [21725694]     Ответить | Цитировать Сообщить модератору
 Re: оптимизация запроса  [new]
Владислав Колосов
Member

Откуда:
Сообщений: 7762
Makvetsyan Artur,

discinct sort вызывает union, уверены, что нельзя использовать union all?
6 ноя 18, 18:36    [21725703]     Ответить | Цитировать Сообщить модератору
 Re: оптимизация запроса  [new]
PizzaPizza
Member

Откуда:
Сообщений: 370
Makvetsyan Artur,

Очень большие таблицы? Может быть вьюшку сделать по всем компаниям с уже посчитанными ценами (SCHEMABINDING?) и из неё выбирать?
6 ноя 18, 19:16    [21725763]     Ответить | Цитировать Сообщить модератору
 Re: оптимизация запроса  [new]
Makvetsyan Artur
Member

Откуда:
Сообщений: 11
Владислав Колосов, пробовал, да и по идею union all должен шустрее работать, но работает больше 5 мин
6 ноя 18, 23:31    [21725955]     Ответить | Цитировать Сообщить модератору
 Re: оптимизация запроса  [new]
Makvetsyan Artur
Member

Откуда:
Сообщений: 11
PizzaPizza,
Не знаю как это поможет, мне нужно получить готовую цену от компании А и Б со своими наценками, а потом от этой цены прибавить наценки В. Грубо говоря А и Б являются поставщиками В
6 ноя 18, 23:52    [21725973]     Ответить | Цитировать Сообщить модератору
 Re: оптимизация запроса  [new]
PizzaPizza
Member

Откуда:
Сообщений: 370
Makvetsyan Artur,

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

запрос выполняется часто

Вы джойните таблицу Supplier в функции, которая выполняется два раза, и потом еще раз джойните в основном запросе. Я бы сравнил планы выполнения ваших запросов, т.к. возможно, что, если не использовать функции, оптимизатор сможет уменьшить количество проходов по таблицам.
7 ноя 18, 00:31    [21725996]     Ответить | Цитировать Сообщить модератору
 Re: оптимизация запроса  [new]
Makvetsyan Artur
Member

Откуда:
Сообщений: 11
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

Вот собственно чего я хотел добиться
7 ноя 18, 01:39    [21726020]     Ответить | Цитировать Сообщить модератору
 Re: оптимизация запроса  [new]
PizzaPizza
Member

Откуда:
Сообщений: 370
Makvetsyan Artur,

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

select * from dbo.[fnSearchSparePartsFromSource]('A')
UNION
select * from dbo.[fnSearchSparePartsFromSource]('B')
UNION
select* from Detail

у вас функции делают выборки из таблицы Detail с фильтром по supplierId, и потом еще раз вся таблица Detail присоединяется к этим выборкам.
7 ноя 18, 03:05    [21726027]     Ответить | Цитировать Сообщить модератору
 Re: оптимизация запроса  [new]
aleks222
Member

Откуда:
Сообщений: 968
PizzaPizza
Makvetsyan Artur,

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

select * from dbo.[fnSearchSparePartsFromSource]('A')
UNION
select * from dbo.[fnSearchSparePartsFromSource]('B')
UNION
select* from Detail

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


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

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

1. Выкинуть функцию.
2. Написать ОДИН раз соединение.
3. Раз в пять должно быть быстрее.
7 ноя 18, 06:08    [21726043]     Ответить | Цитировать Сообщить модератору
 Re: оптимизация запроса  [new]
aleks222
Member

Откуда:
Сообщений: 968
Makvetsyan Artur
CREATE FUNCTION [Detail].[fnSearchSparePartsFromSource]
(
	@applicationId uniqueidentifier,
	@profileId uniqueidentifier,
	@supplierId int
)

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

	FROM( 
				select * from dbo.[fnSearchSparePartsFromSource]('A')
					UNION
				select * from dbo.[fnSearchSparePartsFromSource]('B')


7 ноя 18, 07:49    [21726071]     Ответить | Цитировать Сообщить модератору
 Re: оптимизация запроса  [new]
Makvetsyan Artur
Member

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

Да извините, это я как бы примерный запрос написал, чтоб понять суть. Прошу это не обращать внимания на кол-во параметров в функции. Насчет представления как раз попробую, а потом объединить.
7 ноя 18, 08:21    [21726083]     Ответить | Цитировать Сообщить модератору
 Re: оптимизация запроса  [new]
Makvetsyan Artur
Member

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

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


Это конечно хорошо один раз соединение. Не знаю может я туплю или вы таки не поняли суть задачи
7 ноя 18, 08:24    [21726085]     Ответить | Цитировать Сообщить модератору
 Re: оптимизация запроса  [new]
Maxx
Member [скрыт]

Откуда:
Сообщений: 24290
мдя.. ТС..ето шутка юмора ? Какая оптимизация если пример отродясь не рабочий


 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.
7 ноя 18, 10:28    [21726207]     Ответить | Цитировать Сообщить модератору
 Re: оптимизация запроса  [new]
Makvetsyan Artur
Member

Откуда:
Сообщений: 11
Maxx, Ребята я извиняюсь что с ошибкой опубликовал код, но суть не в этом. Мне просто нужна подсказка, варианты объединения или каких-то хитростей. Я же писал код выполняется 10 сек, и код рабочий, а тут немного скорректировал, показать только саму структуру запроса
7 ноя 18, 10:57    [21726269]     Ответить | Цитировать Сообщить модератору
 Re: оптимизация запроса  [new]
Maxx
Member [скрыт]

Откуда:
Сообщений: 24290
Makvetsyan Artur
Maxx, Ребята я извиняюсь что с ошибкой опубликовал код, но суть не в этом. Мне просто нужна подсказка, варианты объединения или каких-то хитростей. Я же писал код выполняется 10 сек, и код рабочий, а тут немного скорректировал, показать только саму структуру запроса

Да Вам же уже ответили

[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]
7 ноя 18, 11:04    [21726284]     Ответить | Цитировать Сообщить модератору
 Re: оптимизация запроса  [new]
Makvetsyan Artur
Member

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

Кажется я непонятно изложил вопрос. Вы так и не поняли суть задачи. Ответ простой для простой задачи. Все равно все спасибо за помощь
7 ноя 18, 11:15    [21726307]     Ответить | Цитировать Сообщить модератору
 Re: оптимизация запроса  [new]
Maxx
Member [скрыт]

Откуда:
Сообщений: 24290
Makvetsyan Artur,

ну сорри учитесь задавать вопросы....
вам сказали что у вас 3 раза делаеться один и ото же селект ...
какой ответ вы хотите ? Иили избавить от ф-ции религия не возволяет ?
7 ноя 18, 11:18    [21726316]     Ответить | Цитировать Сообщить модератору
 Re: оптимизация запроса  [new]
Makvetsyan Artur
Member

Откуда:
Сообщений: 11
Maxx,
автор
ну сорри учитесь задавать вопросы....

Жаль что нельзя редактировать тему. Но я указал пример исходных данных, и что должно быть на выходе, чтобы поняли какие зависимости
У меня самого нет желания делать лишних селектов и лишних обращений к таблице
7 ноя 18, 11:36    [21726351]     Ответить | Цитировать Сообщить модератору
 Re: оптимизация запроса  [new]
invm
Member

Откуда: Москва
Сообщений: 9349
Makvetsyan Artur,

Запрос нужно переписать без функций и примерно в таком ключе:
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;
7 ноя 18, 11:56    [21726410]     Ответить | Цитировать Сообщить модератору
 Re: оптимизация запроса  [new]
Makvetsyan Artur
Member

Откуда:
Сообщений: 11
invm, Вашу задумку понял, собираете отдельно коэффициенты наценок а потом применить в результирующий запрос. Что то в этом ключе попробую.
Почему я так не делал отдельно, потому что мне нужно знать цены, по которой компания В может купить у поставщиков, а потом применить коэффициенты В.
Пример:
Цена товара поставщика А известна его закупочная 500 руб. Этот же товар А предлагает В по цене 700 руб (наценки А для В).
А затем чтобы компания В могла продавать своим клиентам, нужно применить наценку 30%, плюс 5 % если (товар * 30%) находится в диапазоне от 1500 до 2000 руб.
Ладно ребята, спасибо за помощь.
7 ноя 18, 13:07    [21726637]     Ответить | Цитировать Сообщить модератору
 Re: оптимизация запроса  [new]
aleks222
Member

Откуда:
Сообщений: 968
Makvetsyan Artur
invm, Вашу задумку понял, собираете отдельно коэффициенты наценок а потом применить в результирующий запрос. Что то в этом ключе попробую.
Почему я так не делал отдельно, потому что мне нужно знать цены, по которой компания В может купить у поставщиков, а потом применить коэффициенты В.
Пример:
Цена товара поставщика А известна его закупочная 500 руб. Этот же товар А предлагает В по цене 700 руб (наценки А для В).
А затем чтобы компания В могла продавать своим клиентам, нужно применить наценку 30%, плюс 5 % если (товар * 30%) находится в диапазоне от 1500 до 2000 руб.
Ладно ребята, спасибо за помощь.


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

НО! Большинство понимает, что считать для А и В цены "особым образом" можно в ОДНОМ селекте.
Как только ты это осознаешь - тебе дивно полегчает.
7 ноя 18, 14:47    [21726890]     Ответить | Цитировать Сообщить модератору
 Re: оптимизация запроса  [new]
Makvetsyan Artur
Member

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

Ребята, не пинайте меня одной подсказской. Это я понял. Я это экспериментировал и с одним union, результата нет.
Да знаю что тут я напихал много текста.
Я не страдалец, вы какие-то грубые. Я пришел за подсказской, а вы все одно и тоже. Но invm отдельно спасибо, как-то старались помочь а не пинать как вы меня
7 ноя 18, 15:36    [21726991]     Ответить | Цитировать Сообщить модератору
 Re: оптимизация запроса  [new]
Владислав Колосов
Member

Откуда:
Сообщений: 7762
Makvetsyan Artur,

Здесь надо ориентироваться на то, что сервер пытается сделать все сразу и при этом почти вслепую. Все равно как из мешка среди кучи грецких орехов пытаться вытащить горсть фундука одной рукой не заглядывая в мешок по описанию ощущений другим человеком.
7 ноя 18, 19:03    [21727258]     Ответить | Цитировать Сообщить модератору
 Re: оптимизация запроса  [new]
PizzaPizza
Member

Откуда:
Сообщений: 370
Makvetsyan Artur
Я пришел за подсказской, а вы все одно и тоже.


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

Возможно у вас там более сложный алгоритм и структура таблиц, так как очевидно, что приведенный вами код сильно модифицирован, и не видя это все возможно только гадать.
7 ноя 18, 22:09    [21727454]     Ответить | Цитировать Сообщить модератору
Все форумы / Microsoft SQL Server Ответить