I have attached a sql server function which is taking long time when execute once for each row. if my sql return 100 result then my below function is calling 100 times and which causing delay for sql result set.
what my function does. in one of my table field has value stored like "5525~112617"/"5525~112616" these digits are various ID of Section and Lineitem
my below function get Section & Lineitem name from ID and return this way data "Consensus Model~Cost of Sales"/"Consensus Model~Total Revenue"
so my below function take input like "5525~112617"/"5525~112616" and return output like "Consensus Model~Cost of Sales"/"Consensus Model~Total Revenue"
i am using sql server version Microsoft SQL Server 2017 (RTM) - 14.0.1000.169 (X64) Aug 22 2017 17:04:49 Copyright (C) 2017 Microsoft Corporation Standard Edition (64-bit) on Windows Server 2019 Standard 10.0 <X64> (Build 17763: )
see my function code and guide me how we can restructure code of function as a result it will be much faster when it will execute for each row.
see my function code
CREATE FUNCTION [dbo].[fn_TranslateFormulaToText]
(
@TickerID NVARCHAR(MAX),
@Formula NVARCHAR(MAX),
@IsCrossCalc CHAR(1)
)
RETURNS NVARCHAR(MAX)
AS
BEGIN
/*
This function can parse standard formula, Bluemetrix formula and CrossCal and name with ID in formula
*/
DECLARE @StartIndex INT,@EndIndex INT,@MasterID INT
Declare @TempFormula NVARCHAR(MAX),@tmpFormula NVARCHAR(MAX)
DECLARE @Section NVARCHAR(MAX),@LineItem NVARCHAR(MAX),@Period NVARCHAR(MAX)
DECLARE @SectionID VARCHAR(MAX),@LineItemID VARCHAR(MAX),@PeriodID VARCHAR(MAX)
SET @TempFormula=@Formula
SET @StartIndex=1
SET @EndIndex=0
DECLARE @Temptbl table ( ID INT, DATA NVARCHAR(MAX))
INSERT INTO @Temptbl(ID,DATA)
SELECT ID,[DATA] FROM SplitStringToTable(@Formula,'"') WHERE TRIM([DATA])<>''
SELECT @EndIndex=MAX(ID) FROM @Temptbl
WHILE @StartIndex <=@EndIndex
BEGIN
SELECT @tmpFormula=DATA FROM @Temptbl WHERE ID=@StartIndex
IF CHARINDEX('~', @tmpFormula)>0
BEGIN
IF @IsCrossCalc = 'N'
BEGIN
SELECT @SectionID= CAST([Data] AS INTEGER) FROM SplitStringToTable(@tmpFormula,'~') WHERE TRIM([DATA])<>'' AND ID=1
SELECT @LineItemID=CAST([Data] AS INTEGER) FROM SplitStringToTable(@tmpFormula,'~') WHERE TRIM([DATA])<>'' AND ID=2
IF @SectionID<>'' AND @LineItemID <> ''
BEGIN
SELECT @Section=Section FROM tblSectionTemplate WHERE SectionID=@SectionID AND TickerID=@TickerID
SELECT @LineItem=LineItem FROM TblLineItemTemplate WHERE ID=@LineItemID AND TickerID=@TickerID
IF @Section <> '' AND @LineItem <> ''
BEGIN
--SET @TempFormula=REPLACE(@TempFormula,@tmpFormula, (@Section+'~'+@LineItem))
SET @TempFormula=STUFF(@TempFormula,CHARINDEX(@tmpFormula,@TempFormula),LEN(@tmpFormula),(@Section+'~'+@LineItem+'~9999'))
SET @Section=''
SET @LineItem=''
END
ELSE
BEGIN
RETURN @Formula
END
END
END
ELSE IF @IsCrossCalc = 'Y'
BEGIN
SELECT @SectionID= CAST([Data] AS INTEGER) FROM SplitStringToTable(@tmpFormula,'~') WHERE TRIM([DATA])<>'' AND ID=1
SELECT @LineItemID=CAST([Data] AS INTEGER) FROM SplitStringToTable(@tmpFormula,'~') WHERE TRIM([DATA])<>'' AND ID=2
SELECT @PeriodID= CAST([Data] AS INTEGER) FROM SplitStringToTable(@tmpFormula,'~') WHERE TRIM([DATA])<>'' AND ID=3
IF @SectionID<>'' AND @LineItemID <> '' AND @PeriodID <> ''
BEGIN
SELECT @Section=Section FROM tblSectionTemplate WHERE SectionID=@SectionID AND TickerID=@TickerID
SELECT @LineItem=LineItem FROM TblLineItemTemplate WHERE ID=@LineItemID AND TickerID=@TickerID
SELECT @MasterID=ID FROM tblCalenderMaster WHERE TickerID=@TickerID
SELECT @Period=Period FROM tblCalenderDetail WHERE ID=@PeriodID AND MasterID=@MasterID
IF @Section <> '' AND @LineItem <> '' AND @Period <> ''
BEGIN
--SET @TempFormula=REPLACE(@TempFormula,@tmpFormula, (@Section+'~'+@LineItem+'~'+@Period))
SET @TempFormula=STUFF(@TempFormula,CHARINDEX(@tmpFormula,@TempFormula),LEN(@tmpFormula),(@Section+'~'+@LineItem+'~'+@Period+'~9999'))
SET @Section=''
SET @LineItem=''
SET @Period =''
END
ELSE
BEGIN
RETURN @Formula
END
END
END
END
SET @StartIndex=@StartIndex+1
END
RETURN @TempFormula
END
Please suggest some code restructure as a result performance will be much good. Thanks