Please help me to optimize below stored procedure

Question

CREATE procedure [dbo].[usp_rpt_DigitalChannelConnectivity]-- 532,'<R><P Id ="4"></P></R>','ALL','<R><P Id ="47"></P><P Id ="69"></P><P Id ="70"></P><P Id ="186"></P><P Id ="273"></P><P Id ="306"></P><P Id ="468"></P></R>','<R><P Id="127"></P></R>'

@p_WeekIds int,

@p_OperatorTypeIds Varchar(MAX)='',

@p_MarketIds Varchar(MAX)='',

@p_ChannelIds Varchar(MAX)='',

@p_ClientIds Varchar(MAX)=''

AS

SET NOCOUNT ON

SET ARITHABORT ON

BEGIN

print 'step 1'

print getdate()

DECLARE @t_ChannelIds Table (Id Int)

DECLARE @t_OperatorTypeIds Table (Id Int)

DECLARE @t_MarketIds Table (Id Int)

DECLARE @t_ClientIds Table (Id Int)

INSERT INTO @t_ChannelIds EXECUTE dbo.usp_ParameterIDS @p_ChannelIds

INSERT INTO @t_OperatorTypeIds EXECUTE dbo.usp_ParameterIDS @p_OperatorTypeIds

INSERT INTO @t_MarketIds EXECUTE dbo.usp_ParameterIDS @p_MarketIds

INSERT INTO @t_ClientIds EXECUTE dbo.usp_ParameterIDS @p_ClientIds

print 'step 2'

print getdate()

IF OBJECT_ID(N'tempdb..#Result') IS NOT NULL

BEGIN

DROP TABLE #Result

END

SELECT Channel_Id,Channel_Name,Market_Id,Market_Name,Operator_Id,Operator_Line_Id,

Operator_Type_Id,Operator_Type_Name,Operator_Weightage,Week_Id,Week_Number,

transchannel,[Rank]

INTO #Result FROM

(SELECT temp.Channel_Id,temp.Channel_Name,temp.Market_Id,temp.Market_Name,temp.Operator_Id,temp.Operator_Line_Id,

temp.Operator_Type_Id,temp.Operator_Type_Name,temp.Operator_Weightage,temp.Week_Id,temp.Week_Number,

ISNULL(Trn.Channel_Id,0) as transchannel,

rank() over ( partition by Trn.Week_Id,Trn.Operator_id,Trn.Operator_Line_Id,Trn.Channel_id,Trn.Market_Id

order by CAST(trn.EPG_Number as int)) as [Rank]

from

(SELECT OptWtg.Market_Id,OptWtg.Market_Name,OptWtg.Operator_Id,OptWtg.Operator_Line_Id,

OptWtg.Operator_Type_Id,OptWtg.Operator_Type_Name,OptWtg.Week_Id,OptWtg.Week_Number,OptWtg.Operator_Weightage,

CM.Channel_Id,CM.Channel_Name from

(SELECT Wtg.Market_Id,Mkt.Market_Name,Wtg.Operator_Id,Wtg.Operator_Line_Id,

Wtg.Operator_Type_Id,OTM.Operator_Type_Name,Wtg.Week_Id,WM.Week_Number,Wtg.Operator_Weightage from dbo.tblOperatorWeightage Wtg

INNER JOIN @t_OperatorTypeIds k ON (case when k.Id=0 then Wtg.Operator_Type_Id else k.Id end)=Wtg.Operator_Type_Id

INNER JOIN dbo.vw_OperatorTypeMaster OTM On OTM.Operator_Type_Id=Wtg.Operator_Type_Id

INNER JOIN @t_MarketIds m ON (case when m.Id=0 then Wtg.Market_Id else m.Id end)=Wtg.Market_Id

INNER JOIN dbo.vw_TRPMarket Mkt On Mkt.Market_Id=Wtg.Market_Id

INNER JOIN dbo.vw_WeekMaster WM ON WM.Week_Id=Wtg.Week_Id

where Wtg.Week_Id=@p_WeekIds and Wtg.Weightage_Type_Id=103)OptWtg

CROSS JOIN @t_ChannelIds C

INNER JOIN dbo.vw_ChannelMaster CM ON (case when C.Id=0 then CM.Channel_Id else C.Id end)=CM.Channel_Id ) temp

Left Join dbo.vw_OperatorMasterMarketDetails Trn On Trn.Market_Id=temp.Market_Id and Trn.Operator_Id=temp.Operator_Id and Trn.Operator_Line_Id=temp.Operator_Line_Id

and Trn.Operator_Type_id=temp.Operator_Type_Id and Trn.Week_Id=temp.Week_Id and Trn.Channel_Id=temp.Channel_Id)A

WHERE A.[Rank]=1

GROUP BY

Channel_Id,Channel_Name,Market_Id,Market_Name,Operator_Id,Operator_Line_Id,

Operator_Type_Id,Operator_Type_Name,Operator_Weightage,Week_Id,Week_Number,

transchannel,

[Rank]

print 'step 3'

print getdate()

IF((Select COUNT(*) from @t_ClientIds)=1 and (Select Id from @t_ClientIds)<>0)

BEGIN

select r2.Market_Name,r2.Week_Number,r2.Channel_Name,

ISNULL(ROUND((select SUM(Operator_Weightage) from #Result r1 where r1.Market_Id=r2.Market_Id and r1.Week_Id=r2.Week_Id and r1.Channel_Id=r2.Channel_Id and r1.transchannel<>0),2),0) as Available,

ISNULL(ROUND((select SUM(Operator_Weightage) from #Result r1 where r1.Market_Id=r2.Market_Id and r1.Week_Id=r2.Week_Id and r1.Channel_Id=r2.Channel_Id and r1.transchannel=0),2),0) as Unavailable,

((ISNULL(ROUND((select SUM(Operator_Weightage) from #Result r1 where r1.Market_Id=r2.Market_Id and r1.Week_Id=r2.Week_Id and r1.Channel_Id=r2.Channel_Id and r1.transchannel<>0),2),0))+

(ISNULL(ROUND((select SUM(Operator_Weightage) from #Result r1 where r1.Market_Id=r2.Market_Id and r1.Week_Id=r2.Week_Id and r1.Channel_Id=r2.Channel_Id and r1.transchannel=0),2),0))) as Total

from #Result r2

INNER JOIN dbo.vw_ClientChannelDetails CCD ON CCD.Channel_Id=r2.Channel_Id

INNER JOIN @t_ClientIds CL ON (case when CL.Id=0 then CCD.Client_Id else CL.Id end)=CCD.Client_Id

group by r2.Market_Name,r2.Week_Id,r2.Channel_Name,r2.Market_Id,r2.Week_Id,r2.Channel_Id,r2.Week_Number,CCD.Sort_Order

order by r2.Market_Name,r2.Week_Id,CCD.Sort_Order

END

Else

BEGIN

select r2.Market_Name,r2.Week_Number,r2.Channel_Name,

ISNULL(ROUND((select SUM(Operator_Weightage) from #Result r1 where r1.Market_Id=r2.Market_Id and r1.Week_Id=r2.Week_Id and r1.Channel_Id=r2.Channel_Id and r1.transchannel<>0),2),0) as Available,

ISNULL(ROUND((select SUM(Operator_Weightage) from #Result r1 where r1.Market_Id=r2.Market_Id and r1.Week_Id=r2.Week_Id and r1.Channel_Id=r2.Channel_Id and r1.transchannel=0),2),0) as Unavailable,

((ISNULL(ROUND((select SUM(Operator_Weightage) from #Result r1 where r1.Market_Id=r2.Market_Id and r1.Week_Id=r2.Week_Id and r1.Channel_Id=r2.Channel_Id and r1.transchannel<>0),2),0))+

(ISNULL(ROUND((select SUM(Operator_Weightage) from #Result r1 where r1.Market_Id=r2.Market_Id and r1.Week_Id=r2.Week_Id and r1.Channel_Id=r2.Channel_Id and r1.transchannel=0),2),0))) as Total

from #Result r2

group by r2.Market_Name,r2.Week_Id,r2.Channel_Name,r2.Market_Id,r2.Week_Id,r2.Channel_Id,r2.Week_Number

order by r2.Market_Name,r2.Week_Id,r2.Channel_Name

END

print 'step 4'

print getdate()

IF OBJECT_ID(N'tempdb..#Result') IS NOT NULL

BEGIN

DROP TABLE #Result

END

END

SET ARITHABORT OFF