Performance related question

  • Smash125

    Hall of Fame

    Points: 3527

    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

  • Gail Shaw

    SSC Guru

    Points: 1004454

    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: 186056

    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)

    [font="Arial"]“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw[/font]


    For fast, accurate and documented assistance in answering your questions, please read this article[/url].
    Understanding and using APPLY, (I)[/url] and (II)[/url] Paul White[/url]
    Hidden RBAR: Triangular Joins[/url] / The "Numbers" or "Tally" Table: What it is and how it replaces a loop[/url] Jeff Moden[/url]
    [url

  • Smash125

    Hall of Fame

    Points: 3527

    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

    END

    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

    ) 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

    END

    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

    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: 1004454

    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: 995467

    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.
    "If you think its expensive to hire a professional to do the job, wait until you hire an amateur."--Red Adair
    "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: 1004454

    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: 995467

    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.
    "If you think its expensive to hire a professional to do the job, wait until you hire an amateur."--Red Adair
    "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: 186056

    “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...

    [font="Arial"]“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw[/font]


    For fast, accurate and documented assistance in answering your questions, please read this article[/url].
    Understanding and using APPLY, (I)[/url] and (II)[/url] Paul White[/url]
    Hidden RBAR: Triangular Joins[/url] / The "Numbers" or "Tally" Table: What it is and how it replaces a loop[/url] Jeff Moden[/url]
    [url

  • ChrisM@Work

    SSC Guru

    Points: 186056

    -- 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.

    [font="Arial"]“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw[/font]


    For fast, accurate and documented assistance in answering your questions, please read this article[/url].
    Understanding and using APPLY, (I)[/url] and (II)[/url] Paul White[/url]
    Hidden RBAR: Triangular Joins[/url] / The "Numbers" or "Tally" Table: What it is and how it replaces a loop[/url] Jeff Moden[/url]
    [url

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

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