March 26, 2008 at 6:31 am
Hi All,
I have to tune a SP which has more than 3000 lines.
Below thecode is part of that procedure.
Code :
---------------------------------------------
select
@sManagerID, substring(convert(varchar,@dMonth,101),1,2),right(convert(varchar,@dMonth,101),4),
@_sPlatform, @sReturnSeriesType,getdate() ,@sCurrency,@sMonthYear,@sStatus,
case when @nPlatformAssets = 0 then null else @nPlatformAssets end,
case when @nInstitutionAssets = 0 then null else @nInstitutionAssets end,
case when @nAnalystScore = 0 then null else @nAnalystScore end,
case when @nManagerFee = 0 then null else @nManagerFee end,
case when @nExpectedAlpha = 0 then null else @nExpectedAlpha end,
case when @nTrackingError = 0 then null else @nTrackingError end,
case when @nNER_QTD = 0 then null else @nNER_QTD end,
case when @nNER_YTD = 0 then null else @nNER_YTD end,
case when @nNER_ITD = 0 then null else @nNER_ITD end,
case when @nNER_1Month = 0 then null else @nNER_1Month end,
case when @nNER_3Month = 0 then null else @nNER_3Month end,
case when @nNER_6Month = 0 then null else @nNER_6Month end,
case when @nNER_9Month = 0 then null else @nNER_9Month end,
case when @nNER_1Year = 0 then null else @nNER_1Year end,
case when @nNER_2Year = 0 then null else @nNER_2Year end,
case when @nNER_3Year = 0 then null else @nNER_3Year end,
case when @nNER_5Year = 0 then null else @nNER_5Year end,
case when @nNER_7Year = 0 then null else @nNER_7Year end,
case when @nNER_10Year = 0 then null else @nNER_10Year end,
case when @nGER_QTD = 0 then null else @nGER_QTD end,
case when @nGER_YTD = 0 then null else @nGER_YTD end,
case when @nGER_ITD = 0 then null else @nGER_ITD end,
case when @nGER_1Month = 0 then null else @nGER_1Month end,
case when @nGER_3Month = 0 then null else @nGER_3Month end,
case when @nGER_6Month = 0 then null else @nGER_6Month end,
case when @nGER_9Month = 0 then null else @nGER_9Month end,
case when @nGER_1Year = 0 then null else @nGER_1Year end,
case when @nGER_2Year = 0 then null else @nGER_2Year end,
case when @nGER_3Year = 0 then null else @nGER_3Year end,
case when @nGER_5Year = 0 then null else @nGER_5Year end,
case when @nGER_7Year = 0 then null else @nGER_7Year end,
case when @nGER_10Year = 0 then null else @nGER_10Year end,
case when @nNER_Jan1 = 0 then null else @nNER_Jan1 end,
case when @nNER_Jan2 = 0 then null else @nNER_Jan2 end,
case when @nNER_Jan3 = 0 then null else @nNER_Jan3 end,
case when @nNER_Jan4 = 0 then null else @nNER_Jan4 end,
case when @nNER_Jan5 = 0 then null else @nNER_Jan5 end,
case when @nNER_Jan6 = 0 then null else @nNER_Jan6 end,
case when @nGER_Jan1 = 0 then null else @nGER_Jan1 end,
case when @nGER_Jan2 = 0 then null else @nGER_Jan2 end,
case when @nGER_Jan3 = 0 then null else @nGER_Jan3 end,
case when @nGER_Jan4 = 0 then null else @nGER_Jan4 end,
case when @nGER_Jan5 = 0 then null else @nGER_Jan5 end,
case when @nGER_Jan6 = 0 then null else @nGER_Jan6 end,
case when @nZScoreGross_ITD = 0 then null else @nZScoreGross_ITD end,
case when @nZScoreGross_3Month = 0 then null else @nZScoreGross_3Month end,
case when @nZScoreGross_6Month = 0 then null else @nZScoreGross_6Month end,
case when @nZScoreGross_9Month = 0 then null else @nZScoreGross_9Month end,
case when @nZScoreGross1 = 0 then null else @nZScoreGross1 end,
case when @nZScoreGross2 = 0 then null else @nZScoreGross2 end,
case when @nZScoreGross3 = 0 then null else @nZScoreGross3 end,
case when @nZScoreGross5 = 0 then null else @nZScoreGross5 end,
case when @nZScoreNet_ITD = 0 then null else @nZScoreNet_ITD end,
case when @nZScoreNet_3Month = 0 then null else @nZScoreNet_3Month end,
case when @nZScoreNet_6Month = 0 then null else @nZScoreNet_6Month end,
case when @nZScoreNet_9Month = 0 then null else @nZScoreNet_9Month end,
case when @nZScoreNet1 = 0 then null else @nZScoreNet1 end,
case when @nZScoreNet2 = 0 then null else @nZScoreNet2 end,
case when @nZScoreNet3 = 0 then null else @nZScoreNet3 end,
case when @nZScoreNet5 = 0 then null else @nZScoreNet5 end,
case when @nTrackingError_ITD = 0 then null else @nTrackingError_ITD end,
case when @nCalculatedTE2 = 0 then null else @nCalculatedTE2 end,
case when @nCalculatedTE3 = 0 then null else @nCalculatedTE3 end,
case when @nCalculatedTE5 = 0 then null else @nCalculatedTE5 end,
case when @nCalculatedTE7 = 0 then null else @nCalculatedTE7 end,
case when @nCalculatedTE10 = 0 then null else @nCalculatedTE10 end,
case when @nGross_QTD_Bch = 0 then null else @nGross_QTD_Bch end,
case when @nGross_YTD_Bch = 0 then null else @nGross_YTD_Bch end,
case when @nGross_ITD_Bch = 0 then null else @nGross_ITD_Bch end,
case when @nGross_1Month_Bch = 0 then null else @nGross_1Month_Bch end,
case when @nGross_3Month_Bch = 0 then null else @nGross_3Month_Bch end,
case when @nGross_6Month_Bch = 0 then null else @nGross_6Month_Bch end,
case when @nGross_9Month_Bch = 0 then null else @nGross_9Month_Bch end,
case when @nGross_1Year_Bch = 0 then null else @nGross_1Year_Bch end,
case when @nGross_2Year_Bch = 0 then null else @nGross_2Year_Bch end,
case when @nGross_3Year_Bch = 0 then null else @nGross_3Year_Bch end,
case when @nGross_5Year_Bch = 0 then null else @nGross_5Year_Bch end,
case when @nGross_7Year_Bch = 0 then null else @nGross_7Year_Bch end,
case when @nGross_10Year_Bch = 0 then null else @nGross_10Year_Bch end,
case when @nGross_Jan1_Bch = 0 then null else @nGross_Jan1_Bch end,
case when @nGross_Jan2_Bch = 0 then null else @nGross_Jan2_Bch end,
case when @nGross_Jan3_Bch = 0 then null else @nGross_Jan3_Bch end,
case when @nGross_Jan4_Bch = 0 then null else @nGross_Jan4_Bch end,
case when @nGross_Jan5_Bch = 0 then null else @nGross_Jan5_Bch end,
case when @nGross_Jan6_Bch = 0 then null else @nGross_Jan6_Bch end,
case when @nGross_QTD_Mgr = 0 then null else @nGross_QTD_Mgr end,
case when @nGross_YTD_Mgr = 0 then null else @nGross_YTD_Mgr end,
case when @nGross_ITD = 0 then null else @nGross_ITD end,
case when @nGross_1Month_Mgr = 0 then null else @nGross_1Month_Mgr end,
case when @nGross_3Month_Mgr = 0 then null else @nGross_3Month_Mgr end,
case when @nGross_6Month_Mgr = 0 then null else @nGross_6Month_Mgr end,
case when @nGross_9Month_Mgr = 0 then null else @nGross_9Month_Mgr end,
case when @nGross_1Year_Mgr = 0 then null else @nGross_1Year_Mgr end,
case when @nGross_2Year_Mgr = 0 then null else @nGross_2Year_Mgr end,
case when @nGross_3Year_Mgr = 0 then null else @nGross_3Year_Mgr end,
case when @nGross_5Year_Mgr = 0 then null else @nGross_5Year_Mgr end,
case when @nGross_7Year_Mgr = 0 then null else @nGross_7Year_Mgr end,
case when @nGross_10Year_Mgr = 0 then null else @nGross_10Year_Mgr end,
case when @nGross_Jan1_Mgr = 0 then null else @nGross_Jan1_Mgr end,
case when @nGross_Jan2_Mgr = 0 then null else @nGross_Jan2_Mgr end,
case when @nGross_Jan3_Mgr = 0 then null else @nGross_Jan3_Mgr end,
case when @nGross_Jan4_Mgr = 0 then null else @nGross_Jan4_Mgr end,
case when @nGross_Jan5_Mgr = 0 then null else @nGross_Jan5_Mgr end,
case when @nGross_Jan6_Mgr = 0 then null else @nGross_Jan6_Mgr end,
case when @nNet_1Month_Mgr = 0 then null else @nNet_1Month_Mgr end,
case when @nNet_ITD = 0 then null else @nNet_ITD end,
case when @nNet_QTD_Mgr = 0 then null else @nNet_QTD_Mgr end,
case when @nNet_YTD_Mgr = 0 then null else @nNet_YTD_Mgr end
-------------------------------------
My Questions:
1) Can we use more CASE statement in a single SELECT ? will it affect the performance ?
2) is there any alternate way to achieve the same task?
Inputs are welcome !
karthik
March 26, 2008 at 6:55 am
karthikeyan (3/26/2008)
Hi All,I have to tune a SP which has more than 3000 lines.
...
My Questions:
1) Can we use more CASE statement in a single SELECT ? will it affect the performance ?
2) is there any alternate way to achieve the same task?
Inputs are welcome !
1: In the above code part you have over 100 separate CASE statements already 🙂 You can have up to 4096 elements in a select statement, so that is your limit.
2: what is exactly what you are trying to achieve? Where is the above statement used? Is it a report? Is it selected into another table? ...
Regards,
Andras
March 26, 2008 at 7:33 am
Not real sure it'll increase performance because CASE statements in the SELECT list are normally pretty fast... BUT, things like the following...
case when @nPlatformAssets = 0 then null else @nPlatformAssets end,
... can simply be rewritten as...
NULLIF(@nPlatformAssets,0) AS nPlatformAssets,
The real fact of the matter is, it looks like this code is RBAR... on steroids. If this is for a GUI, likely not a problem as GUI's tend to be single row processors anyway. But, if this is for batch code, you've got huge problems and the code likely needs to be rewritten.
The other thing is like I said earlier... the CASE statements you've shown are likely NOT the performance problem. There's something else... if you want to attach the sproc as a text file, we can take a look and make some additional recommendations.
--Jeff Moden
Change is inevitable... Change for the better is not.
March 26, 2008 at 7:44 am
If this IS for a GUI application, then someone needs to hit the books and read up on "normalization".
Exactly how many parameters and/or variables (can't tell what these are) do you have?
Personally, I'd apply a Dr. Kevorkian to this app (meaning - take it out back and shoot it), and start over. There has GOT to be a better way....
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
March 26, 2008 at 8:00 am
Matt Miller (3/26/2008)
Personally, I'd apply a Dr. Kevorkian to this app (meaning - take it out back and shoot it), and start over. There has GOT to be a better way....
More fun to find the original developer(s) and take them out for a pork chop dinner 😛
--Jeff Moden
Change is inevitable... Change for the better is not.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply