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

Question

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

Smash125

Hall of Fame

Points: 3562
More actions
August 22, 2023 at 7:38 am

#4267842

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

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

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

Phil Parkin SSC Guru Points: 246960 More actions · Answer 1

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
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin

ScottPletcher SSC Guru Points: 101095 More actions · Answer 2

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

Grant Fritchey SSC Guru Points: 398882 More actions · Answer 3

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

Jeff Moden SSC Guru Points: 1004323 More actions · Answer 4

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)