none
Оптимизация запроса

    Вопрос

  • Доброе время суток

    Борюсь с одним запросом, в данный момент выполняется 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
    


    Mak Arti

    6 ноября 2018 г. 15:27

Ответы

  • Если union all не помогает, то начните с переработки структуры запроса вообще

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

    Стройте запрос последовательно. Нарисуйте для начала запрос, который просто отберёт все необходимые вам записи в detail и сделайте быстрым его

    7 ноября 2018 г. 16:50

Все ответы

  • Стоимость объединения discinct sort очень дорогая, ребята сможете подсказать другие варианты?

    Mak Arti

    6 ноября 2018 г. 15:29
  • Использование union вместо union all почти всегда происходит не по делу. И почти всегда приводит к чудовищной деградации производительности.

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

    Из других очевидных мелочей, которые исчезают на фоне union - использование where ISNULL(surProf.IsEnabled, 1) = 1 также не очень хорошо, так как лишает оптимизатор возможность построить план по другому. 

    Эквивиалентная запись через (surProf.IsEnabled is null or surProf.IsEnabled = 1) может улучшить дело. Но повторюсь, это уже мелочи на фоне union.

    7 ноября 2018 г. 9:47
  • Знаю, на удивление union all работает дольше 5 мин.

    Насчет мелочи я хотел избежать от 'or' в плане производительности. Но если вы говорите что это эквивалентно, то буду знать.


    Mak Arti

    7 ноября 2018 г. 10:15
  • Если union all не помогает, то начните с переработки структуры запроса вообще

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

    Стройте запрос последовательно. Нарисуйте для начала запрос, который просто отберёт все необходимые вам записи в detail и сделайте быстрым его

    7 ноября 2018 г. 16:50