July 14, 2020 at 10:19 pm
hi I have a simple query that needs better performance. I have added primary key into the temp table which helped, but here is the root of the problem below.. There has to be a cleaner and more efficient way to write this, any thoughts? Thanks
INSERT INTO [dbo].[WeeklySalesReportTempTable]
([Location Code]
,[Profit2YrsAgo]
,[ProfitLastYear]
,[ProfitLYMTD]
,[ProfitMTD]
,[Sales2YrsAgo]
,[SalesLastYear]
,[SalesLYMTD]
,[SalesMTD]
,[ESPLastYear]
,[ESPMTD]
,[InvOnHand]
,[AccLastYear]
,[AccMTD])
VALUES
(@Location
,(select sum([Profit]) as 'Profit2YRsAgoP'
from [db].[dbo].[Salesperson Ledger Entry] le
where [Global Dimension 1 Code] = @Location
and ([Posting Date] >= @PriorStart2 and [Posting Date] <= @PriorEnd2)
and [Salesperson Code (last 2)] not in ('10','98','96','99','90')
and [Item_Resource Category] not in ('RC','ZZ','9T','RD','XX')
and [Item_Resource Division] not in ('RC','ZZ','9S','9T','ZC','RD','ZX','9V','ZY','51','RG','RH','RI','RJ','RK'))
,(select sum([Profit]) as 'ProfitLastYearP'
from [db].[dbo].[Salesperson Ledger Entry] le
where [Global Dimension 1 Code] = @Location
and ([Posting Date] >= @PriorStart and [Posting Date] <= @PriorEnd)
and [Salesperson Code (last 2)] not in ('10','98','96','99','90')
and [Item_Resource Category] not in ('RC','ZZ','9T','RD','XX')
and [Item_Resource Division] not in ('RC','ZZ','9S','9T','ZC','RD','ZX','9V','ZY','51','RG','RH','RI','RJ','RK'))
,(select sum([Profit]) as 'ProfitLYMTDP'
from [db].[dbo].[Salesperson Ledger Entry] le
where [Global Dimension 1 Code] = @Location
and ([Posting Date] >= @PriorStartLY and [Posting Date] <= @PriorEndLY)
and [Salesperson Code (last 2)] not in ('10','98','96','99','90')
and [Item_Resource Category] not in ('RC','ZZ','9T','RD','XX')
and [Item_Resource Division] not in ('RC','ZZ','9S','9T','ZC','RD','ZX','9V','ZY','51','RG','RH','RI','RJ','RK'))
,(select sum([Profit]) as 'ProfitMTDP'
from [db].[dbo].[Salesperson Ledger Entry] le
where [Global Dimension 1 Code] = @Location
and ([Posting Date] >= @start and [Posting Date] <= @end)
and [Salesperson Code (last 2)] not in ('10','98','96','99','90')
and [Item_Resource Category] not in ('RC','ZZ','9T','RD','XX')
and [Item_Resource Division] not in ('RC','ZZ','9S','9T','ZC','RD','ZX','9V','ZY','51','RG','RH','RI','RJ','RK'))
,(select sum([Amount]) as 'Sales2YRAgoS'
from [db].[dbo].[Salesperson Ledger Entry] le
where [Global Dimension 1 Code] = @Location
and ([Posting Date] >= @PriorStart2 and [Posting Date] <= @PriorEnd2)
and [Salesperson Code (last 2)] not in ('10','98','96','99','90')
and [Item_Resource Category] not in ('RC','ZZ','9T','RD','RE','RF','RG','RH','RI','XX')
and [Item_Resource Division] not in ('RC','ZZ','9S','9T','51','ZC','RD','RG','RH','RI','RJ','RK'))
,( select sum([Amount]) as 'SalesLastYearS'
from [db].[dbo].[Salesperson Ledger Entry] le
where [Global Dimension 1 Code] = @Location
and ([Posting Date] >= @PriorStart and [Posting Date] <= @PriorEnd)
and [Salesperson Code (last 2)] not in ('10','98','96','99','90')
and [Item_Resource Category] not in ('RC','ZZ','9T','RD','RE','RF','RG','RH','RI','XX')
and [Item_Resource Division] not in ('RC','ZZ','9S','9T','51','ZC','RD','RG','RH','RI','RJ','RK'))
,(select sum([Amount]) as 'SalesYRMTDS'
from [db].[dbo].[Salesperson Ledger Entry] le
where [Global Dimension 1 Code] = @Location
and (([Posting Date] >= @PriorStartLY and [Posting Date] <= @PriorEndLY))
and [Salesperson Code (last 2)] not in ('10','98','96','99','90')
and [Item_Resource Category] not in ('RC','ZZ','9T','RD','RE','RF','RG','RH','RI','XX')
and [Item_Resource Division] not in ('RC','ZZ','9S','9T','51','ZC','RD','RG','RH','RI','RJ','RK'))
,(-- MTD
select sum([Amount])as 'SalesMTDS'
from [db].[dbo].[Visions$Salesperson Ledger Entry] le
where [Global Dimension 1 Code] = @Location
and ([Posting Date] >= @start and [Posting Date] <= @end)
and [Salesperson Code (last 2)] not in ('10','98','96','99','90')
and [Item_Resource Category] not in ('RC','ZZ','9T','RD','RE','RF','RG','RH','RI','XX')
and [Item_Resource Division] not in ('RC','ZZ','9S','9T','51','ZC','RD','RG','RH','RI','RJ','RK'))
,-- ESP Values
-- Last Year
(select sum([Amount]) as 'ESPLastYearE'
from [db].[dbo].[Visions$Salesperson Ledger Entry] le
where [Global Dimension 1 Code] = @Location
and ([Posting Date] >= @PriorStart and [Posting Date] <= @PriorEnd)
and [Salesperson Code (last 2)] not in ('10','98','96','99','90')
and [Item_Resource Category] in ('60')
and [Item_Resource Division] in ('91','92','93','94','95','96','97','98','99','9A','9B','9C','ZE','ZF','ZG',
'ZI','ZK','Z0','Z1','Z2','Z3','Z4','Z5','Z6','Z7','Z8','9F','9G','9H','9I','9J','9K','9L','9W','ZL'))
, -- MTD
(select sum([Amount]) as 'ESPMTDE'
from [db].[dbo].[Visions$Salesperson Ledger Entry] le
where [Global Dimension 1 Code] = @Location
and ([Posting Date] >= @start and [Posting Date] <= @end)
and [Salesperson Code (last 2)] not in ('10','98','96','99','90')
and [Item_Resource Category] in ('60')
and [Item_Resource Division] in ('91','92','93','94','95','96','97','98','99','9A','9B','9C','ZE','ZF','ZG','ZI',
'ZK','Z0','Z1','Z2','Z3','Z4','Z5','Z6','Z7','Z8','9F','9G','9H','9I','9J','9K','9L','9W','ZL'))
,-- Inventory
(select sum([Item Average Cost])
from [db].[dbo].[Visions$Item Valuation by Location] le
where [Location Code] = @Location)
,--Accessories
(select sum([Amount])
from [db].[dbo].[Visions$Salesperson Ledger Entry] le
where [Global Dimension 1 Code] = @Location
and ([Posting Date] >= @PriorStart and [Posting Date] <= @PriorEnd)
and [Salesperson Code (last 2)] not in ('10','98','96','99','90')
and [Item_Resource Category] not in ('RC','ZZ','9T','RD','RE','RF','RG','RH','RI','XX')
and [Item_Resource Division] in ('I1','1A','1B','1V','49','TV','71','81','90','A','B','A1','B1','SC','SD','SM','SQ','SR','SS','ST','SU','SZ','S3','S7','T0','T1','T2','T3','T4','TP',
'50','90','SP','SY','48','D','SW','TW','QB','Q3','Q6','GU','GW','GX','SL','A2','SO','GT','27','1J','1K','1L','1M'))
,
(select sum([Amount])
from [db].[dbo].[Visions$Salesperson Ledger Entry] le
where [Global Dimension 1 Code] = @Location
and ([Posting Date] >= @start and [Posting Date] <= @end)
and [Salesperson Code (last 2)] not in ('10','98','96','99','90')
and [Item_Resource Category] not in ('RC','ZZ','9T','RD','RE','RF','RG','RH','RI','XX')
and [Item_Resource Division] in ('I1','1A','1B','1V','49','TV','71','81','90','A','B','A1','B1','SC','SD','SM','SQ','SR','SS','ST','SU','SZ','S3','S7','T0','T1','T2','T3','T4','TP',
'50','90','SP','SY','48','D','SW','TW','QB','Q3','Q6','GU','GW','GX','SL','A2','SO','GT','27','1J','1K','1L','1M')))?
INSERT INTO [dbo].[WeeklySalesReportTempTable]([Location Code],[Profit2YrsAgo],[ProfitLastYear],[ProfitLYMTD],[ProfitMTD],[Sales2YrsAgo],[SalesLastYear],[SalesLYMTD],[SalesMTD],[ESPLastYear],[ESPMTD],[InvOnHand],[AccLastYear],[AccMTD])VALUES(@Location,(select sum([Profit]) as 'Profit2YRsAgoP'from [db].[dbo].[Salesperson Ledger Entry] lewhere [Global Dimension 1 Code] = @Locationand ([Posting Date] >= @PriorStart2 and [Posting Date] <= @PriorEnd2)and [Salesperson Code (last 2)] not in ('10','98','96','99','90')and [Item_Resource Category] not in ('RC','ZZ','9T','RD','XX')and [Item_Resource Division] not in ('RC','ZZ','9S','9T','ZC','RD','ZX','9V','ZY','51','RG','RH','RI','RJ','RK')),(select sum([Profit]) as 'ProfitLastYearP'from [db].[dbo].[Salesperson Ledger Entry] lewhere [Global Dimension 1 Code] = @Locationand ([Posting Date] >= @PriorStart and [Posting Date] <= @PriorEnd)and [Salesperson Code (last 2)] not in ('10','98','96','99','90')and [Item_Resource Category] not in ('RC','ZZ','9T','RD','XX')and [Item_Resource Division] not in ('RC','ZZ','9S','9T','ZC','RD','ZX','9V','ZY','51','RG','RH','RI','RJ','RK')),(select sum([Profit]) as 'ProfitLYMTDP'from [db].[dbo].[Salesperson Ledger Entry] lewhere [Global Dimension 1 Code] = @Locationand ([Posting Date] >= @PriorStartLY and [Posting Date] <= @PriorEndLY)and [Salesperson Code (last 2)] not in ('10','98','96','99','90')and [Item_Resource Category] not in ('RC','ZZ','9T','RD','XX')and [Item_Resource Division] not in ('RC','ZZ','9S','9T','ZC','RD','ZX','9V','ZY','51','RG','RH','RI','RJ','RK')),(select sum([Profit]) as 'ProfitMTDP'from [db].[dbo].[Salesperson Ledger Entry] lewhere [Global Dimension 1 Code] = @Locationand ([Posting Date] >= @start and [Posting Date] <= @end)and [Salesperson Code (last 2)] not in ('10','98','96','99','90')and [Item_Resource Category] not in ('RC','ZZ','9T','RD','XX')and [Item_Resource Division] not in ('RC','ZZ','9S','9T','ZC','RD','ZX','9V','ZY','51','RG','RH','RI','RJ','RK')),(select sum([Amount]) as 'Sales2YRAgoS'from [db].[dbo].[Salesperson Ledger Entry] lewhere [Global Dimension 1 Code] = @Locationand ([Posting Date] >= @PriorStart2 and [Posting Date] <= @PriorEnd2)and [Salesperson Code (last 2)] not in ('10','98','96','99','90')and [Item_Resource Category] not in ('RC','ZZ','9T','RD','RE','RF','RG','RH','RI','XX')and [Item_Resource Division] not in ('RC','ZZ','9S','9T','51','ZC','RD','RG','RH','RI','RJ','RK')),( select sum([Amount]) as 'SalesLastYearS'from [db].[dbo].[Salesperson Ledger Entry] lewhere [Global Dimension 1 Code] = @Locationand ([Posting Date] >= @PriorStart and [Posting Date] <= @PriorEnd)and [Salesperson Code (last 2)] not in ('10','98','96','99','90')and [Item_Resource Category] not in ('RC','ZZ','9T','RD','RE','RF','RG','RH','RI','XX')and [Item_Resource Division] not in ('RC','ZZ','9S','9T','51','ZC','RD','RG','RH','RI','RJ','RK')),(select sum([Amount]) as 'SalesYRMTDS'from [db].[dbo].[Salesperson Ledger Entry] lewhere [Global Dimension 1 Code] = @Locationand (([Posting Date] >= @PriorStartLY and [Posting Date] <= @PriorEndLY))and [Salesperson Code (last 2)] not in ('10','98','96','99','90')and [Item_Resource Category] not in ('RC','ZZ','9T','RD','RE','RF','RG','RH','RI','XX')and [Item_Resource Division] not in ('RC','ZZ','9S','9T','51','ZC','RD','RG','RH','RI','RJ','RK')),(-- MTDselect sum([Amount])as 'SalesMTDS'from [db].[dbo].[Visions$Salesperson Ledger Entry] lewhere [Global Dimension 1 Code] = @Locationand ([Posting Date] >= @start and [Posting Date] <= @end)and [Salesperson Code (last 2)] not in ('10','98','96','99','90')and [Item_Resource Category] not in ('RC','ZZ','9T','RD','RE','RF','RG','RH','RI','XX')and [Item_Resource Division] not in ('RC','ZZ','9S','9T','51','ZC','RD','RG','RH','RI','RJ','RK')),-- ESP Values-- Last Year(select sum([Amount]) as 'ESPLastYearE'from [db].[dbo].[Visions$Salesperson Ledger Entry] lewhere [Global Dimension 1 Code] = @Locationand ([Posting Date] >= @PriorStart and [Posting Date] <= @PriorEnd)and [Salesperson Code (last 2)] not in ('10','98','96','99','90')and [Item_Resource Category] in ('60')and [Item_Resource Division] in ('91','92','93','94','95','96','97','98','99','9A','9B','9C','ZE','ZF','ZG','ZI','ZK','Z0','Z1','Z2','Z3','Z4','Z5','Z6','Z7','Z8','9F','9G','9H','9I','9J','9K','9L','9W','ZL')) , -- MTD(select sum([Amount]) as 'ESPMTDE'from [db].[dbo].[Visions$Salesperson Ledger Entry] lewhere [Global Dimension 1 Code] = @Locationand ([Posting Date] >= @start and [Posting Date] <= @end)and [Salesperson Code (last 2)] not in ('10','98','96','99','90')and [Item_Resource Category] in ('60')and [Item_Resource Division] in ('91','92','93','94','95','96','97','98','99','9A','9B','9C','ZE','ZF','ZG','ZI','ZK','Z0','Z1','Z2','Z3','Z4','Z5','Z6','Z7','Z8','9F','9G','9H','9I','9J','9K','9L','9W','ZL')) ,-- Inventory(select sum([Item Average Cost])from [db].[dbo].[Visions$Item Valuation by Location] lewhere [Location Code] = @Location),--Accessories(select sum([Amount])from [db].[dbo].[Visions$Salesperson Ledger Entry] lewhere [Global Dimension 1 Code] = @Locationand ([Posting Date] >= @PriorStart and [Posting Date] <= @PriorEnd)and [Salesperson Code (last 2)] not in ('10','98','96','99','90')and [Item_Resource Category] not in ('RC','ZZ','9T','RD','RE','RF','RG','RH','RI','XX')and [Item_Resource Division] in ('I1','1A','1B','1V','49','TV','71','81','90','A','B','A1','B1','SC','SD','SM','SQ','SR','SS','ST','SU','SZ','S3','S7','T0','T1','T2','T3','T4','TP','50','90','SP','SY','48','D','SW','TW','QB','Q3','Q6','GU','GW','GX','SL','A2','SO','GT','27','1J','1K','1L','1M')),(select sum([Amount])from [db].[dbo].[Visions$Salesperson Ledger Entry] lewhere [Global Dimension 1 Code] = @Locationand ([Posting Date] >= @start and [Posting Date] <= @end)and [Salesperson Code (last 2)] not in ('10','98','96','99','90')and [Item_Resource Category] not in ('RC','ZZ','9T','RD','RE','RF','RG','RH','RI','XX')and [Item_Resource Division] in ('I1','1A','1B','1V','49','TV','71','81','90','A','B','A1','B1','SC','SD','SM','SQ','SR','SS','ST','SU','SZ','S3','S7','T0','T1','T2','T3','T4','TP','50','90','SP','SY','48','D','SW','TW','QB','Q3','Q6','GU','GW','GX','SL','A2','SO','GT','27','1J','1K','1L','1M')))
July 15, 2020 at 1:03 am
Is there any way to replace all those NOT IN ( ) clauses? Basically, you're forcing a table scan on all of those columns you're using that on.
July 15, 2020 at 7:06 am
I would change it to have the following construct instead
insert into ....
select @Location
, sum(case
when [Item_Resource Category] not in ('RC','ZZ','9T','RD','RE','RF','RG','RH','RI','XX')
and [Item_Resource Division] in ('I1','1A','1B','1V','49','TV','71','81','90','A','B','A1','B1','SC','SD','SM','SQ','SR','SS','ST','SU','SZ','S3','S7','T0','T1','T2','T3','T4','TP', '50','90','SP','SY','48','D','SW','TW','QB','Q3','Q6','GU','GW','GX','SL','A2','SO','GT','27','1J','1K','1L','1M'))
and ([Posting Date] >= @PriorStart and [Posting Date] <= @PriorEnd)
then [Amount]
else null
end) as accessories_total_previous_Period
, sum(case
when [Item_Resource Category] not in ('RC','ZZ','9T','RD','RE','RF','RG','RH','RI','XX')
and [Item_Resource Division] in ('I1','1A','1B','1V','49','TV','71','81','90','A','B','A1','B1','SC','SD','SM','SQ','SR','SS','ST','SU','SZ','S3','S7','T0','T1','T2','T3','T4','TP', '50','90','SP','SY','48','D','SW','TW','QB','Q3','Q6','GU','GW','GX','SL','A2','SO','GT','27','1J','1K','1L','1M'))
and ([Posting Date] >= @start and [Posting Date] <= @end)
then [Amount]
else null
end) as accessories_total_current_period
.... all other columns
from [dbo].[Visions$Salesperson Ledger Entry] le -- hardcoded db removed - should use synonyms if required
where [Global Dimension 1 Code] = @Location
and (
-- if periods overlap then only a single range should be used
([Posting Date] >= @start and [Posting Date] <= @end)
or ([Posting Date] >= @PriorStart and [Posting Date] <= @PriorEnd)
)
and [Salesperson Code (last 2)] not in ('10','98','96','99','90') -- it would be advisable to build a table containing the valid codes and join to it instead of the not in
-- note that if this query is called multiple times for location then it should be changed so that all locations are supplied (alongside their dates if they change per location) and do all locations in a single pass
July 15, 2020 at 2:48 pm
Thanks for your suggestions... Cheers
July 16, 2020 at 6:46 am
Erased my post... I found an exception to the code I was going to post.
--Jeff Moden
Change is inevitable... Change for the better is not.
July 16, 2020 at 11:48 am
FWIW
Have you tried replacing the "not IN"-lists with temp tables or tablevars ?
Basically you have SQLServer using its query engine in a better way using table objects in stead of scalar lists.
Test then should contain variants of " not exists + correlated subquery " and " left join temptb t on t.col = .. where t.col is null"
Johan
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data/code to get the best help[/url]
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
July 16, 2020 at 1:46 pm
What Frederico wrote at the ende: "note that if this query is called multiple times for location then it should be changed so that all locations are supplied (alongside their dates if they change per location) and do all locations in a single pass" This is what I thought too. This code is maybe being executed in a loop. It would be good/better if the OP posted the whole procedure or script. The whole thing could be table based and all those values removed from code.
Aus dem Paradies, das Cantor uns geschaffen, soll uns niemand vertreiben können
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply