June 13, 2017 at 3:29 pm
I am wondering if someone can look at this query and tell me if this can be optimized. There are multiple heap tables in this query. --set statistics time, io on
--Revenue By Reporting Groups Excluding Pending IOs---
DECLARE @fd DATETIME
SET @fd = '01/01/2016'--'01/01/2016' --'01/01/2016'--
DECLARE @TD DATETIME
SET @TD = '12/31/2016'--'12/31/2016' --'12/31/2016'--
DECLARE @p_FromDate DATETIME = DATEADD(year, -1, @fd )
DECLARE @p_toDate DATETIME = DATEADD(year, -1, @TD)
select
--A.CompanyID ---For Revenue by reporting Grp with Advertiser---Remove when using in revenue by Reporting Grp only--
--,D.Company ---For Revenue by reporting Grp with Advertiser---Remove when using in revenue by Reporting Grp only--
A.EmpID
,B.EmpName
,E.media_group_id as MediaGroupID
,E.media_group_desc as MediaGroup
,C.pub_desc
--,rpt.Reporting_Grp_name as ReportingGrpName
-------------------------Without Adjustments-----------------------
,SUM (Case When A.IssDate between @p_FromDate and @p_toDate or (A.Fiscal_dt between @p_FromDate and @p_toDate ) Then ROUND(A.[AmtWithoutSA], 2 ) else 0 End) as [PrevYearRevenue]
,SUM (Case When A.IssDate between @fd and @TD or (A.Fiscal_dt between @fd and @TD ) Then ROUND(A.[AmtWithoutSA],2) else 0 End) as [ThisYearRevenue]
,ROUND( (Sum(Case When A.IssDate between @fd and @TD or (A.Fiscal_dt between @fd and @TD) Then ROUND(A.[AmtWithoutSA], 2 ) else 0 End) -
SUM (Case When A.IssDate between @p_FromDate and @p_toDate or (A.Fiscal_dt between @p_FromDate and @p_toDate) Then ROUND(A.[AmtWithoutSA], 2 ) else 0 End)),3) as [PeriodDifference]
,ROUND(Case when SUM (Case When A.IssDate between @p_FromDate and @p_toDate or (A.Fiscal_dt between @p_FromDate and @p_toDate) Then ROUND(A.[AmtWithoutSA], 2 ) else 0 End) = 0 then 0
else
((SUM (Case When A.IssDate between @fd and @TD or (A.Fiscal_dt between @fd and @TD) Then ROUND(A.[AmtWithoutSA], 2 ) else 0 End) -
SUM (Case When A.IssDate between @p_FromDate and @p_toDate or (A.Fiscal_dt between @p_FromDate and @p_toDate) Then ROUND(A.[AmtWithoutSA], 2 ) else 0 End)) /
SUM (Case When A.IssDate between @p_FromDate and @p_toDate or (A.Fiscal_dt between @p_FromDate and @p_toDate) Then ROUND(A.[AmtWithoutSA], 2 ) else 0 End) ) end,3) [PercentageDifference]
--------------------------------With Adjustment------------------------
,SUM(Case When a.amtwithSa <0 then a.amtwithSa else (Case When A.IssDate between @p_FromDate and @p_toDate or (A.Fiscal_dt between @p_FromDate and @p_toDate ) Then ROUND(A.[AmtWithSA], 2 ) else 0 End)end ) as [PrevYearRevenue2]
,SUM(Case When a.amtwithSa <0 then a.amtwithSa else (Case When A.IssDate between @fd and @TD or (A.Fiscal_dt between @fd and @TD) Then ROUND(A.[AmtWithSA],2) else 0 End) end )as [ThisYearRevenue2]
,(SUM(Case When a.amtwithSa <0 then a.amtwithSa else (Case When A.IssDate between @fd and @TD or (A.Fiscal_dt between @fd and @TD) Then ROUND(A.[AmtWithSA], 2 ) else 0 End)end )
-SUM(Case When a.amtwithSa <0 then a.amtwithSa else (Case When A.IssDate between @p_FromDate and @p_toDate or (A.Fiscal_dt between @p_FromDate and @p_toDate )Then ROUND(A.[AmtWithSA],2) else 0 End) end )
) as [PeriodDifference2]
,ROUND(Case when SUM (Case When A.IssDate between @p_FromDate and @p_toDate or (A.Fiscal_dt between @p_FromDate and @p_toDate) Then ROUND(A.[AmtWithSA], 2 ) else 0 End) = 0 then 0
else
((SUM(Case When a.amtwithSa <0 then a.amtwithSa else (Case When A.IssDate between @fd and @TD or (A.Fiscal_dt between @fd and @TD ) Then ROUND(A.[AmtWithSA], 2 ) else 0 End)end ) -
SUM (Case When A.IssDate between @p_FromDate and @p_toDate or (A.Fiscal_dt between @p_FromDate and @p_toDate) Then ROUND(A.[AmtWithSA], 2 ) else 0 End)) /
SUM (Case When A.IssDate between @p_FromDate and @p_toDate or (A.Fiscal_dt between @p_FromDate and @p_toDate) Then ROUND(A.[AmtWithSA], 2 ) else 0 End) ) end,3) [PercentageDifference2]--* 100
from
[dbo].[Revenue] A
left join [dbo].[Employees] B on A.EmpID = B.EmpID
left join dbo.lu_pub C on A.MediaID = C.pub_id
left join [dbo].[AccountRecord] D on A.CompanyID = D.AccountID
left join dbo.lu_media_group E on C.media_group_id = E.media_group_id
left join [dbo].[Media_Repo_grp] MRG ON C.pub_id = MRG.media_ID
left join dbo.ReportingGroups rpt on MRG.Repo_Grp_ID = rpt.ReportingGroupID
Where ((A.IssDate between @fd and @TD )
or (A.Fiscal_dt between @p_FromDate and @p_ToDAte ) )
and (A.EmpID in ('110'))
and (MRG.Repo_Grp_ID in ('1','20','18','19','8','6','3','11','17','14','9','16','7','15','5','2','10','13','21','25','26','24','22','23','27','4','12') or MRG.Master_Grp_ID in ('1','20','18','19','8','6','3','11','17','14','9','16','7','15','5','2','10','13','21','25','26','24','22','23','27','4','12')) ---@SingleQuote.Request.
Group BY
--A.CompanyID ---For Revenue by reporting Grp with Advertiser---Remove when using in revenue by Reporting Grp only--
--,D.Company ---For Revenue by reporting Grp with Advertiser---Remove when using in revenue by Reporting Grp only--
A.EmpID
,B.EmpName
,E.media_group_id
,E.media_group_desc
,C.pub_desc
--,rpt.Reporting_Grp_name
SQL Server Execution Times:
CPU time = 0 ms, elapsed time = 8 ms.
SQL Server Execution Times:
CPU time = 0 ms, elapsed time = 0 ms.
SQL Server Execution Times:
CPU time = 0 ms, elapsed time = 0 ms.
SQL Server Execution Times:
CPU time = 0 ms, elapsed time = 0 ms.
(34 row(s) affected)
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 '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 'lu_account_type'. Scan count 749, logical reads 749, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'tbl_master'. Scan count 749, logical reads 954975, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'lu_emp'. Scan count 3, logical reads 684, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'lu_ad_position'. Scan count 2, logical reads 112, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'lu_payroll_deadline'. Scan count 2, logical reads 56, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'lu_io_status'. Scan count 2, logical reads 56, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'lu_ad_type'. Scan count 2, logical reads 56, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'lu_pub_type'. Scan count 2, logical reads 56, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'lu_pub'. Scan count 56, logical reads 1008, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'lu_edition'. Scan count 55, logical reads 385, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'lu_ad_sect'. Scan count 55, logical reads 385, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'lu_adsize'. Scan count 55, logical reads 1210, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'lu_iss'. Scan count 56, logical reads 60424, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'tbl_io_detail'. Scan count 55, logical reads 992805, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'lu_ledger'. Scan count 54, logical reads 56, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'lu_accounting_period'. Scan count 54, logical reads 224, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'lu_subsidiary_ledger'. Scan count 54, logical reads 112, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'tbl_unapplied_credit'. Scan count 54, logical reads 1736, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'lu_ar_trans'. Scan count 54, logical reads 56, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'tbl_applied_credit'. Scan count 1153, logical reads 68027, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'tbl_bill_detail'. Scan count 1154, logical reads 4180942, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'tbl_io_split_commission'. Scan count 1154, logical reads 1494430, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'tbl_bill_register'. Scan count 1, logical reads 326, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'tbl_bill_invoice'. Scan count 1, logical reads 2354, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'lu_bill_register_type'. Scan count 1, logical reads 1, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'lu_media_group'. Scan count 1, logical reads 1, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'Media_Repo_grp'. Scan count 1, logical reads 3, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'ReportingGroups'. Scan count 1, logical reads 1, 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 = 38672 ms, elapsed time = 39034 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.
June 13, 2017 at 4:12 pm
That's a bit of a monster, especially since it looks like a lot of what's in your FROM clause are themselves very complicated views. On that note, the first takeaway is that the definitions of those views would be good for us to see.
Given how complicated the views seem to be, looking for tuning opportunities there is especially important, since at a certain point joining a bunch of complicated views that each join a decent number of tables can make it nearly impossible for the optimizer to come up with an efficient plan (as an aside, one option you may want to consider is writing the query "from scratch", only referencing the required tables, and not these monster views).
The second major takeaway is that it seems nearly every one of the many tables referenced by the query is a heap, which means you're doing full scans every time they're accessed. Figuring out some useful supporting indexing could prove very helpful.
What's more, without proper supporting indexes, the potential improvement for this query just by tinkering with the code is likely extremely limited.
Cheers!
June 14, 2017 at 8:14 am
Jacob, I just checked all the tables which are used in the query and they are pretty much all tables except Account record and Employee. The biggest table we have here has 200000 rows and pretty much more than half of the tables have less than 1000 records.
June 14, 2017 at 8:21 am
You are asking us to help with performance but we have no idea what your table structures and indexing is for any of this....although "There are multiple heap tables in this query." indicates that indexing is probably pretty sparse and likely not helpful. Start by getting some clustered indexes on these heaps.
Without details of the tables and such we can't really help much with performance. It is kind of like asking the mechanic how to make your car faster but you don't tell him what car you are driving, what engine you have etc.
Here is a great article of what is needed to help with performance problems. http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
June 14, 2017 at 8:28 am
Hmmm...you might want to check again.
Compare what's in your FROM clause to what the IO statistics and query plan say you're accessing.
The FROM clause shows these objects that are not listed as tables accessed in either the IO statistics or the execution plan:
dbo.Revenue
dbo.Employees
dbo.AccountRecord
Conversely, most of the tables shown as accessed, again both in IO statistics and the execution plan, are not named anywhere in the query you posted, so some of these objects in the FROM clause must be views.
Also, you seem to have confirmed already in your most recent post that Employees and AccountRecord are indeed views, so those definitions would be helpful.
Again, though, as I said initially and as Sean also pointed out, you really have to start with looking at putting some supporting indexing in place.
With literally every data access being a full table scan, the potential for improvement is going to be capped pretty severely.
Cheers!
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply