Performance related question

Question

Performance related question

Smash125

Hall of Fame

Points: 3562
More actions
April 24, 2014 at 5:29 am

#286055

This query takes 2 mins to execute, Please suggesr if we can improve more on performance
SELECT P.ProgramId
,P.Tier4Id
,7 AS MetricId
,(convert(DATETIME, CONVERT(VARCHAR, Month(s.DATE)) + '/1/' + CONVERT(VARCHAR, Year(s.DATE)))) AS DATE
,CASE
WHEN SUM(ISNULL(S.ITD_BCWS_PMB, 0)) <> 0
THEN ROUND(SUM(ISNULL(S.ITD_BCWP_PMB, 0)) / SUM(ISNULL(S.ITD_BCWS_PMB, 0)), 2)
ELSE NULL
END AS Value
,SUM(ISNULL(S.ITD_BCWP_PMB, 0)) AS BCWP
FROM Staging.SPI_CPI_WBS S
INNER JOIN Core.Projects PJ ON S.ProjectId = PJ.ProjectId AND PJ.IsActive = 1
INNER JOIN Core.ProgramFinancials PF ON PF.ProgramId = S.ProgramId AND Year(PF.DATE) = Year(S.DATE) AND PF.IsYearly = 1
INNER JOIN Core.v_WBS_Tier_Structure_SPICPI P ON S.WBSId = P.WBSId AND P.ProgramId = S.ProgramId
WHERE S.LSKINdicator = 1 AND S.ProjectId IS NOT NULL
/*AP3-820 Check for BCWP > 0*/
AND ISNULL(S.ITD_BCWP_PMB, 0) > 0 AND (P.ProgramStatus = 1 OR isnull(PF.Spend, 0) != 0)
GROUP BY P.ProgramId
,s.DATE
,P.Tier4Id
HAVING SUM(ISNULL(S.ITD_BCWP_PMB, 0)) > 0

Viewing 11 posts - 1 through 10 (of 10 total)

You must be logged in to reply to this topic. Login to reply

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 1

Table definitions, index definitions and execution plan please.

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

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 2

And while you're doing that, don't forget the effect of wrapping functions around columns in your WHERE clause (or joins)

WHERE S.LSKINdicator = 1

AND S.ProjectId IS NOT NULL

/*AP3-820 Check for BCWP > 0*/

AND S.ITD_BCWP_PMB > 0

AND (P.ProgramStatus = 1 OR PF.Spend <> 0)

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

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

Smash125 Hall of Fame Points: 3562 More actions · Answer 3

I am posting the query suggest me if any performance improvements can be made

UPDATE DW.ProgramScores_T4_P

SET

SPI=null

,SPI_H=null

,CPI=null

,CPI_h=null

, M_SPI = null

,M_SPI_H = null

,M_CPI = null

,M_CPI_H = null

where Date between dateadd(mm,-12,getdate()) and getdate()

-- SPI CPI - Programscores

MERGE DW.ProgramScores_T4_P DW

USING

(

SELECT isnull(SPI.ProgramId, CPI.ProgramId) AS ProgramId

,isnull(SPI.DATE, CPI.DATE) AS DATE

,isnull(SPI.Tier4Id,CPI.Tier4Id) as Tier4Id

,SPI

,SPI_H

,CPI

,CPI_H

,CASE

WHEN isnull(SPI.DATE, CPI.DATE) BETWEEN dateadd(mm, - 12, getdate()) AND getdate()

THEN 1

ELSE 0

END AS Datediff

,Month(isnull(SPI.DATE, CPI.DATE)) AS Month

,Year(isnull(SPI.DATE, CPI.DATE)) AS Year

FROM (

SELECT SPI.ProgramId

,SPI.Metricid

,Tier4Id

,DATE

,SPI

,SPI_H

FROM (

SELECT ProgramId

,Tier4Id

,A.MetricId

,DATE

,Value AS SPI

,(

CASE

WHEN [Value] IS NULL OR [IsGoalOriented] = (0) OR [IsGoalOriented] IS NULL /*OR MBM.fn_CheckGoalsAvailability(A.MetricId) = 0 */

THEN (5)

ELSE CASE

WHEN [Value] >= [LCLG] AND [Value] <= [UCLG] OR [Value] >= [LCLG] AND [UCLG] IS NULL

THEN (2)

ELSE CASE

WHEN [Value] >= [LCLY] AND [Value] <= [UCLY]

THEN (3)

ELSE (4)

END

) AS SPI_H

FROM (

SELECT P.ProgramId

,P.Tier4Id

,7 AS MetricId

,(convert(DATETIME, CONVERT(VARCHAR, Month(s.DATE)) + '/1/' + CONVERT(VARCHAR, Year(s.DATE)))) AS DATE

,CASE

WHEN SUM(ISNULL(S.ITD_BCWS_PMB, 0)) <> 0

THEN ROUND(SUM(ISNULL(S.ITD_BCWP_PMB, 0)) / SUM(ISNULL(S.ITD_BCWS_PMB, 0)), 2)

ELSE NULL

END AS Value

,SUM(ISNULL(S.ITD_BCWP_PMB, 0)) AS BCWP

FROM Staging.SPI_CPI_WBS S

INNER JOIN Core.Projects PJ ON S.ProjectId = PJ.ProjectId AND PJ.IsActive = 1

INNER JOIN Core.ProgramFinancials PF ON PF.ProgramId = S.ProgramId AND Year(PF.DATE) = Year(S.DATE) AND PF.IsYearly = 1

INNER JOIN Core.v_WBS_Tier_Structure_SPICPI P ON S.WBSId = P.WBSId AND P.ProgramId = S.ProgramId

WHERE S.LSKINdicator = 1 AND S.ProjectId IS NOT NULL

/*AP3-820 Check for BCWP > 0*/

AND ISNULL(S.ITD_BCWP_PMB, 0) > 0 AND (P.ProgramStatus = 1 OR isnull(PF.Spend, 0) != 0)

GROUP BY P.ProgramId

,s.DATE

,P.Tier4Id

HAVING SUM(ISNULL(S.ITD_BCWP_PMB, 0)) > 0

) A

INNER JOIN MBM.Metrics M ON M.MetricId = A.MetricId AND M.IsActive = 1

) SPI

LEFT JOIN (

SELECT CPI.ProgramId

--,WBSId

,Tier4Id

--,Tier3Id

--,Tier2Id

,CPI.Metricid

,DATE

,CPI

,CPI_H

FROM (

SELECT ProgramId

--,WBSId

,Tier4Id

--,Tier3Id

--,Tier2Id

,A.MetricId

,DATE

,Value AS CPI

,(

CASE

WHEN [Value] IS NULL OR [IsGoalOriented] = (0) OR [IsGoalOriented] IS NULL /*OR MBM.fn_CheckGoalsAvailability(A.MetricId) = 0 */

THEN (5)

ELSE CASE

WHEN [Value] >= [LCLG] AND [Value] <= [UCLG] OR [Value] >= [LCLG] AND [UCLG] IS NULL

THEN (2)

ELSE CASE

WHEN [Value] >= [LCLY] AND [Value] <= [UCLY] THEN (3)

ELSE (4)

END

) AS CPI_H

FROM (

SELECT P.ProgramId

--,S.WBSId

,P.Tier4Id

--,WBS.Tier3Id

--,WBS.Tier2Id

,8 AS MetricId

,(convert(DATETIME, CONVERT(VARCHAR, Month(s.DATE)) + '/1/' + CONVERT(VARCHAR, Year(s.DATE)))) AS DATE

,CASE

WHEN SUM(ISNULL(S.ITD_ACWP, 0)) <> 0

THEN ROUND(SUM(ISNULL(S.ITD_BCWP_PMB, 0)) / SUM(ISNULL(S.ITD_ACWP, 0)), 2)

ELSE NULL

END AS Value

,SUM(ISNULL(S.ITD_BCWP_PMB, 0)) AS BCWP

FROM Staging.SPI_CPI_WBS S

INNER JOIN Core.Projects PJ ON S.ProjectId = PJ.ProjectId AND PJ.IsActive = 1

--INNER JOIN Core.Programs P ON P.ProgramId = S.ProgramId and P.Isactive=1

INNER JOIN Core.ProgramFinancials PF ON PF.ProgramId = S.ProgramId AND Year(PF.DATE) = Year(S.DATE) AND PF.IsYearly = 1

--inner join Core.WBS_CAM_TIERS_Structure WBS ON S.WBSId = WBS.WBSId

INNER JOIN Core.v_WBS_Tier_Structure_SPICPI P ON S.WBSId = P.WBSId AND P.ProgramId = S.ProgramId

WHERE S.LSKINdicator = 1 AND S.ProjectId IS NOT NULL

/*AP3-820 Check for BCWP > 0*/

AND ISNULL(S.ITD_BCWP_PMB, 0) > 0 AND (P.ProgramStatus = 1 OR isnull(PF.Spend, 0) != 0)

GROUP BY P.ProgramId

,s.DATE

--,S.WBSId

,P.Tier4Id

--,WBS.Tier3Id

--,WBS.Tier2Id

HAVING SUM(ISNULL(S.ITD_BCWP_PMB, 0)) > 0 /*AP3-688 & AP3-702 Check for BCWP > 0 */

) A

INNER JOIN MBM.Metrics M ON M.MetricId = A.MetricId AND M.IsActive = 1

) CPI

WHERE CPI IS NOT NULL

) CPI

ON CPI.ProgramId = SPI.ProgramId AND CPI.DATE = SPI.DATE AND

CPI.Tier4Id = SPI.Tier4Id --AND CPI.Tier3Id = SPI.Tier3Id AND CPI.Tier2Id = SPI.Tier2Id

)SPICPI

on SPICPI.ProgramId = DW.ProgramId and Datediff(dd,SPICPI.Date,DW.Date) = 0 and

SPICPI.Tier4Id = DW.Tier4Id --and SPICPI.Tier3Id = DW.Tier3Id and SPICPI.Tier2Id = DW.Tier2Id

WHEN MATCHED AND (SPICPI.Datediff = 1) THEN

update SET DW.SPI = SPICPI.SPI,

DW.SPI_H = SPICPI.SPI_H,

DW.CPI = SPICPI.CPI,

DW.CPI_H = SPICPI.CPI_H,

DW.UpdatedDate = getdate()

WHEN NOT MATCHED THEN

insert (ProgramId,Tier4Id, Date,SPI,SPI_H,CPI,CPI_H,CreatedDate)

values(SPICPI.ProgramId,SPICPI.Tier4Id,SPICPI.Date,SPICPI.SPI,SPICPI.SPI_H, SPICPI.CPI,SPICPI.CPI_H,getdate());

Regards,

thava SSCrazy Points: 2275 More actions · Answer 4

OMG ,

What a merge statement?

My small suggestion

execute the using part separately and find the low performing area then

analyse the query as per your tables and indexes you have

if possible make it simpler, avoid subqueries as much as possible

Every rule in a world of bits and bytes, can be bend or eventually be broken
MyBlog About Common dialog control
A Visualizer for viewing SqlCommand object script [/url]

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 5

GilaMonster (4/24/2014)
Table definitions, index definitions and execution plan please.

Can't do much without these

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

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Jeff Moden SSC Guru Points: 1004339 More actions · Answer 6

Sorry folks. I'm not sure what I was thinking on this particular post and I've taken this particular entry down to make sure that no one would use it. Please see Gail's post below for why Chris' solution is correct.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 7

Original where clause predicate:

isnull(PF.Spend, 0) != 0

So, if any row has NULL for Spend, make that 0 and then look for rows where the resultant value is not 0. i.e. exclude rows which have 0 or NULL for Spend

To demonstrate

DECLARE @SomeInt INT;

SELECT @SomeInt = 0;

SELECT 'Don''t return 0'

WHERE (isnull(@SomeInt, 0) != 0);

SELECT @SomeInt = NULL;

SELECT 'Don''t return NULL'

WHERE (isnull(@SomeInt, 0) != 0);

So, without the function that would be

PF.Spend IS NOT NULL OR PF.Spend != 0

But since nulls are never = or != anything, just

PF.Spend != 0

DECLARE @SomeInt INT;

SELECT @SomeInt = 0;

SELECT 'Don''t return 0'

WHERE @SomeInt != 0;

SELECT @SomeInt = NULL;

SELECT 'Don''t return NULL'

WHERE @SomeInt != 0;

--- Edited ---

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

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Jeff Moden SSC Guru Points: 1004339 More actions · Answer 8

Wow! I'm not sure what I was thinking about when I wrote that mess. Looking back at it, it doesn't even make sense to me. :blush: I can't even blame it on working on a similar problem at the time it's so far out in left field.

Thanks for the catch and the correction, Gail.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 9

“Peel one potato at a time” as Jeff says. Firstly, separate out the source query from the MERGE statement. What you notice with this simplification - cutting down only a little on a baffling amount of unnecessary code - is that the same core query appears twice:

SELECT -- a1

P.ProgramId,

P.Tier4Id,

MetricId = 7,

[DATE] = convert(DATETIME, CONVERT(VARCHAR, MONTH(s.DATE)) + '/1/' + CONVERT(VARCHAR, YEAR(s.DATE))),

-- S.ITD_BCWS_PMB

Value = CASE WHEN SUM(ISNULL(S.ITD_BCWS_PMB, 0)) <> 0 THEN ROUND(SUM(ISNULL(S.ITD_BCWP_PMB, 0)) / SUM(ISNULL(S.ITD_BCWS_PMB, 0)), 2)

ELSE NULL END,

BCWP = SUM(ISNULL(S.ITD_BCWP_PMB, 0))

FROM Staging.SPI_CPI_WBS S

INNER JOIN Core.Projects PJ

ON S.ProjectId = PJ.ProjectId AND PJ.IsActive = 1

INNER JOIN Core.ProgramFinancials PF

ON PF.ProgramId = S.ProgramId AND YEAR(PF.[DATE]) = YEAR(S.[DATE]) AND PF.IsYearly = 1

INNER JOIN Core.v_WBS_Tier_Structure_SPICPI P

ON S.WBSId = P.WBSId AND P.ProgramId = S.ProgramId

WHERE S.LSKINdicator = 1

AND S.ProjectId IS NOT NULL /*AP3-820 Check for BCWP > 0*/

AND ISNULL(S.ITD_BCWP_PMB, 0) > 0

AND (P.ProgramStatus = 1 OR isnull(PF.Spend, 0) != 0)

GROUP BY P.ProgramId, s.[DATE], P.Tier4Id

HAVING SUM(ISNULL(S.ITD_BCWP_PMB, 0)) > 0

-----------------------------------------------------------------------------

SELECT -- a2

P.ProgramId, --,S.WBSId

P.Tier4Id --,WBS.Tier3Id --,WBS.Tier2Id

MetricId = 8,

[DATE] = convert(DATETIME, CONVERT(VARCHAR, MONTH(s.DATE)) + '/1/' + CONVERT(VARCHAR, YEAR(s.DATE))),

-- S.ITD_ACWP

Value = CASE WHEN SUM(ISNULL(S.ITD_ACWP, 0)) <> 0 THEN ROUND(SUM(ISNULL(S.ITD_BCWP_PMB, 0)) / SUM(ISNULL(S.ITD_ACWP, 0)), 2)

ELSE NULL END,

BCWP = SUM(ISNULL(S.ITD_BCWP_PMB, 0))

FROM Staging.SPI_CPI_WBS S

INNER JOIN Core.Projects PJ

ON S.ProjectId = PJ.ProjectId AND PJ.IsActive = 1 --INNER JOIN Core.Programs P ON P.ProgramId = S.ProgramId and P.Isactive=1

INNER JOIN Core.ProgramFinancials PF

ON PF.ProgramId = S.ProgramId AND YEAR(PF.[DATE]) = YEAR(S.[DATE]) AND PF.IsYearly = 1 --inner join Core.WBS_CAM_TIERS_Structure WBS ON S.WBSId = WBS.WBSId

INNER JOIN Core.v_WBS_Tier_Structure_SPICPI P

ON S.WBSId = P.WBSId AND P.ProgramId = S.ProgramId

WHERE S.LSKINdicator = 1

AND S.ProjectId IS NOT NULL /*AP3-820 Check for BCWP > 0*/

AND ISNULL(S.ITD_BCWP_PMB, 0) > 0

AND (P.ProgramStatus = 1 OR isnull(PF.Spend, 0) != 0)

GROUP BY P.ProgramId, s.[DATE], P.Tier4Id --,WBS.Tier3Id --,WBS.Tier2Id

HAVING SUM(ISNULL(S.ITD_BCWP_PMB, 0)) > 0 /*AP3-688 & AP3-702 Check for BCWP > 0 */

The core query sections can be clipped out of the source query and replaced with a reference to the same core query set up as a CTE. With the source query simplified, it’s much easier to visualise what it’s doing and remove some of the dead wood:

-- SPI CPI - Programscores

WITH CTEa AS (

SELECT -- a1

P.ProgramId,

P.Tier4Id,

[DATE] = DATEADD(month,DATEDIFF(month,0,s.[DATE]),0), -- first day of the month

Value7 = CASE WHEN SUM(ISNULL(S.ITD_BCWS_PMB, 0)) <> 0 THEN ROUND(SUM(ISNULL(S.ITD_BCWP_PMB, 0)) / SUM(ISNULL(S.ITD_BCWS_PMB, 0)), 2)

ELSE NULL END,

Value8 = CASE WHEN SUM(ISNULL(S.ITD_ACWP, 0)) <> 0 THEN ROUND(SUM(ISNULL(S.ITD_BCWP_PMB, 0)) / SUM(ISNULL(S.ITD_ACWP, 0)), 2)

ELSE NULL END,

BCWP = SUM(ISNULL(S.ITD_BCWP_PMB, 0))

FROM Staging.SPI_CPI_WBS S

INNER JOIN Core.Projects PJ

ON S.ProjectId = PJ.ProjectId AND PJ.IsActive = 1

INNER JOIN Core.ProgramFinancials PF

ON PF.ProgramId = S.ProgramId AND YEAR(PF.[DATE]) = YEAR(S.[DATE]) AND PF.IsYearly = 1

INNER JOIN Core.v_WBS_Tier_Structure_SPICPI P

ON S.WBSId = P.WBSId AND P.ProgramId = S.ProgramId

WHERE S.LSKINdicator = 1

AND S.ProjectId IS NOT NULL /*AP3-820 Check for BCWP > 0*/

AND ISNULL(S.ITD_BCWP_PMB, 0) > 0

AND (P.ProgramStatus = 1 OR isnull(PF.Spend, 0) != 0)

GROUP BY P.ProgramId, s.[DATE], P.Tier4Id

HAVING SUM(ISNULL(S.ITD_BCWP_PMB, 0)) > 0

)

SELECT

ProgramId = isnull(SPI.ProgramId, CPI.ProgramId),

[DATE] = isnull(SPI.[DATE], CPI.[DATE]),

Tier4Id = isnull(SPI.Tier4Id, CPI.Tier4Id),

SPI,

SPI_H,

CPI,

CPI_H,

[Datediff] = CASE

WHEN isnull(SPI.[DATE], CPI.[DATE]) BETWEEN dateadd(mm, - 12, getdate()) AND getdate() THEN 1

ELSE 0 END,

[MONTH] = Month(isnull(SPI.[DATE], CPI.[DATE])),

[YEAR] = Year(isnull(SPI.[DATE], CPI.[DATE]))

INTO #SPICPI

FROM ( -- SPI

SELECT

ProgramId, MetricId = 7, Tier4Id, [DATE], SPI = Value7,

SPI_H = CASE

WHEN [Value7] IS NULL OR [IsGoalOriented] = 0 OR [IsGoalOriented] IS NULL /*OR MBM.fn_CheckGoalsAvailability(A.MetricId) = 0 */ THEN 5

WHEN [Value7] >= [LCLG] AND [Value7] <= [UCLG] OR [Value7] >= [LCLG] AND [UCLG] IS NULL THEN 2

WHEN [Value7] >= [LCLY] AND [Value7] <= [UCLY] THEN 3 ELSE 4 END

FROM CTEa a

CROSS JOIN MBM.Metrics M

ON M.MetricId = 7 AND M.IsActive = 1

) SPI

LEFT JOIN ( -- CPI

SELECT

ProgramId, MetricId = 8, Tier4Id, [DATE], CPI = Value8,

CPI_H = CASE

WHEN [Value8] IS NULL OR [IsGoalOriented] = 0 OR [IsGoalOriented] IS NULL /*OR MBM.fn_CheckGoalsAvailability(A.MetricId) = 0 */ THEN 5

WHEN [Value8] >= [LCLG] AND [Value8] <= [UCLG] OR [Value8] >= [LCLG] AND [UCLG] IS NULL THEN 2

WHEN [Value8] >= [LCLY] AND [Value8] <= [UCLY] THEN 3 ELSE 4 END

FROM CTEa a

CROSS JOIN MBM.Metrics M

ON M.MetricId = 8 AND M.IsActive = 1

WHERE Value8 IS NOT NULL

) CPI

ON CPI.ProgramId = SPI.ProgramId

AND CPI.[DATE] = SPI.[DATE]

AND CPI.Tier4Id = SPI.Tier4Id --AND CPI.Tier3Id = SPI.Tier3Id AND CPI.Tier2Id = SPI.Tier2Id

MERGE DW.ProgramScores_T4_P DW

USING #SPICPI SPICPI

ON SPICPI.ProgramId = DW.ProgramId

AND Datediff(dd,SPICPI.[Date],DW.[Date]) = 0

AND SPICPI.Tier4Id = DW.Tier4Id --and SPICPI.Tier3Id = DW.Tier3Id and SPICPI.Tier2Id = DW.Tier2Id

WHEN MATCHED

AND (SPICPI.Datediff = 1)

THEN UPDATE

SET DW.SPI = SPICPI.SPI,

DW.SPI_H = SPICPI.SPI_H,

DW.CPI = SPICPI.CPI,

DW.CPI_H = SPICPI.CPI_H,

DW.UpdatedDate = getdate()

WHEN NOT MATCHED THEN

INSERT (

ProgramId,

Tier4Id,

[Date],

SPI,

SPI_H,

CPI,

CPI_H,

CreatedDate)

VALUES(

SPICPI.ProgramId,

SPICPI.Tier4Id,

SPICPI.[Date],

SPICPI.SPI,

SPICPI.SPI_H,

SPICPI.CPI,

SPICPI.CPI_H,

getdate());

At this level of simplification, it becomes apparent exactly what the query is supposed to do, and an alternative method using only one pass of the tables can be designed...

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

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

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 10

-- SPI CPI - Programscores

SELECT

a.ProgramId, a.Tier4Id, a.[DATE],

SPI= a.Value7,

SPI_H= CASE

WHEN a.[Value7] IS NULL OR ISNULL(m7.[IsGoalOriented],0) = 0 THEN 5 /*OR MBM.fn_CheckGoalsAvailability(A.MetricId) = 0 */

WHEN a.[Value7] BETWEEN m7.[LCLG] AND m7.[UCLG] OR (a.[Value7] >= m7.[LCLG] AND m7.[UCLG] IS NULL) THEN 2

WHEN a.[Value7] BETWEEN m7.[LCLY] AND m7.[UCLY] THEN 3

ELSE 4 END,

CPI= Value8,

CPI_H= CASE

WHEN a.[Value8] IS NULL THEN NULL

WHEN ISNULL(m8.[IsGoalOriented],0) = 0 THEN 5 /*OR MBM.fn_CheckGoalsAvailability(A.MetricId) = 0 */

WHEN a.[Value8] BETWEEN m8.[LCLG] AND m8.[UCLG] OR (a.[Value8] >= m8.[LCLG] AND m8.[UCLG] IS NULL) THEN 2

WHEN a.[Value8] BETWEEN m8.[LCLY] AND m8.[UCLY] THEN 3

ELSE 4 END,

[Datediff] = CASE

WHEN a.[DATE] BETWEEN DATEADD(mm, - 12, GETDATE()) AND GETDATE() THEN 1

ELSE 0 END,

[MONTH]= MONTH(a.[DATE]),

[YEAR]= YEAR(a.[DATE])

INTO #SPICPI

FROM (

SELECT -- a1

P.ProgramId,

P.Tier4Id,

[DATE] = DATEADD(month,DATEDIFF(month,0,s.[DATE]),0), -- first day of the month

Value7 = ROUND(SUM(S.ITD_BCWP_PMB) / NULLIF(SUM(S.ITD_BCWS_PMB),0), 2),

Value8 = ROUND(SUM(S.ITD_BCWP_PMB) / NULLIF(SUM(S.ITD_ACWP), 2),0)

FROM Staging.SPI_CPI_WBS S

INNER JOIN Core.v_WBS_Tier_Structure_SPICPI P

ON S.WBSId = P.WBSId AND P.ProgramId = S.ProgramId

INNER JOIN Core.Projects PJ

ON S.ProjectId = PJ.ProjectId AND PJ.IsActive = 1

INNER JOIN Core.ProgramFinancials PF

ON PF.ProgramId = S.ProgramId

AND PF.IsYearly = 1

AND YEAR(PF.[DATE]) = YEAR(S.[DATE]) -- function on JOIN columns

WHERE S.LSKINdicator = 1

--AND S.ProjectId IS NOT NULL /*AP3-820 Check for BCWP > 0*/ -- unnecessary, see PJ join

AND S.ITD_BCWP_PMB > 0

AND (P.ProgramStatus = 1 OR PF.Spend <> 0)

GROUP BY P.ProgramId, P.Tier4Id, s.[DATE] --HAVING SUM(S.ITD_BCWP_PMB) > 0 -- unnecessary, see WHERE clause

) a

CROSS JOIN MBM.Metrics m7

ON m7.MetricId = 7 AND m7.IsActive = 1

CROSS JOIN MBM.Metrics m8

ON m8.MetricId = 8 AND m8.IsActive = 1

Use brackets to disambiguate logic. Use table aliases. Avoid using functions on table columns referenced in joins or WHERE clause. Don’t use SQL Server keywords as column names. Learn how to use CASE properly. Avoid putting half-finished queries into production even if the results appear at first glance to be correct.

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

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