September 11, 2014 at 5:01 am
Experts,
I have an sql query am using which takes over 10minutes to draw data. it is divided into 3 sets each of which creates a temp table. When I run the first two set along both take less that 10 seconds. However on the 3rd set which combines the first two into one temp table is where the delay seems to come from. Kindly could someone look it up and tell me where the speed issue could be coming from ?
CREATE PROCEDURE [dbo].[SPA_PL_v2] --'2014-09-01','2014-01-30'
@FROMDATE datetime,
@TODATE datetime
AS
BEGIN
DECLARE @CY_START datetime
DECLARE @BEG_START datetime
DECLARE @START_QTRE datetime
DECLARE @END_QTRE datetime
DECLARE @account NVARCHAR (30)
SET @FROMDATE = DATEADD(HOUR, DATEPART(HOUR, @FROMDATE) * -1, @FROMDATE)
SET @FROMDATE = DATEADD(MINUTE, DATEPART(MINUTE, @FROMDATE) * -1, @FROMDATE)
SET @FROMDATE = DATEADD(SECOND, DATEPART(SECOND, @FROMDATE) * -1, @FROMDATE)
SET @FROMDATE = DATEADD(MILLISECOND, DATEPART(MILLISECOND, @FROMDATE) * -1, @FROMDATE)
SET @TODATE = DATEADD(HOUR, DATEPART(HOUR, @TODATE) * -1, @TODATE)
SET @TODATE = DATEADD(MINUTE, DATEPART(MINUTE, @TODATE) * -1, @TODATE)
SET @TODATE = DATEADD(SECOND, DATEPART(SECOND, @TODATE) * -1, @TODATE)
SET @TODATE = DATEADD(MILLISECOND, DATEPART(MILLISECOND, @TODATE) * -1, @TODATE)
SET @CY_START = (CAST(YEAR(@FROMDATE)as nvarchar(4)) + '-01-01')
SET @BEG_START = (CAST(YEAR(@FROMDATE)-1 as nvarchar(4)) + '-01-01')
-- first day of the quater
SET @START_QTRE = (SELECT DATEADD(qq, DATEDIFF(qq, 0, @FROMDATE), 0))
-- last day of the quater
SET @END_QTRE = (SELECT DATEADD (dd, -1, DATEADD(qq, DATEDIFF(qq, 0,@FROMDATE) +1, 0)))
CREATE table #MainAccount
(
[AccountID] nVarchar(100) ,
[AcctCode]nVarchar(100),
[AcctName]nVarchar(100),
[Segment0] int ,
[Segment1] nvarchar(100) ,
[SegmentName1] nVarchar(100),
[SegmentShortName1]nVarchar(100),
[Segment2] nVarchar(100) ,
[SegmentName2] nVarchar(100),
[SegmentShortName2]nVarchar(100),
[AcctType] nVarchar(100),
[Fathernum] numeric (20) ,
[Levels]nVarchar(100),
[Level1] nVarchar(100) ,
[Level1Name] nVarchar(100),
[Level1FrgnName] nVarchar(100),
[Level2] nVarchar(100),
[Level2Name] nVarchar(100),
[Level2FrgnName]nVarchar(100),
[Level3] nVarchar(100),
[Level3Name] nVarchar(100),
[Level3FrgnName] nVarchar(100),
[Level4] nVarchar(100),
[Level4Name] nVarchar(100),
[Level4FrgnName] nVarchar(100),
[Level5FrgnName] nVarchar(100),
[Level5] nVarchar(100),
[Level5Name] nVarchar(100),
[Level5FrgName] nVarchar(100) ,
[PeriodStartdate]datetime,
[DateMonth] varchar(20),
[ActualCredit] numeric(19,6),
[ActualDebit] numeric(19,6),
[ActualAmount] numeric(19,6),
[Refdate] datetime,
[profitcode] nVarchar(100) ,
[ocrname]nVarchar(100) ,
[Group1] nVarchar(100),
[Group2] nVarchar(100)
)
insert into #MainAccount
(
[AccountID] ,
[AcctCode],
[AcctName],
[Segment0] ,
[Segment1] ,
[SegmentName1] ,
[SegmentShortName1],
[Segment2] ,
[SegmentName2] ,
[SegmentShortName2],
[AcctType] ,
[Fathernum],
[Levels],
[Level1] ,
[Level1Name] ,
[Level1FrgnName] ,
[Level2] ,
[Level2Name] ,
[Level2FrgnName],
[Level3] ,
[Level3Name] ,
[Level3FrgnName] ,
[Level4] ,
[Level4Name] ,
[Level4FrgnName] ,
[Level5] ,
[Level5Name] ,
[Level5FrgnName] ,
[PeriodStartdate],
[DateMonth],
[ActualCredit] ,
[ActualDebit] ,
[ActualAmount] ,
[Refdate] ,
[profitcode] ,
[ocrname] ,
[Group1],
[Group2]
)
select
a.AcctCode AS AccountID,
case
when (a.Segment_0 IS NOT NULL) then
RTRIM(ISNULL(a.Segment_0,'') +' '+ ISNULL(a.Segment_1,'') +' '+ ISNULL(a.Segment_2,'') +' '+ ISNULL(a.Segment_3,''))
else
a.AcctCode
end as AcctCode,
a.AcctName,
ISNULL(a.Segment_0,'') as Segment0,
ISNULL(a.Segment_1,'') as Segment1,
c.Name as SegmentName1,
c.ShortName as SegmentShortName1,
ISNULL(a.Segment_2,'') as Segment2,
d.Name as SegmentName2,
d.ShortName as SegmentShortName2,
a.ActType,
a.FatherNum,
a.Levels,
b.Level1 as Level1,
b.Level1Name as Level1Name,
b.Level1FrgnName as Level1FrgnName,
(SELECT n.Level2 FROM ixvAccLev n WHERE n.Level1 <> '600000000000000' and n.AcctCode = a.AcctCode )AS 'Level2',
--b.Level2 as Level2,
b.Level2Name as Level2Name,
b.Level2FrgnName as Level2FrgnName,
(SELECT n.Level3 FROM ixvAccLev n WHERE n.Level1 <> '600000000000000' and n.AcctCode = a.AcctCode )AS 'Level3',
--b.Level3 as Level3,
b.Level3Name as Level3Name,
b.Level3FrgnName as Level3FrgnName,
(SELECT n.Level4 FROM ixvAccLev n WHERE n.Level1 <> '600000000000000' and n.AcctCode = a.AcctCode )AS 'Level4',
--b.Level4 as Level4,
b.Level4Name as Level4Name,
b.Level4FrgnName as Level4FrgnName,
b.Level5 as Level5,
b.Level5Name as Level5Name,
b.Level5FrgnName as Level5FrgnName,
DATEADD(DAY,-(DAY(f.RefDate) - 1),f.RefDate) AS PeriodStartDate,
MONTH (DATEADD(DAY,-(DAY(f.RefDate) - 1),f.RefDate)) AS DateMonth,
SUM(f.Credit) AS ActualCredit,
SUM(f.Debit) AS ActualDebit,
(SUM( f.Debit) - sum(f.Credit )) AS ActualAmount,
g.RefDate AS Refdate,
f.Profitcode,
h.OcrName,
a.U_Group1,
a.U_Group2
from dbo.OACT a
LEFT OUTER JOIN ixvAccLev b on a.AcctCode=b.AcctCode
LEFT OUTER JOIN OASC c on a.Segment_1=c.Code and c.SegmentId=1
LEFT OUTER JOIN OASC d on a.Segment_2=d.Code and d.SegmentId=2
LEFT OUTER JOIN OASC e on a.Segment_3=e.Code and e.SegmentId=3
LEFT OUTER JOIN JDT1 f on a.AcctCode = f.Account
LEFT OUTER JOIN OJDT g on f.TransId = g.TransId
LEFT outer join OOCR h on f.ProfitCode = h.OcrCode
where (f.RefDate >= @BEG_START and f.RefDate <= @TODATE) and b.Level1 >= '400000000000000'
GROUP BY a.AcctCode, a.AcctName, a.Segment_0, a.Segment_1,
a.Segment_2, a.Segment_3, c.Name, c.ShortName,
d.Name, d.ShortName, e.Name, e.ShortName, a.ActType,
a.FatherNum, a.Levels, b.Level1, b.Level1Name, b.Level1FrgnName,a.U_Group1,a.U_Group2,
b.Level2, b.Level2Name, b.Level2FrgnName,
b.Level3, b.Level3Name, b.Level3FrgnName,
b.Level4, b.Level4Name, b.Level4FrgnName,
b.Level5, b.Level5Name, b.Level5FrgnName,
f.RefDate,g.RefDate,
f.Profitcode,
h.OcrName
ORDER BY b.Level1,b.Level2,b.Level3,b.Level4,b.Level5
----------------------------------------------------------------------------------------------------------------------------
CREATE table #BudgetAccount
(
[AccountID] nVarchar(100),
[AcctCode] nVarchar(100),
[AcctName] nVarchar(100),
[Segment0] int ,
[Segment1] int ,
[SegmentName1] nVarchar(100),
[SegmentShortName]nVarchar(100),
[Segment2] nVarchar(100) ,
[SegmentName2 ] nVarchar(100),
[SegmentShortName2]nVarchar(100),
[AcctType] nVarchar(100),
[Fathernum] numeric (20) ,
[Levels]nVarchar(100),
[Level1] nVarchar(100) ,
[Level1Name] nVarchar(100),
[Level1FrgnName] nVarchar(100),
[Level2] nVarchar(100),
[Level2Name] nVarchar(100),
[Level2FrgnName]nVarchar(100),
[Level3] nVarchar(100),
[Level3Name] nVarchar(100),
[Level3FrgnName] nVarchar(100),
[Level4] nVarchar(100),
[Level4Name] nVarchar(100),
[Level5FrgnName] nVarchar(100),
[Level5] nVarchar(100),
[Level5Name] nVarchar(100),
[Level5FrgName] nVarchar(100) ,
[PeriodStartdate] datetime ,
[DATEMONTH] varchar(20),
[BudgetCredit] numeric(19,6),
[BudgetDebit] numeric(19,6),
[BudgetAmount] numeric(19,6),
[BudgetName] nVarchar (100),
[BudgetPCentre] nVarchar (20)
)
insert into #BudgetAccount
(
[AccountID],
[AcctCode],
[AcctName],
[Segment0] ,
[Segment1] ,
[SegmentName1] ,
[SegmentShortName],
[Segment2] ,
[SegmentName2 ] ,
[SegmentShortName2],
[AcctType],
[Fathernum] ,
[Levels],
[Level1] ,
[Level1Name] ,
[Level1FrgnName] ,
[Level2] ,
[Level2Name] ,
[Level2FrgnName],
[Level3] ,
[Level3Name] ,
[Level3FrgnName] ,
[Level4] ,
[Level4Name] ,
[Level5FrgnName] ,
[Level5] ,
[Level5Name] ,
[Level5FrgName] ,
[PeriodStartdate] ,
[DATEMONTH] ,
[BudgetCredit] ,
[BudgetDebit] ,
[BudgetAmount],
[BudgetName],
[BudgetPCentre]
)
SELECT a.AcctCode AS AccountID,
CASE WHEN (a.Segment_0 IS NOT NULL) THEN RTRIM(ISNULL(a.Segment_0, '') + ' ' + ISNULL(a.Segment_1, '') + ' ' +
ISNULL(a.Segment_2,'') + ' ' + ISNULL(a.Segment_3, '')) ELSE a.AcctCode END AS AcctCode,
a.AcctName, ISNULL(a.Segment_0, '') AS Segment0,
ISNULL(a.Segment_1, '') AS Segment1,
c.Name AS SegmentName1, c.ShortName AS SegmentShortName1,
ISNULL(a.Segment_2, '') AS Segment2,
d.Name AS SegmentName2,
d.ShortName AS SegmentShortName2,
a.ActType,
a.FatherNum,
a.Levels,
b.Level1,
b.Level1Name,
b.Level1FrgnName,
b.Level2,
b.Level2Name,
b.Level2FrgnName,
b.Level3,
b.Level3Name,
b.Level3FrgnName,
b.Level4,
b.Level4Name,
b.Level4FrgnName,
b.Level5,
b.Level5Name,
b.Level5FrgnName,
DATEADD(MONTH, f.Line_ID,g.FinancYear) AS PeriodStartDate,
MONTH(DATEADD(MONTH, f.Line_ID, g.FinancYear)) AS DATEMONTH,
SUM(f.CredLTotal) AS BudgetCredit,
SUM(f.DebLTotal) AS BudgetDebit,
SUM(f.DebLTotal - f.CredLTotal) AS BudgetAmount,
dbo.OBGS.Name AS BudgetName,
dbo.OBGS.OcrCode AS BudgetPCentre
FROM dbo.OBGS INNER JOIN
dbo.OBGT AS g ON dbo.OBGS.AbsId = g.Instance RIGHT OUTER JOIN
dbo.OACT AS a LEFT OUTER JOIN
dbo.ixvAccLev AS b ON a.AcctCode = b.AcctCode LEFT OUTER JOIN
dbo.OASC AS c ON a.Segment_1 = c.Code AND c.SegmentId = 1 LEFT OUTER JOIN
dbo.OASC AS d ON a.Segment_2 = d.Code AND d.SegmentId = 2 LEFT OUTER JOIN
dbo.OASC AS e ON a.Segment_3 = e.Code AND e.SegmentId = 3 LEFT OUTER JOIN
dbo.BGT1 AS f ON a.AcctCode = f.AcctCode ON g.AbsId = f.BudgId
WHERE (a.Segment_0 <> '') and b.Level1 >= '400000000000000' AND dbo.OBGS.AbsId in (5,6)
GROUP BY a.AcctCode, a.AcctName, a.Segment_0, a.Segment_1, a.Segment_2,
a.Segment_3, c.Name, c.ShortName, d.Name, d.ShortName, e.Name, e.ShortName, a.ActType,
a.FatherNum, a.Levels, b.Level1, b.Level1Name, b.Level1FrgnName, b.Level2,
b.Level2Name, b.Level2FrgnName, b.Level3, b.Level3Name, b.Level3FrgnName,
b.Level4, b.Level4Name, b.Level4FrgnName,
b.Level5, b.Level5Name, b.Level5FrgnName,
f.BudgId, f.Line_ID, g.FinancYear, dbo.OBGS.Name,
dbo.OBGS.OcrCode
ORDER BY b.Level1,b.Level2,b.Level3,b.Level4,b.Level5
Create table #Display
(
[AccountID] nVarchar(100) ,
[AcctCode]nVarchar(100),
[AcctName]nVarchar(100),
[Segment0] int ,
[Segment1] nvarchar(100) ,
[SegmentName1] nVarchar(100),
[SegmentShortName1]nVarchar(100),
[Segment2] nVarchar(100) ,
[SegmentName2] nVarchar(100),
[SegmentShortName2]nVarchar(100),
[AcctType] nVarchar(100),
[Fathernum] numeric (20) ,
[Levels]nVarchar(100),
[Level1] nVarchar(100) ,
[Level1Name] nVarchar(100),
[Level1FrgnName] nVarchar(100),
[Level2] nVarchar(100),
[Level2Name] nVarchar(100),
[Level2FrgnName]nVarchar(100),
[Level3] nVarchar(100),
[Level3Name] nVarchar(100),
[Level3FrgnName] nVarchar(100),
[Level4] nVarchar(100),
[Level4Name] nVarchar(100),
[Level4FrgnName] nVarchar(100),
[Level5FrgnName] nVarchar(100),
[Level5] nVarchar(100),
[Level5Name] nVarchar(100),
[Level5FrgName] nVarchar(100) ,
[PeriodStartdate]datetime,
[DateMonth] varchar(20),
[ActualAmount] numeric(19,6),
[Refdate] datetime,
[profitcode] nVarchar(100) ,
[ocrname]nVarchar(100) ,
--[op] numeric(19,6),
[Group1] nVarchar (100),
[Group2] nVarchar (100),
[BudgetCredit] numeric(20),
[BudgetDebit] numeric(19,6),
[BudgetAmount] numeric(19,6),
[BudgetName] nVarchar (100),
[BudgetPCentre] nVarchar (20),
[Actual_CurrentMonth] numeric(19,6),
[Actual_CurrentQtr] numeric (19,6),
[Actual_Currentyear] numeric(19,6),
[Budget_CurrentMonth] numeric(19,6),
[Budget_CurrentQtr] numeric (19,6),
[Budget_Currentyear] numeric(19,6),
[Credit] numeric(19,6),
[Debit] numeric(19,6),
[PL] numeric(19,6),
[YTDPL] numeric (19,6),
--[BS] numeric(19,6),
--[MONTHBS]numeric(19,6),
[Actuals] numeric(19,6),
--[TB] numeric(19,6),
[Actuals_Year] numeric(19,6),
--[YTD_BSAccounts]numeric(19,6),
[Actuals_Depot]numeric(19,6),
[Actuals_Year_Depot]numeric(19,6)
)
insert into #Display
(
[AccountID],
[AcctCode],
[AcctName],
[Segment0],
[SegmentName1],
[SegmentShortName1],
[Segment2],
[SegmentName2],
[SegmentShortName2],
[AcctType],
[FatherNum],
[Levels],
[Level1],
[Level1Name],
[Level1FrgnName],
[Level2],
[Level2Name],
[Level2FrgnName],
[Level3],
[Level3Name],
[Level3FrgnName],
[Level4],
[Level4Name],
[Level4FrgnName],
[Level5],
[Level5Name],
[Level5FrgnName],
[PeriodStartDate],
[DateMonth],
[ActualAmount],
[Refdate],
[OcrName],
--[op],
[Group1],
[Group2],
[Segment1],
[Profitcode],
[BudgetPCentre],
[BudgetCredit],
[BudgetDebit],
[BudgetName],
[BudgetAmount],
[Actual_CurrentMonth],
[Actual_CurrentQtr],
[Actual_Currentyear],
[Budget_CurrentMonth],
[Budget_CurrentQtr],
[Budget_Currentyear],
[Credit],
[Debit],
[PL],
[YTDPL],
[Actuals],
[Actuals_Year],
[Actuals_Depot],
[Actuals_Year_Depot]
)
SELECT
aa.[AccountID],
aa.[AcctCode],
aa.[AcctName],
aa.[Segment0],
aa.[SegmentName1],
aa.[SegmentShortName1],
aa.[Segment2],
aa.[SegmentName2],
aa.[SegmentShortName2],
aa.[AcctType],
aa.[FatherNum],
aa.[Levels],
aa.[Level1],
aa.[Level1Name],
aa.[Level1FrgnName],
aa.[Level2] ,
aa.[Level2Name],
aa.[Level2FrgnName],
aa.[Level3],
aa.[Level3Name],
aa.[Level3FrgnName],
aa.[Level4],
aa.[Level4Name],
aa.[Level4FrgnName],
aa.[Level5],
aa.[Level5Name],
aa.[Level5FrgnName],
aa.[PeriodStartDate],
aa.[DateMonth],
aa.[ActualAmount],
aa.[Refdate],
aa.[OcrName],
--aa.[op],
aa.[Group2],
aa.[Group1],
aa.[Segment1],
aa.[Profitcode],
bb.[BudgetPCentre],
bb.[BudgetCredit],
bb.[BudgetDebit],
bb.[BudgetName],
bb.[BudgetAmount],
isnull((select (SUM(a1.ActualDebit) - SUM(a1.ActualCredit) ) from #MainAccount a1 where a1.AccountID = aa.AccountID
and a1.Refdate >= @FROMDATE and a1.Refdate <= @TODATE),0)
As Actual_CurrentMonth,
isnull((select (SUM(a1.ActualDebit) - SUM(a1.ActualCredit) ) from #MainAccount a1 where a1.AccountID = aa.AccountID
and a1.Refdate >= @START_QTRE and a1.Refdate <= @END_QTRE),0)
As Actual_CurrentQtr,
isnull((select (SUM(a1.ActualDebit) - SUM(a1.ActualCredit) ) from #MainAccount a1 where a1.AccountID =
aa.AccountID and a1.Refdate
between @CY_START and @TODATE ),0)As Actual_Currentyear,
isnull((select (SUM(b1.BudgetDebit) - SUM(b1.BudgetCredit) ) from #BudgetAccount b1 where b1.AccountID = bb.AccountID and b1.PeriodStartdate
between @FROMDATE and @TODATE ),0)As Budget_CurrentMonth,
isnull((select (SUM(b1.BudgetDebit) - SUM(b1.BudgetCredit) ) from #BudgetAccount b1 where b1.AccountID = bb.AccountID and b1.PeriodStartdate
between @START_QTRE and @END_QTRE ),0)As Budget_CurrentQtr,
isnull((select (SUM(b1.BudgetDebit) - SUM(b1.BudgetCredit) ) from #BudgetAccount b1 where b1.AccountID = aa.AccountID
and b1.PeriodStartdate between @CY_START and @TODATE ),0)As Budget_CurrentYear,
isnull((select (SUM(a1.actualCredit)) from #MainAccount a1 where a1.AccountID = aa.AccountID and a1.Refdate
between @FROMDATE and @TODATE ),0) As Credit,
isnull((select (SUM(a1.actualDebit)) from #MainAccount a1 where a1.AccountID = aa.AccountID and a1.Refdate
between @FROMDATE and @TODATE ),0) As Debit,
isnull((select (SUM(a1.ActualCredit) - SUM(a1.ActualDebit)) from #MainAccount a1 where a1.Refdate >= @FROMDATE
and a1.Refdate <= @TODATE
and (a1.Level1 >= '400000000000000')),0)
As PL,
isnull((select (SUM(a1.ActualCredit) - SUM(a1.ActualDebit)) from #MainAccount a1 where a1.Refdate >= @CY_START
and a1.Refdate <= @TODATE
and (a1.Level1 >= '400000000000000')),0)
As YTDPL,
isnull((select (sum (a1.ActualAmount)) from #MainAccount a1 WHERE a1.Segment1 = aa.Segment1
and a1.profitcode = aa.profitcode and a1.AccountID = aa.AccountID AND
a1.Refdate >= @FROMDATE and a1.Refdate <= @TODATE),0)
As Actuals,
isnull((select (sum (a1.ActualAmount)) from #MainAccount a1 WHERE a1.Segment1 = aa.Segment1
and a1.profitcode = aa.profitcode and a1.AccountID = aa.AccountID AND a1.ActualAmount <> 0 AND
a1.Refdate >= @CY_START and a1.Refdate <= @TODATE),0)
As Actuals_Year,
isnull((select (sum (a1.ActualAmount)) from #MainAccount a1 WHERE a1.Segment1 = aa.Segment1
and a1.AccountID = aa.AccountID and
a1.Refdate >= @FROMDATE and a1.Refdate <= @TODATE),0)
As Actuals_Depot,
isnull((select (sum (a1.ActualAmount)) from #MainAccount a1 WHERE a1.Segment1 = aa.Segment1
and a1.AccountID = aa.AccountID AND
a1.Refdate >= @CY_START and a1.Refdate <= @TODATE),0)
As Actuals_Year_Depot
FROM #MainAccount aa LEFT OUTER JOIN
#BudgetAccount bb ON aa.AccountID = bb.AccountID
--AND aa.Profitcode = bb.BudgetPCentre
aND
aa.DateMonth = bb.DATEMONTH AND aa.PeriodStartDate = bb.PeriodStartDate
GROUP BY bb.BudgetDebit, bb.BudgetName, bb.BudgetPCentre,bb.AccountID, aa.AccountID, aa.AcctCode,
aa.AcctName,
aa.Segment0, aa.Segment1, aa.SegmentName1, aa.SegmentShortName1, aa.Segment2,
aa.SegmentName2, aa.SegmentShortName2, aa.AcctType, aa.FatherNum, aa.Levels, aa.Level1,
aa.Level1Name, aa.Level1FrgnName, aa.Level2, aa.Level2Name, aa.Level2FrgnName, aa.Level3,
aa.Level3Name, aa.Level3FrgnName, aa.Level4, aa.Level4Name, aa.Level4FrgnName, aa.Level5,
aa.Level5Name, aa.Level5FrgnName, aa.PeriodStartDate,aa.DateMonth, aa.ActualCredit,
aa.ActualDebit, aa.ActualAmount, aa.Refdate, aa.Profitcode, aa.OcrName, bb.BudgetCredit, bb.BudgetAmount,aa.Group1,aa.Group2
ORDER BY aa.Level1,aa.Level2,aa.Level3,aa.Level4,aa.Level5,aa.AcctCode ASC
select * from #Display
drop table #BudgetAccount
drop table #MainAccount
drop table #Display
End
September 11, 2014 at 5:12 am
That is 600 lines of code... When I do tuning for clients, that size of procedure would probably take me half a day to work through.
Can you do some investigation, work out which portion of that code, which queries are specifically the problem, then post just the problematic portions along with table definitions, index definitions and an actual execution plan of the problematic section?
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
September 12, 2014 at 8:56 am
Really, really, really need the execution plan to understand how things are behaving within your environment. Without that, it's all guesses. But, all these correlated sub-queries:
select (SUM(a1.ActualDebit) - SUM(a1.ActualCredit) ) from #MainAccount a1 where a1.AccountID = aa.AccountID
and a1.Refdate >= @FROMDATE and a1.Refdate <= @TODATE)
Are usually an indication of a problem. It does depend on how the optimizer chooses to resolve them, but, you could see some seriously poor choices there. I'd suggest a JOIN to the table and then reference the columns as needed instead of what you have here.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
September 12, 2014 at 9:44 am
-- this would help as Grant suggested, however refactoring the whole query
-- to work as a cross-tab would be far more efficient.
SELECT
aa.[AccountID],
aa.[AcctCode],
aa.[AcctName],
aa.[Segment0],
aa.[SegmentName1],
aa.[SegmentShortName1],
aa.[Segment2],
aa.[SegmentName2],
aa.[SegmentShortName2],
aa.[AcctType],
aa.[FatherNum],
aa.[Levels],
aa.[Level1],
aa.[Level1Name],
aa.[Level1FrgnName],
aa.[Level2] ,
aa.[Level2Name],
aa.[Level2FrgnName],
aa.[Level3],
aa.[Level3Name],
aa.[Level3FrgnName],
aa.[Level4],
aa.[Level4Name],
aa.[Level4FrgnName],
aa.[Level5],
aa.[Level5Name],
aa.[Level5FrgnName],
aa.[PeriodStartDate],
aa.[DateMonth],
aa.[ActualAmount],
aa.[Refdate],
aa.[OcrName],
--aa.[op],
aa.[Group2],
aa.[Group1],
aa.[Segment1],
aa.[Profitcode],
bb.[BudgetPCentre],
bb.[BudgetCredit],
bb.[BudgetDebit],
bb.[BudgetName],
bb.[BudgetAmount],
x1.Actual_CurrentMonth,
x1.Actual_CurrentQtr,
x1.Actual_Currentyear,
x1.Credit,
x1.Debit,
x2.Budget_CurrentMonth,
x2.Budget_CurrentQtr,
x2.Budget_CurrentYear,
x3.PL,
x3.YTDPL,
x4.Actuals,
x4.Actuals_Year,
x5.Actuals_Depot,
x5.Actuals_Year_Depot
FROM #MainAccount aa
LEFT OUTER JOIN #BudgetAccount bb
ON aa.AccountID = bb.AccountID
--AND aa.Profitcode = bb.BudgetPCentre
aND aa.DateMonth = bb.DATEMONTH
AND aa.PeriodStartDate = bb.PeriodStartDate
CROSS APPLY (
SELECT
Actual_CurrentMonth = SUM(CASE WHEN a1.Refdate >= @FROMDATE AND a1.Refdate <= @TODATE THEN a1.ActualDebit-a1.ActualCredit ELSE 0 END),
Actual_CurrentQtr = SUM(CASE WHEN a1.Refdate >= @START_QTRE AND a1.Refdate <= @END_QTRE THEN a1.ActualDebit-a1.ActualCredit ELSE 0 END),
Actual_Currentyear = SUM(CASE WHEN a1.Refdate BETWEEN @CY_START AND @TODATE THEN a1.ActualDebit-a1.ActualCredit ELSE 0 END),
Credit = SUM(CASE WHEN a1.Refdate BETWEEN @FROMDATE AND @TODATE THEN a1.actualCredit ELSE 0 END),
Debit = SUM(CASE WHEN a1.Refdate BETWEEN @FROMDATE AND @TODATE THEN a1.actualDebit ELSE 0 END)
FROM #MainAccount a1
WHERE a1.AccountID = aa.AccountID
) x1
CROSS APPLY (
SELECT
Budget_CurrentMonth = SUM(CASE WHEN b1.PeriodStartdate BETWEEN @FROMDATE AND @TODATE THEN b1.BudgetDebit - b1.BudgetCredit ELSE 0 END),
Budget_CurrentQtr = SUM(CASE WHEN b1.PeriodStartdate BETWEEN @START_QTRE AND @END_QTRE THEN b1.BudgetDebit - b1.BudgetCredit ELSE 0 END),
Budget_CurrentYear = SUM(CASE WHEN b1.PeriodStartdate BETWEEN @CY_START AND @TODATE THEN b1.BudgetDebit - b1.BudgetCredit ELSE 0 END)
FROM #BudgetAccount b1
WHERE b1.AccountID = bb.AccountID
) x2
CROSS APPLY (
SELECT
PL = SUM(CASE WHEN a1.Refdate >= @FROMDATE and a1.Refdate <= @TODATE and (a1.Level1 >= '400000000000000') THEN a1.ActualCredit - a1.ActualDebit ELSE 0 END),
YTDPL = SUM(CASE WHEN a1.Refdate >= @CY_START and a1.Refdate <= @TODATE and (a1.Level1 >= '400000000000000') THEN a1.ActualCredit - a1.ActualDebit ELSE 0 END)
FROM #MainAccount a1
) x3
CROSS APPLY (
SELECT
Actuals = SUM(CASE WHEN a1.Refdate >= @FROMDATE and a1.Refdate <= @TODATE THEN a1.ActualAmount ELSE 0 END),
Actuals_Year = SUM(CASE WHEN a1.ActualAmount <> 0 AND a1.Refdate >= @CY_START and a1.Refdate <= @TODATE THEN a1.ActualAmount ELSE 0 END)
FROM #MainAccount a1
WHERE a1.Segment1 = aa.Segment1
AND a1.profitcode = aa.profitcode
AND a1.AccountID = aa.AccountID
) x4
CROSS APPLY (
SELECT
Actuals_Depot = SUM(CASE WHEN a1.Refdate >= @FROMDATE and a1.Refdate <= @TODATE THEN a1.ActualAmount ELSE 0 END),
Actuals_Year_Depot = SUM(CASE WHEN a1.Refdate >= @CY_START and a1.Refdate <= @TODATE THEN a1.ActualAmount ELSE 0 END)
FROM #MainAccount a1
WHERE a1.Segment1 = aa.Segment1
AND a1.AccountID = aa.AccountID
) x5
GROUP BY bb.BudgetDebit, bb.BudgetName, bb.BudgetPCentre,bb.AccountID, aa.AccountID, aa.AcctCode,
aa.AcctName,
aa.Segment0, aa.Segment1, aa.SegmentName1, aa.SegmentShortName1, aa.Segment2,
aa.SegmentName2, aa.SegmentShortName2, aa.AcctType, aa.FatherNum, aa.Levels, aa.Level1,
aa.Level1Name, aa.Level1FrgnName, aa.Level2, aa.Level2Name, aa.Level2FrgnName, aa.Level3,
aa.Level3Name, aa.Level3FrgnName, aa.Level4, aa.Level4Name, aa.Level4FrgnName, aa.Level5,
aa.Level5Name, aa.Level5FrgnName, aa.PeriodStartDate,aa.DateMonth, aa.ActualCredit,
aa.ActualDebit, aa.ActualAmount, aa.Refdate, aa.Profitcode, aa.OcrName, bb.BudgetCredit, bb.BudgetAmount,aa.Group1,aa.Group2
ORDER BY aa.Level1, aa.Level2, aa.Level3, aa.Level4, aa.Level5, aa.AcctCode ASC
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
September 12, 2014 at 9:57 am
This won't help with performance, but it will save you coding time.
You can change this:
DECLARE @FROMDATE DATETIME = GETDATE()
SET @FROMDATE = DATEADD(HOUR, DATEPART(HOUR, @FROMDATE) * - 1, @FROMDATE)
SET @FROMDATE = DATEADD(MINUTE, DATEPART(MINUTE, @FROMDATE) * - 1, @FROMDATE)
SET @FROMDATE = DATEADD(SECOND, DATEPART(SECOND, @FROMDATE) * - 1, @FROMDATE)
SET @FROMDATE = DATEADD(MILLISECOND, DATEPART(MILLISECOND, @FROMDATE) * - 1, @FROMDATE)
SELECT @FROMDATE
GO
To this:
DECLARE @FROMDATE DATETIME = GETDATE()
SET @FROMDATE = DATEADD(DD, DATEDIFF(DD, 0, @FROMDATE),0)
SELECT @FROMDATE
That will work for years, months, quarters and other date parts.
Reference: http://www.sqlservercentral.com/blogs/lynnpettis/2009/03/25/some-common-date-routines/
September 13, 2014 at 4:21 am
Thanks Grant. That really does help a lot in speeding up.
September 13, 2014 at 4:22 am
Grant,
What do you mean by execution plan ?
September 13, 2014 at 4:25 am
This is mind blowing......
I also realised also when I removed columns aa.refdate from the group by section it really did help a lot. The query mainly produces summed up data
September 13, 2014 at 4:26 am
Thanks Luis
September 13, 2014 at 5:07 am
SQL DJ (9/13/2014)
What do you mean by execution plan ?
The graphical execution plan which management studio can show you, the 'recipe' if you will of how the query is executed.
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
September 13, 2014 at 6:09 am
SQL DJ (9/13/2014)
Grant,What do you mean by execution plan ?
Gail's answer is good. You can also follow the links in my signature to my books on query tuning and execution plans.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
September 13, 2014 at 7:59 am
Grant,
I have made the modification as below. It gives me duplicates...how can I remove these ?
isnull((select distinct (SUM(a1.ActualDebit) - SUM(a1.ActualCredit) ) from #MainAccount a1
LEFT OUTER JOIN
#BudgetAccount bb ON aa.AccountID = bb.AccountID AND a1.PeriodStartdate = bb.PeriodStartDate and
a1.DateMonth=bb.DateMonth and a1.Budget = bb.Budget WHERE a1.AccountID = aa.AccountID and
a1.Refdate >= @FROMDATE and a1.Refdate <= @TODATE GROUP BY a1.group1, a1.Group2),0)
As Actual_CurrentMonth,
September 13, 2014 at 9:19 am
SQL DJ (9/13/2014)
Grant,I have made the modification as below. It gives me duplicates...how can I remove these ?
isnull((select distinct (SUM(a1.ActualDebit) - SUM(a1.ActualCredit) ) from #MainAccount a1
LEFT OUTER JOIN
#BudgetAccount bb ON aa.AccountID = bb.AccountID AND a1.PeriodStartdate = bb.PeriodStartDate and
a1.DateMonth=bb.DateMonth and a1.Budget = bb.Budget WHERE a1.AccountID = aa.AccountID and
a1.Refdate >= @FROMDATE and a1.Refdate <= @TODATE GROUP BY a1.group1, a1.Group2),0)
As Actual_CurrentMonth,
I'm not sure. You'll need to check the data and validate all your criteria is correct. You do know that DISTINCT is an aggregation mechanism and can lead to performance problems to though, right? Frequently that's put in place when the data is bad, the structures are problematic, or there's logic issues with the query. I would focus on addressing those things rather than use DISTINCT to prop the query up.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
September 15, 2014 at 2:56 pm
That GROUP BY is gonna kill your performance. See if the code below is close to what you need.
I've added clustered indexes on the temp tables prior to loading to hopefully help the performance of the later query.
If you see "dups", then I think your joins are wrong. If you only want to report one row per month, which is implied in some of what and some parts of the code but not actually fully written into the code, let us know so we can adjust the code to do that.
DECLARE @force_unique_index_name varchar(32)
DECLARE @sql varchar(4000)
CREATE table #MainAccount
(
AccountID int
--...
)
SET @force_unique_index_name = REPLACE(NEWID(), '-', '')
SET @sql = 'CREATE CLUSTERED INDEX MainAccount__CL_' + @force_unique_index_name + ' ( AccountId, Refdate ); '
EXEC(@sql)
--INSERT INTO #MainAccount
--...
--...
CREATE table #BudgetAccount
(
AccountID int
--...
)
SET @force_unique_index_name = REPLACE(NEWID(), '-', '')
SET @sql = 'CREATE CLUSTERED INDEX BudgetAccount__CL_' + @force_unique_index_name + ' ( AccountId, Refdate ); '
EXEC(@sql)
--...
SELECT
main_accountid.[AccountID],
ma.[AcctCode],
ma.[AcctName],
ma.[Segment0],
ma.[SegmentName1],
ma.[SegmentShortName1],
ma.[Segment2],
ma.[SegmentName2],
ma.[SegmentShortName2],
ma.[AcctType],
ma.[FatherNum],
ma.[Levels],
ma.[Level1],
ma.[Level1Name],
ma.[Level1FrgnName],
ma.[Level2] ,
ma.[Level2Name],
ma.[Level2FrgnName],
ma.[Level3],
ma.[Level3Name],
ma.[Level3FrgnName],
ma.[Level4],
ma.[Level4Name],
ma.[Level4FrgnName],
ma.[Level5],
ma.[Level5Name],
ma.[Level5FrgnName],
ma.[PeriodStartDate],
ma.[DateMonth],
ma.[ActualAmount],
ma.[Refdate],
ma.[OcrName],
--ma.[op],
ma.[Group2],
ma.[Group1],
ma.[Segment1],
ma.[Profitcode],
ba.[BudgetPCentre],
ba.[BudgetCredit],
ba.[BudgetDebit],
ba.[BudgetName],
ba.[BudgetAmount],
Actual_CurrentMonth,
Actual_CurrentQtr,
Actual_Currentyear,
Budget_CurrentMonth,
Budget_CurrentQtr,
Budget_CurrentYear,
Credit,
Debit,
PL,
YTDPL,
Actuals,
Actuals_Year,
Actuals_Depot,
Actuals_Year_Depot
FROM (
SELECT
AccountID,
SUM(CASE WHEN Refdate >= @FROMDATE and Refdate <= @TODATE
THEN ActualDebit - ActualCredit ELSE 0 END)
AS Actual_CurrentMonth,
SUM(CASE WHEN Refdate >= @START_QTRE and Refdate <= @END_QTRE
THEN ActualDebit - ActualCredit ELSE 0 END)
AS Actual_CurrentQtr,
SUM(CASE WHEN Refdate BETWEEN @CY_START and @TODATE
THEN ActualDebit - ActualCredit ELSE 0 END)
AS Actual_CurrentYear,
SUM(CASE WHEN Refdate between @FROMDATE and @TODATE
THEN actualCredit ELSE 0 END)
AS Credit,
SUM(CASE WHEN Refdate between @FROMDATE and @TODATE
THEN actualDebit ELSE 0 END)
AS Dedit,
SUM(CASE WHEN Refdate between @FROMDATE and @TODATE AND a1.Level1 >= '400000000000000'
THEN actualCredit - ActualDebit ELSE 0 END)
AS PL,
SUM(CASE WHEN Refdate BETWEEN @CY_START and @TODATE AND a1.Level1 >= '400000000000000'
THEN actualCredit - ActualDebit ELSE 0 END)
AS YTDPL
FROM #MainAccount
WHERE
Refdate BETWEEN @CY_START and @TODATE
GROUP BY AccountID
) AS main_accountid
INNER JOIN #MainAccount ma ON
ma.AccountID = main_accountid.AccountID
LEFT OUTER JOIN (
SELECT
AccountID, Segment1,
SUM(CASE WHEN Refdate >= @FROMDATE and a1.Refdate <= @TODATE
THEN ActualAmount ELSE 0 END) AS Actuals,
SUM(CASE WHEN Refdate >= @CY_START and a1.Refdate <= @TODATE
THEN ActualAmount ELSE 0 END) AS Actuals_Year
FROM #MainAccount
WHERE
Refdate BETWEEN @CY_START and @TODATE
GROUP BY AccountID, Segment1
) AS main_accountid_segment1 ON
main_accountid_segment1.AccountID = main_accountid.AccountID AND
main_accountid_segment1.Segment1 = ma.Segment1
LEFT OUTER JOIN (
SELECT
AccountID, Segment1, profitcode,
SUM(CASE WHEN Refdate >= @FROMDATE and a1.Refdate <= @TODATE
THEN ActualAmount ELSE 0 END) AS Actuals_Depot,
SUM(CASE WHEN Refdate >= @CY_START and a1.Refdate <= @TODATE
THEN ActualAmount ELSE 0 END) AS Actuals_Year_Depot
FROM #MainAccount
WHERE
Refdate BETWEEN @CY_START and @TODATE
GROUP BY AccountID, Segment1, profitcode
) AS main_accountid_segment1_profitcode ON
main_accountid_segment1_profitcode.AccountID = ma.AccountID AND
main_accountid_segment1_profitcode.Segment1 = ma.Segment1 AND
main_accountid_segment1_profitcode.profitcode = ma.profitcode
LEFT OUTER JOIN ##BudgetAccount ba ON
ba.AccountID = ma.AccountID
--AND aa.Profitcode = bb.BudgetPCentre
AND ma.DateMonth = ba.DATEMONTH
AND ma.PeriodStartDate = ba.PeriodStartDate
LEFT OUTER JOIN (
SELECT
SUM(CASE WHEN Refdate >= @FROMDATE and Refdate <= @TODATE
THEN BudgetDebit - BudgetCredit ELSE 0 END)
AS Budget_CurrentMonth,
SUM(CASE WHEN Refdate between @START_QTRE and @END_QTRE
THEN BudgetDebit - BudgetCredit ELSE 0 END)
AS Budget_CurrentQtr,
SUM(CASE WHEN Refdate between @CY_START and @TODATE
THEN BudgetDebit - BudgetCredit ELSE 0 END)
AS Budget_CurrentYear
FROM #BudgetAccount
WHERE
Refdate BETWEEN @CY_START and @TODATE
) AS budget_accountid ON
ma.AccountID = ba.AccountID
ORDER BY ma.Level1, ma.Level2, ma.Level3, ma.Level4, ma.Level5, ma.AcctCode
Edit: "Bolded" the index creation code.
SQL DBA,SQL Server MVP(07, 08, 09) A socialist is someone who will give you the shirt off *someone else's* back.
September 16, 2014 at 5:55 am
Can you provide some data for first two temp tables?
Viewing 15 posts - 1 through 15 (of 21 total)
You must be logged in to reply to this topic. Login to reply