Please help me to optimize below stored procedure

CREATE procedure [dbo].[usp_rpt_DigitalChannelConnectivity]-- 532,'

','ALL','

','

@p_WeekIds int,

@p_OperatorTypeIds Varchar(MAX)='',

@p_MarketIds Varchar(MAX)='',

@p_ChannelIds Varchar(MAX)='',

@p_ClientIds Varchar(MAX)=''

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

SET ARITHABORT OFF

Two things you may want to change:

1. Performance of conditional join really depends on the data in the table. Please check the actual query execution plan for that specific query. If that is a problem, try to do seperate

joins (without condition) and do Union for the same. Also, if possible, try to limit nested inner queries. So try simplifying these conditional joins.

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

2. Some of the select statements, like below, you are doing inline queries again and again for almost each record. instead of doing that, try to get the results of below queries into temp table along with some fields like ID (sort of commmon fields) and then you can join it back to Result R2 table and you should see performance improvements.

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

Instead of above queries as in Inline statements try doing union and select appropriate field (using case statements) in your SELECT clause ( make sure to handle group by fields) .

SELECT ID,SUM(Operator_Weightage) [somecolname]
into #tempOPW
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
UNION
SELECT ID,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 Replies

Know the answer? Post it — somebody with the same question will find it here.

Tapan Patel9y ago
Accepted answer
Two things you may want to change:

1. Performance of conditional join really depends on the data in the table. Please check the actual query execution plan for that specific query. If that is a problem, try to do seperate
joins (without condition) and do Union for the same. Also, if possible, try to limit nested inner queries. So try simplifying these conditional joins.
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
2. Some of the select statements, like below, you are doing inline queries again and again for almost each record. instead of doing that, try to get the results of below queries into temp table along with some fields like ID (sort of commmon fields) and then you can join it back to Result R2 table and you should see performance improvements.
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

and

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

Instead of above queries as in Inline statements try doing union and select appropriate field (using case statements) in your SELECT clause ( make sure to handle group by fields) .
SELECT ID,SUM(Operator_Weightage) [somecolname]
into #tempOPW
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
UNION
SELECT ID,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
0
Manish Kumar9y ago
How could we optimize your procedure without an execution plan, index structure , no. of rows in tables etc
0