August 10, 2017 at 11:19 am
I am just curious if there a way to make this query perform better? I had statistics io and time on. Can I create a clustered index on Year column? Will that help?
(124 row(s) affected)
Table 'Worktable'. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'Workfile'. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'bean_all'. Scan count 20, logical reads 11940, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'ProFarmer_Soybean'. Scan count 4, logical reads 12, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.(1 row(s) affected)
SQL Server Execution Times:
CPU time = 110 ms, elapsed time = 220 ms.
SQL Server parse and compile time:
CPU time = 0 ms, elapsed time = 0 ms.SQL Server Execution Times:
CPU time = 0 ms, elapsed time = 0 ms.
Select Case when state='W. Iowa' then 'Iowa'
when state='E. Iowa' then 'Iowa'
Else state end state,
WeekDayName,
Desctext,
SortOrder,
sum(AvgDataHistory) AvgDataHistory,
sum(Data2017) Data2017,
sum(Avg3Year) Avg3Year,
case when sum(Data2017)=0 then 0 else (sum(Data2017)- sum(AvgDataHistory))/sum(Data2017) end PercChange,
case when sum(Avg3Year)=0 then 0 else (sum(Data2017)- sum(Avg3Year))/sum(Avg3Year) end PercentageChange from
(
select state, 'Pods 3-ft Row' DescText, DATENAME(dw,AddedDTTM) WeekDayName, Avg (PodCount) AvgDataHistory, 0 Data2017, 0 Avg3Year,1 SortOrder from Bean_All where year in (2016) group by State, DATENAME(dw,AddedDTTM)
union
select state, 'Pods 3 By 3' DescText, DATENAME(dw,AddedDTTM) WeekDayName, Avg(PodCountIn3SquareFeet) AvgDataHistory, 0 Data2017, 0 Avg3Year,4 SortOrder from Bean_All where year in (2016) group by State, DATENAME(dw,AddedDTTM)
union
select state, 'Growth Stage' DescText, DATENAME(dw,AddedDTTM) WeekDayName, Avg(Growth) AvgDataHistory, 0 Data2017, 0 Avg3Year,3 SortOrder from Bean_All where year in (2016) group by State, DATENAME(dw,AddedDTTM)
union
select state, 'Moisture' DescText, DATENAME(dw,AddedDTTM) WeekDayName, Avg(Moisture) AvgDataHistory, 0 Data2017, 0 Avg3Year,2 SortOrder from Bean_All where year in (2016) group by State, DATENAME(dw,AddedDTTM)
union
select state, 'Pods 3-ft Row' DescText, DATENAME(dw,AddedDTTM) WeekDayName, 0 AvgDataHistory, Avg(PodsIn3Ft) Data2017, 0 Avg3Year,1 SortOrder from ProFarmer_Soybean where Year(AddedDTTM)=2017 group by State, DATENAME(dw,AddedDTTM)
union
select state, 'Pods 3 By 3' DescText, DATENAME(dw,AddedDTTM) WeekDayName, 0 AvgDataHistory, Avg(PodsIn3X3) Data2017, 0 Avg3Year,4 SortOrder from ProFarmer_Soybean where Year(AddedDTTM)=2017 group by State, DATENAME(dw,AddedDTTM)
union
select state, 'Growth Stage' DescText, DATENAME(dw,AddedDTTM) WeekDayName, 0 AvgDataHistory, Avg(SoybeanMaturityID) Data2017, 0 Avg3Year,3 SortOrder from ProFarmer_Soybean where Year(AddedDTTM)=2017 group by State, DATENAME(dw,AddedDTTM)
union
select state, 'Moisture' DescText, DATENAME(dw,AddedDTTM) WeekDayName, 0 AvgDataHistory, Avg(SoybeanSoilMoistureID) Data2017, 0 Avg3Year,2 SortOrder from ProFarmer_Soybean where Year(AddedDTTM)=2017 group by State, DATENAME(dw,AddedDTTM)
union
select state, 'Pods 3-ft Row' DescText, 'Monday' WeekDayName, 0 AvgDataHistory, 0 Data2017, Avg(PodCount) Avg3Year,1 SortOrder from Bean_All where year in (2014,2015,2016) group by State
union
select state, 'Pods 3 By 3' DescText, 'Monday' WeekDayName, 0 AvgDataHistory, 0 Data2017, Avg(PodCountIn3SquareFeet) Avg3Year,4 SortOrder from Bean_All where year in (2014,2015,2016) group by State
union
select state, 'Growth Stage' DescText, 'Monday' WeekDayName, 0 AvgDataHistory, 0 Data2017, Avg(Growth) Avg3Year,3 SortOrder from Bean_All where year in (2014,2015,2016) group by State
union
select state, 'Moisture' DescText, 'Monday' WeekDayName, 0 AvgDataHistory, 0 Data2017, Avg(Moisture) Avg3Year,2 SortOrder from Bean_All where year in (2014,2015,2016) group by State
union
select state, 'Pods 3-ft Row' DescText, 'Tuesday' WeekDayName, 0 AvgDataHistory, 0 Data2017, Avg(PodCount) Avg3Year,1 SortOrder from Bean_All where year in (2014,2015,2016) group by State
union
select state, 'Pods 3 By 3' DescText, 'Tuesday' WeekDayName, 0 AvgDataHistory, 0 Data2017, Avg(PodCountIn3SquareFeet) Avg3Year,4 SortOrder from Bean_All where year in (2014,2015,2016) group by State
union
select state, 'Growth Stage' DescText, 'Tuesday' WeekDayName, 0 AvgDataHistory, 0 Data2017, Avg(Growth) Avg3Year,3 SortOrder from Bean_All where year in (2014,2015,2016) group by State
union
select state, 'Moisture' DescText, 'Tuesday' WeekDayName, 0 AvgDataHistory, 0 Data2017, Avg(Moisture) Avg3Year,2 SortOrder from Bean_All where year in (2014,2015,2016) group by State
union
select state, 'Pods 3-ft Row' DescText, 'Wednesday' WeekDayName, 0 AvgDataHistory, 0 Data2017, Avg(PodCount) Avg3Year,1 SortOrder from Bean_All where year in (2014,2015,2016) group by State
union
select state, 'Pods 3 By 3' DescText, 'Wednesday' WeekDayName, 0 AvgDataHistory, 0 Data2017, Avg(PodCountIn3SquareFeet) Avg3Year,4 SortOrder from Bean_All where year in (2014,2015,2016) group by State
union
select state, 'Growth Stage' DescText, 'Wednesday' WeekDayName, 0 AvgDataHistory, 0 Data2017, Avg(Growth) Avg3Year,3 SortOrder from Bean_All where year in (2014,2015,2016) group by State
union
select state, 'Moisture' DescText, 'Wednesday' WeekDayName, 0 AvgDataHistory, 0 Data2017, Avg(Moisture) Avg3Year,2 SortOrder from Bean_All where year in (2014,2015,2016) group by State
union
select state, 'Pods 3-ft Row' DescText, 'Thursday' WeekDayName, 0 AvgDataHistory, 0 Data2017, Avg(PodCount) Avg3Year,1 SortOrder from Bean_All where year in (2014,2015,2016) group by State
union
select state, 'Pods 3 By 3' DescText, 'Thursday' WeekDayName, 0 AvgDataHistory, 0 Data2017, Avg(PodCountIn3SquareFeet) Avg3Year,4 SortOrder from Bean_All where year in (2014,2015,2016) group by State
union
select state, 'Growth Stage' DescText, 'Thursday' WeekDayName, 0 AvgDataHistory, 0 Data2017, Avg(Growth) Avg3Year,3 SortOrder from Bean_All where year in (2014,2015,2016) group by State
union
select state, 'Moisture' DescText, 'Thursday' WeekDayName, 0 AvgDataHistory, 0 Data2017, Avg(Moisture) Avg3Year,2 SortOrder from Bean_All where year in (2014,2015,2016) group by State
) AggSoybeanData
--Where (Case when state='W. Iowa' then 'Iowa'
--when state='E. Iowa' then 'Iowa'
--Else state end)='All'
group by
Case when state='W. Iowa' then 'Iowa'
when state='E. Iowa' then 'Iowa'
Else state end
, WeekDayName, Desctext,SortOrder
order by Sortorder
August 10, 2017 at 11:40 am
I didn't check in detail, but something like this might improve the query.
SELECT CASE
WHEN STATE = 'W. Iowa'
THEN 'Iowa'
WHEN STATE = 'E. Iowa'
THEN 'Iowa'
ELSE STATE
END STATE,
WeekDayName,
Desctext,
SortOrder,
sum(AvgDataHistory) AvgDataHistory,
sum(Data2017) Data2017,
sum(Avg3Year) Avg3Year,
CASE
WHEN sum(Data2017) = 0
THEN 0
ELSE (sum(Data2017) - sum(AvgDataHistory)) / sum(Data2017)
END PercChange,
CASE
WHEN sum(Avg3Year) = 0
THEN 0
ELSE (sum(Data2017) - sum(Avg3Year)) / sum(Avg3Year)
END PercentageChange
FROM (
SELECT STATE,
up.DescText,
DATENAME(dw, AddedDTTM) WeekDayName,
Avg(up.DataHistory) AvgDataHistory,
0 Data2017,
0 Avg3Year,
up.SortOrder
FROM Bean_All
CROSS APPLY(VALUES
('Pods 3-ft Row', 1, PodCount),
('Moisture', 2, Moisture),
('Growth Stage', 3, Growth),
('Pods 3 By 3', 4, PodCountIn3SquareFeet)) up(DescText, SortOrder, DataHistory)
WHERE year IN (2016)
GROUP BY STATE,
up.DescText,
DATENAME(dw, AddedDTTM),
up.SortOrder
UNION ALL
SELECT STATE,
up.DescText,
DATENAME(dw, AddedDTTM) WeekDayName,
0 AvgDataHistory,
Avg(up.Data2017) Data2017,
0 Avg3Year,
up.SortOrder
FROM ProFarmer_Soybean
CROSS APPLY(VALUES
('Pods 3-ft Row', 1, PodsIn3Ft),
('Moisture', 2, SoybeanSoilMoistureID),
('Growth Stage', 3, SoybeanMaturityID),
('Pods 3 By 3', 4, PodsIn3X3)) up(DescText, SortOrder, Data2017)
WHERE Year(AddedDTTM) = 2017
GROUP BY STATE,
up.DescText,
DATENAME(dw, AddedDTTM),
up.SortOrder
UNION ALL
SELECT STATE,
up.DescText,
dw.WeekDayName,
Avg(up.DataHistory) AvgDataHistory,
0 Data2017,
0 Avg3Year,
up.SortOrder
FROM Bean_All
CROSS APPLY(VALUES
('Pods 3-ft Row', 1, PodCount),
('Moisture', 2, Moisture),
('Growth Stage', 3, Growth),
('Pods 3 By 3', 4, PodCountIn3SquareFeet)) up(DescText, SortOrder, DataHistory)
CROSS APPLY(VALUES('Monday'),('Tuesday'),('Wednesday'),('Thursday'))dw(WeekDayName)
WHERE year IN (2014,2015,2016)
GROUP BY STATE,
up.DescText,
dw.WeekDayName,
up.SortOrder
) AggSoybeanData
GROUP BY CASE
WHEN STATE = 'W. Iowa'
THEN 'Iowa'
WHEN STATE = 'E. Iowa'
THEN 'Iowa'
ELSE STATE
END,
WeekDayName,
Desctext,
SortOrder
ORDER BY Sortorder;
August 10, 2017 at 11:46 am
By the way, the execution plan attached is only for the following statement.set statistics time, io on
In other words, it's useless.
August 10, 2017 at 12:05 pm
1) You just cannot hit the same table over and over and be successful.
2) CASE is your friend!!! Learn to use it!
3) All of the hits you did were mutually exclusive. So your UNIONs did a SORT/DISTINCT under the covers (a VERY expensive operation) to remove duplicates that couldn't possibly exist.
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
August 10, 2017 at 12:29 pm
TheSQLGuru - Thursday, August 10, 2017 12:05 PM2) CASE is your friend!!! Learn to use it!
Just curious. How would you use CASE in here? I might actually remove it.
August 10, 2017 at 3:30 pm
Luis Cazares - Thursday, August 10, 2017 12:29 PMTheSQLGuru - Thursday, August 10, 2017 12:05 PM2) CASE is your friend!!! Learn to use it!Just curious. How would you use CASE in here? I might actually remove it.
When I looked at the OP I saw values and iterative hits. DOH! :blink:
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply