Performance related issue in the report after adding new table in the sp .

  • I have an issue in the report. Have added new table in the SP. After adding new table report rendering gets hanged and never processes

    below is the stored procedure

    this is the table added in the query last

     

    UM_Risk_ICRA_FI_Rating_ICRA_ST_LT_Mapping_CP_CD_Table this table contains 4 lakh records

    CREATE PROCEDURE [dbo].[RPT_IssuerRatingNew]

    /* Issuer Rating */

    --Declare @NAVDATE as date

    --set @NAVDATe = '2018-07-07'

    @NAVDATE DATE,

    @MATDATE DATE,

    @Scheme VARCHAR (MAX),

    @TYPE VARCHAR(MAX) ,

    @SectorCode Varchar(MAX)

    AS

    ;WITH MAIN_CTE

    AS (SELECT D.NAME AS ManagementGroupName,

    C.ISSUER_NAME AS Issuer_Name,

    /* CASE WHEN h.Rating = 'AA(SO)' THEN 'AA'

    WHEN h.Rating = 'AA-(SO)' THEN 'AA-'

    WHEN h.Rating = 'A(SO)' THEN 'A'

    WHEN h.Rating = 'A+(SO)' THEN 'A+'

    WHEN h.Rating = 'AA+(SO)' THEN 'AA+'

    WHEN h.Rating = 'AAA(SO)' THEN 'AAA'

    WHEN h.Rating = 'A1+(SO)' THEN 'A1+'

    WHEN h.Rating = 'A3(SO)' THEN 'A3'

    WHEN h.Rating = 'BBB(SO)' THEN 'BBB'

    WHEN h.Rating IS NULL THEN 'UnRated'

    ELSE h.Rating END AS Rating,*/

    cast(CASE

    WHEN h.Rating IS NULL THEN 'UnRated'

    ELSE replace(replace(rtrim(ltrim(h.Rating)),'(CE)',''),'(SO)','')

    END as nvarchar(8)) AS Rating,

    E.SCHEME_CODE AS Scheme_Code,

    I.SECURITY,

    a.ASSET_TYPE,

    E.SCHEME_NAME AS Scheme_Name,

    C.ISSTYPECODE AS ISSUERTYPE,

    C.IssTypeName,

    C.SectorName AS SectorName,

    B.AssetTypeName AS SOVRatings,

    Round(abs(a.AMOUNT) / 10000000,7) Marketvalue,

    -- ROUND((abs(a.AMOUNT) / SUM(ABS(a.AMOUNT)) OVER ()), 5) * 100 AS Exposure,

    -- I.VALUE_DATE,

    i.FA_YTM_MAT_DATE CLPT_Date,

    b.MAT_DATE,

    --CASE WHEN ISNULL(I.VALUE_DATE,B.Mat_Date)>= @NAVDATE

    -- AND ISNULL(I.VALUE_DATE,B.Mat_Date)<= DATEADD(MM, 3, @NAVDATE) THEN Round(abs(a.AMOUNT) / 10000000, 2) END AS Exposure_lessthan3Months,

    --CASE WHEN ISNULL(I.VALUE_DATE,B.Mat_Date) > DATEADD(MM, 3, @NAVDATE)

    -- AND ISNULL(I.VALUE_DATE,B.Mat_Date) <= DATEADD(MM, 12, @NAVDATE) THEN Round(abs(a.AMOUNT) / 10000000, 2) END AS Exposure_3to12Months,

    --CASE WHEN ISNULL(I.VALUE_DATE,B.Mat_Date) > DATEADD(MM, 12, @NAVDATE) THEN Round(abs(a.AMOUNT) / 10000000, 2) END AS Exposure_Morethan12Months,

    ISNULL(i.FA_YTM_MAT_DATE,b.MAT_DATE) AS Maturity,

    ABS(datediff(D,@NAVDATE,ISNULL(i.FA_YTM_MAT_DATE,b.MAT_DATE))) AS Mat_NavDiff,

    h.YIELD

    FROM stg_mf_schnavbd AS A

    LEFT OUTER JOIN stg_mf_Security AS B ON A.security = B.SecCode

    INNER JOIN stg_mf_YTM_CallPutDate I ON a.NAV_DATE=I.VALUE_DATE and A.SCHEME=I.SCHEME AND B.SecCode = I.[SECURITY]

    LEFT OUTER JOIN stg_mf_Issuer AS C ON B.issuer = C.ISSUER_ID

    LEFT OUTER JOIN stg_mf_Mgmt AS D ON C.MGMTGRPCODE = D.MGMT_GROUP AND D.RECTYPE = 'L'

    LEFT OUTER JOIN stg_mf_SchemeMaster AS E ON A.SCHEME = E.SCHEME_CODE

    LEFT OUTER JOIN stg_mf_Holding AS h ON A.NAV_DATE = h.NAV_DATE

    LEFT JOIN UM_Risk_ICRA_FI_Rating_ICRA_ST_LT_Mapping_CP_CD_Table FF ON FF.FileDate = A.NAV_DATE AND FF.ISIN = B.ISIN_CODE

    AND A.SCHEME = h.SCHEME

    AND a.SECURITY = h.SECURITY

    AND a.CUSTODIAN = h.CUSTODIAN

    AND a.ASSET_TYPE = h.ASSET_TYPE

    AND a.AMC_CODE = h.AMC_CODE

    WHERE B.AssetGrpCode IN ('DB','MM')

    AND B.RECTYPE = 'L'

    AND C.Rectype = 'L'

    AND A.NAV_DATE = @NAVDATE

    AND i.FA_YTM_MAT_DATE <= @MATDATE

    AND A.SCHEME in (select * from string_split(@Scheme,','))

    AND C.ISSTYPECODE in (select * from string_split(@TYPE,','))

    AND C.SectorCode in (select * from string_split(@SectorCode,','))

    )

    --select * from MAIN_CTE

    SELECT

    a.Rating AS WorstRating,

    a.Issuer_Name,

    a.ManagementGroupName,

    a.Scheme_Code,

    a.Scheme_Name,

    a.IssTypeName,

    a.SectorName,

    a.SOVRatings,

    a.Marketvalue,

    --a.Exposure,

    a.Maturity,

    a.Mat_NavDiff,

    -- a.VALUE_DATE,

    -- a.MAT_DATE1,

    --abs(a.Marketvalue)/SUM(ABS(a.Marketvalue)) OVER (PARTITION BY Rating,ISSUER_NAME,ManagementGroupName,SCHEME_CODE,ISSUERTYPE,SectorName,ASSET_TYPE ORDER BY Rating,ISSUER_NAME,ManagementGroupName,SCHEME_CODE,ISSUERTYPE,SectorName,ASSET_TYPE) *100 Expo

    sure,

    Round((abs(a.Marketvalue) / SUM(ABS(a.Marketvalue)) OVER ()),7)*100 Exposure,

    CASE WHEN ISNULL(a.CLPT_Date,a.MAT_DATE)>= @NAVDATE

    AND ISNULL(a.CLPT_Date,a.MAT_DATE)<= DATEADD(MM, 3, @NAVDATE) THEN a.Marketvalue END AS Exposure_lessthan3Months,

    CASE WHEN ISNULL(a.CLPT_Date,a.MAT_DATE)> DATEADD(MM, 3, @NAVDATE)

    AND ISNULL(a.CLPT_Date,a.MAT_DATE)<= DATEADD(MM, 12, @NAVDATE) THEN a.Marketvalue END AS Exposure_3to12Months,

    CASE WHEN ISNULL(a.CLPT_Date,a.MAT_DATE)> DATEADD(MM, 12, @NAVDATE) THEN a.Marketvalue END AS Exposure_Morethan12Months,

    CASE WHEN SUM(ABS(a.Marketvalue)) OVER (PARTITION BY Rating, ISSUER_NAME, ManagementGroupName,SCHEME_CODE ORDER BY Rating, ISSUER_NAME, ManagementGroupName,SCHEME_CODE) > 0 THEN (abs(a.Marketvalue) / SUM(ABS(a.Marketvalue)) OVER (PARTITION BY Ratin

    g, ISSUER_NAME, ManagementGroupName,SCHEME_CODE ORDER BY Rating, ISSUER_NAME, ManagementGroupName,SCHEME_CODE) * a.Mat_NavDiff) / 365 ELSE 0 END AS SCHEME_WeightedAvgMaturity,

    CASE WHEN SUM(ABS(a.Marketvalue)) OVER (PARTITION BY Rating, ISSUER_NAME ORDER BY Rating, ISSUER_NAME) > 0 THEN (abs(a.Marketvalue) / SUM(ABS(a.Marketvalue)) OVER (PARTITION BY Rating, ISSUER_NAME ORDER BY Rating, ISSUER_NAME) * a.Mat_NavDiff) / 36

    5 ELSE 0 END AS ISSUER_WeightedAvgMaturity,

    CASE WHEN SUM(ABS(a.Marketvalue)) OVER (PARTITION BY Rating ORDER BY Rating) > 0 THEN (abs(a.Marketvalue) / SUM(ABS(a.Marketvalue)) OVER (PARTITION BY Rating ORDER BY Rating) * a.Mat_NavDiff) / 365 ELSE 0 END AS Rating_WeightedAvgMaturity,

    CASE WHEN SUM(ABS(a.Marketvalue)) OVER (PARTITION BY Rating, ISSUER_NAME, ManagementGroupName, SCHEME_CODE ORDER BY Rating, ISSUER_NAME, ManagementGroupName, SCHEME_CODE) > 0 THEN (abs(a.Marketvalue) / SUM(ABS(a.Marketvalue)) OVER (PARTITION BY Rat

    ing, ISSUER_NAME, ManagementGroupName,SCHEME_CODE ORDER BY Rating, ISSUER_NAME, ManagementGroupName,SCHEME_CODE) * a.YIELD) ELSE 0 END AS SCHEME_Yield,

    CASE WHEN SUM(ABS(a.Marketvalue)) OVER (PARTITION BY Rating, ISSUER_NAME ORDER BY Rating, ISSUER_NAME) > 0 THEN (abs(a.Marketvalue) / SUM(ABS(a.Marketvalue)) OVER (PARTITION BY Rating, ISSUER_NAME ORDER BY Rating, ISSUER_NAME) * YIELD) ELSE 0 END A

    S Issuer_Yield,

    CASE WHEN SUM(ABS(a.Marketvalue)) OVER (PARTITION BY Rating ORDER BY Rating) > 0 THEN (abs(a.Marketvalue) / SUM(ABS(a.Marketvalue)) OVER (PARTITION BY Rating ORDER BY Rating) * YIELD) ELSE 0 END AS Rating_Yield

    FROM MAIN_CTE AS a

  • Is there a covering index on the table being added?

    Try running the proc outside of the report & examining the before and after execution plans. That should give you some clues.

    The absence of evidence is not evidence of absence.
    Martin Rees

    You can lead a horse to water, but a pencil must be lead.
    Stan Laurel

  • (1) Cluster the "FF" table on ( FileDate, ISIN ) (if it's not already clustered that way).

    (2) Some of the JOIN conditions are out of place.  Change this code:

    /*current code*/
    LEFT OUTER JOIN stg_mf_Holding AS h ON A.NAV_DATE = h.NAV_DATE
    LEFT JOIN UM_Risk_ICRA_FI_Rating_ICRA_ST_LT_Mapping_CP_CD_Table FF ON FF.FileDate = A.NAV_DATE AND FF.ISIN = B.ISIN_CODE
    AND A.SCHEME = h.SCHEME
    AND a.SECURITY = h.SECURITY
    AND a.CUSTODIAN = h.CUSTODIAN
    AND a.ASSET_TYPE = h.ASSET_TYPE
    AND a.AMC_CODE = h.AMC_CODE
    WHERE B.AssetGrpCode IN ('DB','MM')

    /*replace with this new code*/
    LEFT OUTER JOIN stg_mf_Holding AS h ON A.NAV_DATE = h.NAV_DATE
    AND A.SCHEME = h.SCHEME
    AND a.SECURITY = h.SECURITY
    AND a.CUSTODIAN = h.CUSTODIAN
    AND a.ASSET_TYPE = h.ASSET_TYPE
    AND a.AMC_CODE = h.AMC_CODE
    LEFT JOIN UM_Risk_ICRA_FI_Rating_ICRA_ST_LT_Mapping_CP_CD_Table FF ON FF.FileDate = A.NAV_DATE AND FF.ISIN = B.ISIN_CODE
    WHERE B.AssetGrpCode IN ('DB','MM')

    SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".

  • An execution plan is your best friend when it comes to understanding what's happening on a query like this, especially after you change it.

    "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

  • Recommendation: When posting performance issues, please follow the guidelines posted in the article at the 2nd link in my signature line below.

     

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

Viewing 5 posts - 1 through 4 (of 4 total)

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