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:
-- Create date:
-- 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
Jenith ThakkarPosted Dec 16, 2019, 6:31 AM
You can do that in this way