stored procedure execution taking more time

Question

Hi,

 

I have stored proc as below and its taking a lot tome to complete exection.Any one have any Idea about this stored proc code, where is the acutal proble area:

 

-- =============================================

-- Author: <Author,,Name>

-- Create date: <Create Date,,>

-- Description: <Description,,>

-- =============================================

CREATE PROCEDURE [dbo].[RecalculateEpiForCombinedNodes_Imp]

@ForecastID NUMERIC(18, 0),

@ScenarioID NUMERIC(18, 0)

AS

BEGIN

SET NOCOUNT ON;

IF OBJECT_ID('tempdb..#tblPatientNumbers') IS NOT NULL

DROP TABLE #tblPatientNumbers

CREATE TABLE #tblPatientNumbers

(

FCPSTempID numeric(18),

InputParameterName nvarchar(250),

ItemType nvarchar(50),

ParentParameterID numeric(18),

ParentParameterName nvarchar(250),

ParentItemType nvarchar(50),

Period nvarchar(30),

Periodinput nvarchar(50),

SegmentID numeric(18),

LineID numeric(18),

DisplayOrder numeric(18),

InputFormat nvarchar(2)

)

--input data

IF OBJECT_ID('tempdb..#InputDataSet1') IS NOT NULL

DROP TABLE #InputDataSet1

CREATE TABLE #InputDataSet1

(

RelFCastInputID NUMERIC (18, 0),

ForecastID NUMERIC (18, 0) NULL,

FCPSTempID NUMERIC (18, 0) NULL,

ParameterName NVARCHAR (250) NULL,

InputParameterName NVARCHAR (250) NULL,

ParentParameterID NUMERIC (18, 0) NULL,

ParentParameterName NVARCHAR (250) NULL,

ParentItemType NVARCHAR (50) NULL,

ParamOperator NVARCHAR (10) NULL,

ParamInputType NVARCHAR (20) NULL,

InputFormat NVARCHAR (20) NULL,

Period NVARCHAR (30) NULL,

Periodinput NUMERIC (38, 20) NULL,

ItemType NVARCHAR (30) NULL,

ProdType NVARCHAR (50) NULL,

Section NVARCHAR (150) NULL,

Scenario NVARCHAR (50) NULL,

Denomination NVARCHAR (5) NULL,

ScenarioID NUMERIC (18, 0) NULL,

FormulaSeqID NUMERIC (18, 0) NULL,

EventID NUMERIC (18, 0) NULL,

LevelID NUMERIC (18, 0) NULL,

SeqID NUMERIC (18, 0) NULL

)

DECLARE

@FCPSTempID NUMERIC(18, 0),

@Delay NUMERIC(10, 2),

@CurrentSectionNumericFormat NVARCHAR (5) = NULL,

@Frequency NVARCHAR(30),

@DelayRoundDown NUMERIC (18, 0),

@DelayRoundUp NUMERIC (18, 0);

IF EXISTS (SELECT CombinationID FROM [tblForecastTemplateCombination] WHERE ForecastID = @ForecastID) -- AND CombiningParameterType NOT IN ('Segment', 'EPIParameters')

BEGIN

SET @Frequency = (SELECT TOP 1 DataFrequency FROM dbo.tblForecastSettings WHERE ForecastID = @ForecastID);

SET @CurrentSectionNumericFormat = (SELECT

TOP 1 Denomination

FROM

dbo.tblForecastInput

WHERE

ForecastID = @ForecastID AND

ScenarioID = @ScenarioID AND

Section = 'EPIParameters' AND

ISNULL(ParamInputType, 'I') != 'O' AND ISNULL(ParamInputType, 'I') != 'E');

--Pushing input data into temp table

BEGIN

DELETE FROM #InputDataSet1;

--Pulling input data to the temp table

INSERT INTO #InputDataSet1

SELECT

RelFCastInputID,

ForecastID,

FCPSTempID,

ParameterName,

InputParameterName,

ParentParameterID,

ParentParameterName,

ParentItemType,

ParamOperator,

ISNULL(ParamInputType, 'I') AS ParamInputType,

InputFormat,

Period,

(CASE

WHEN

Section = 'EPIParameters' AND ((ParamInputType = 'I' AND ( ParamOperator = 'Absolute' OR ParamOperator = '/')) OR ParamInputType = 'O')

THEN

dbo.CalculateDenomination(ISNULL(TRY_CONVERT(NUMERIC(38,20), REPLACE(REPLACE(REPLACE(Periodinput,'#DIV/0!','0'),'#VALUE!','0'),'%','')), 0), @CurrentSectionNumericFormat, 'A')

ELSE ISNULL(TRY_CONVERT(NUMERIC(38,20), REPLACE(REPLACE(REPLACE(Periodinput,'#DIV/0!','0'),'#VALUE!','0'),'%','')), 0)

END) AS Periodinput,

ItemType,

ProdType,

Section,

Scenario,

Denomination,

ScenarioID,

FormulaSeqID,

EventID,

LevelID,

SeqID

FROM

dbo.tblForecastInput

WHERE

ForecastID = @ForecastID AND

ScenarioID = @ScenarioID AND

Section IN ('EPIParameters', 'EffectiveShares')

END

-- Get latest patient numbers

EXEC [dbo].[GetInstantPatientNumbers_Imp] @ForecastID, @ScenarioID, 0, 1;

-- Relcalculate EPi using the latest patient numbers

DECLARE combinedNodes CURSOR LOCAL FOR

SELECT

temp.FCPSTempID, ISNULL(temp.[Delay], 0) AS [Delay]

FROM tblForecastTemplate temp

WHERE ForecastID=@forecastId and IsCombined = 1

ORDER BY LineID, DisplayOrder

OPEN combinedNodes

FETCH NEXT FROM combinedNodes INTO @FCPSTempID, @Delay

WHILE @@FETCH_STATUS = 0

BEGIN

IF(@Delay > 0)

BEGIN

IF OBJECT_ID('tempdb..#outputData') IS NOT NULL

DROP TABLE #outputData

SELECT

ROW_NUMBER() OVER(ORDER BY PeriodDate) AS RowID,

*

INTO #outputData

FROM

(

SELECT TOP 100 PERCENT

Period,

dbo.ConvertPeriodToDate(@Frequency, Period) AS PeriodDate,

MAX(FCPSTempID) AS FCPSTempID,

SUM(TRY_CONVERT(DECIMAL(25, 14), PeriodInput)) AS PeriodInput

FROM

((SELECT

cm.FCPSTempID, Period, PeriodInput

FROM [dbo].[tblForecastTemplateCombination] cm

JOIN #tblPatientNumbers ot ON ot.FCPSTempID = cm.CombiningFCPSTempID

WHERE

cm.ForecastID=@forecastId AND

cm.FCPSTempID = @FCPSTempID AND

(cm.CombiningParameterType != 'Segment' AND cm.CombiningParameterType != 'EPIParameters'))

UNION ALL

(Select

cm.FCPSTempID, Period, PeriodInput

FROM [dbo].[tblForecastTemplateCombination] cm

JOIN #InputDataSet1 inp ON inp.FCPSTempID = cm.CombiningParameterOutputFCPSTempID AND inp.ForecastID=cm.ForecastID

WHERE

cm.ForecastID=@forecastId AND

cm.FCPSTempID = @FCPSTempID AND

(cm.CombiningParameterType = 'Segment' OR cm.CombiningParameterType = 'EPIParameters') AND

inp.ScenarioID=@ScenarioID AND

inp.Section = 'EpiParameters')) as st

GROUP BY Period

ORDER BY PeriodDate

) AS data

SET @DelayRoundDown = FLOOR(@Delay);

SET @DelayRoundUp = CEILING(@Delay);

UPDATE

[dbo].[tblForecastInput]

SET

Periodinput = 0.00

WHERE

ForecastID = @ForecastID AND

ScenarioID = @ScenarioID AND

Section = 'EPIParameters' AND

FCPSTempID = @FCPSTempID AND

Period IN (SELECT Period FROM #outputData WHERE RowID <= @DelayRoundDown)

IF(@DelayRoundDown <> @DelayRoundUp)

BEGIN

UPDATE inp

SET inp.Periodinput = dbo.CalculateDenomination(ISNULL(com.PeriodInput, 0), 'A', @CurrentSectionNumericFormat)

FROM [dbo].[tblForecastInput] inp

LEFT JOIN

(

SELECT

main.FCPSTempID,

main.Period,

((ISNULL(mainDown.PeriodInput, 0) * (1 - (@Delay - @DelayRoundDown))) + (ISNULL(mainUp.PeriodInput, 0) * (@Delay - @DelayRoundDown))) AS PeriodInput

FROM #outputData main

LEFT JOIN #outputData mainDown ON mainDown.RowID = main.RowID - @DelayRoundDown

LEFT JOIN #outputData mainUp ON mainUp.RowID = main.RowID - @DelayRoundUp

WHERE main.RowID > @DelayRoundDown

) com ON com.FCPSTempID = inp.FCPSTempID AND com.Period = inp.Period

WHERE

inp.ForecastID = @ForecastID AND

inp.ScenarioID = @ScenarioID AND

inp.FCPSTempID = @FCPSTempID AND

inp.Section = 'EPIParameters';

END

ELSE

BEGIN

UPDATE inp

SET inp.Periodinput = dbo.CalculateDenomination(ISNULL(com.PeriodInput, 0), 'A', @CurrentSectionNumericFormat)

FROM [dbo].[tblForecastInput] inp

LEFT JOIN

(

SELECT

main.FCPSTempID, main.Period, mainDown.PeriodInput

FROM #outputData main

LEFT JOIN #outputData mainDown ON mainDown.RowID = main.RowID - @DelayRoundDown

WHERE main.RowID > @DelayRoundDown

) com ON com.FCPSTempID = inp.FCPSTempID AND com.Period = inp.Period

WHERE

inp.ForecastID = @ForecastID AND

inp.ScenarioID = @ScenarioID AND

inp.FCPSTempID = @FCPSTempID AND

inp.Section = 'EPIParameters';

END

END

ELSE

BEGIN

UPDATE inp

SET

inp.Periodinput = dbo.CalculateDenomination(ISNULL(com.PeriodInput, 0), 'A', @CurrentSectionNumericFormat)

FROM [dbo].[tblForecastInput] inp

LEFT JOIN

(

SELECT

Period, MAX(FCPSTempID) AS FCPSTempID, SUM(TRY_CONVERT(DECIMAL(25, 14), PeriodInput)) AS PeriodInput

FROM

((SELECT

cm.FCPSTempID, Period, PeriodInput

FROM [dbo].[tblForecastTemplateCombination] cm

JOIN #tblPatientNumbers ot ON ot.FCPSTempID = cm.CombiningFCPSTempID

WHERE

cm.ForecastID=@forecastId AND

cm.FCPSTempID = @FCPSTempID AND

(cm.CombiningParameterType != 'Segment' AND cm.CombiningParameterType != 'EPIParameters'))

UNION ALL

(Select

cm.FCPSTempID, Period, PeriodInput

FROM [dbo].[tblForecastTemplateCombination] cm

JOIN #InputDataSet1 inp ON inp.FCPSTempID = cm.CombiningParameterOutputFCPSTempID AND inp.ForecastID=cm.ForecastID

WHERE

cm.ForecastID=@forecastId AND

cm.FCPSTempID = @FCPSTempID AND

(cm.CombiningParameterType = 'Segment' OR cm.CombiningParameterType = 'EPIParameters') AND

inp.ScenarioID=@ScenarioID AND

inp.Section = 'EpiParameters')) as st

GROUP BY Period

) com ON com.FCPSTempID = inp.FCPSTempID AND com.Period = inp.Period

WHERE

inp.ForecastID = @ForecastID AND

inp.ScenarioID = @ScenarioID AND

inp.FCPSTempID = @FCPSTempID AND

inp.Section = 'EPIParameters';

END

UPDATE inpTemp

SET

inpTemp.Periodinput = dbo.CalculateDenomination(inp.PeriodInput, @CurrentSectionNumericFormat, 'A')

FROM #InputDataSet1 inpTemp

JOIN [dbo].[tblForecastInput] inp

ON

inp.ForecastID = inpTemp.ForecastID AND

inp.ScenarioID = inpTemp.ScenarioID AND

inp.Section = inpTemp.Section AND

inp.FCPSTempID = inpTemp.FCPSTempID AND

inp.Period = inpTemp.Period

WHERE

inpTemp.Section = 'EPIParameters' AND

inpTemp.FCPSTempID = @FCPSTempID;

--Updating the output of all the depended nodes - Segments

EXEC [dbo].[RecalculateEpiForCombinedNodesRecursive] @ForecastID, @ScenarioID, @FCPSTempID, @CurrentSectionNumericFormat;

-- Get latest patient numbers

DELETE FROM #tblPatientNumbers

EXEC [dbo].[GetInstantPatientNumbers_Imp] @ForecastID, @ScenarioID;

FETCH NEXT FROM combinedNodes INTO @FCPSTempID, @Delay

END

CLOSE combinedNodes

DEALLOCATE combinedNodes

END

IF OBJECT_ID('tempdb..#tblPatientNumbers') IS NOT NULL

DROP TABLE #tblPatientNumbers

IF OBJECT_ID('tempdb..#InputDataSet1') IS NOT NULL

DROP TABLE #InputDataSet1

IF OBJECT_ID('tempdb..#outputData') IS NOT NULL

DROP TABLE #outputData

END