How to optimize speed for a sql server function

Question

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

Answer 1

That's how functions work, they will be applied to each row in the result set. So, 100 rows, 100 calls.

Consider using a stored procedure instead. Or you could add a column to your existing tables and store the text output there.

HTH

Answer 2

-- The result:
"Consensus Model~Cost of Sales"/"Consensus Model~Cost of Sales"/"Consensus Model~Total Revenue"

DECLARE @text NVARCHAR(MAX) = ''
SELECT @text = @text+'"'+section+'~'+line+'"'+'/'
FROM codes c JOIN xref x ON c.Code_Id = x.Code_Id
JOIN sections s ON s.Section_Id = x.Section_Id
JOIN lineItems l ON l.LineItem_Id = x.LineItem_Id
ORDER BY Id

SET @text = LEFT(@text,Len(@text)-1)

PRINT @text

=== How I got that:
USE TestDB2
GO
-- DROP TABLE xref; DROP TABLE lineitems;
-- DROP TABLE sections; DROP TABLE codes;
GO
CREATE TABLE codes(Code_Id INT PRIMARY KEY IDENTITY,
Code NVARCHAR(MAX))
CREATE TABLE sections(Section_Id INT PRIMARY KEY,
Section NVARCHAR(MAX))
CREATE TABLE lineItems(LineItem_Id INT PRIMARY KEY,
Line NVARCHAR(MAX))
CREATE TABLE xref(Id INT PRIMARY KEY Identity,
Changed DateTime, Code_Id INT, Section_Id INT, LineItem_Id INT)
GO
DECLARE @CodeId INT
-- It will parse any number of code groups
INSERT INTO codes (code) VALUES
('"5525~112617"/"5525~112617"/"5525~112616"');
SET @CodeId = @@Identity; -- Don't separate from previos line
INSERT INTO sections (Section_ID, Section)
VALUES (5525,'Consensus Model')
INSERT INTO lineItems(LineItem_ID, Line)
VALUES (112616,'Total Revenue')
INSERT INTO lineItems(LineItem_ID, Line)
VALUES (112617,'Cost of Sales')
DECLARE @Aidan Wick NVARCHAR(MAX), @code1 NVARCHAR(MAX),
@IsCrossCalc CHAR = 'N'
DECLARE cr CURSOR FAST_FORWARD READ_ONLY FOR
SELECT code FROM codes ORDER BY Code_id

OPEN cr; -- Cycle by "mmmm~nnnnn"     
WHILE 1=1 BEGIN    
    FETCH NEXT FROM cr INTO @code    
    IF @@FETCH_STATUS <> 0 BREAK    
    PRINT @code -- I keep it - it shows progress   
    DECLARE @t TABLE (ID INT IDENTITY, DATA NVARCHAR(MAX))    

    INSERT INTO @t (DATA)    
    SELECT value FROM string_split(@code,'"')   
    WHERE value NOT IN ('','/');    

    DECLARE cr1 CURSOR FAST_FORWARD READ_ONLY FOR    
     SELECT DATA FROM @t ORDER BY ID    
     OPEN cr1    
     WHILE 1=1 BEGIN -- Cycle by values 1st,2nd    
          FETCH NEXT FROM cr1 INTO @code1    
          IF @@FETCH_STATUS <> 0 BREAK    
          PRINT 'Code1: '+@code1    
          -- IDENTITY Will enumerate the lines    
          DECLARE @t2 TABLE(ID INT IDENTITY, Num INT,   
          DATA NVARCHAR(MAX));    

          INSERT INTO @t2 (DATA)    
          SELECT value FROM string_split(@code1,'~');    
          SELECT @Code1    
          -- Nums - 1, 2, ...    

          UPDATE @t2 SET Num = ID - (SELECT Min(ID) FROM @t2 ) + 1    

          SELECT * FROM @t2    

          DECLARE @Id1 NVARCHAR(50) , @Id2 NVARCHAR(50),     
          @SectionId INT, @LineItemId INT     

          SELECT @Id1 = (SELECT DATA FROM @t2 WHERE Num=1)    
          SELECT @Id2 = (SELECT DATA FROM @t2 WHERE Num=2)    

           DELETE @t2 -- For the next cycle    

           PRINT '@Id1:@Id2 - ['+@Id1+']:['+@Id2+']'    
           SET @SectionId = Convert(INT, @Id1)    
           SET @LineItemId = Convert(INT, @Id2)    

           IF @IsCrossCalc = 'N' BEGIN    
               DECLARE @Count INT = 0    
               SELECT @Count FROM xref WHERE Code_Id = @CodeId    
               AND Section_Id = @SectionId   
               AND LineItem_Id = @LineItemId    
               IF @Count = 0 BEGIN -- Avoiding duplicates    
                   INSERT INTO xref(Code_Id, Changed, Section_Id,   
                   LineItem_Id) VALUES(@CodeId, GetDate(),   
                   @SectionId, @LineItemId)    
                END    
           END    
       END    
       CLOSE cr1    
       DEALLOCATE cr1    
    END    
    CLOSE cr    
    DEALLOCATE cr    
   
-- Data    
SELECT * FROM codes    
SELECT * FROM xref    
SELECT * FROM sections    
SELECT * FROM lineItems    

-- All together - it works quickly    
SELECT * FROM codes c JOIN xref x ON c.Code_Id = x.Code_Id    
JOIN sections s ON s.Section_Id = x.Section_Id    
JOIN lineItems l ON l.LineItem_Id = x.LineItem_Id    

SELECT id,code,section,line FROM codes c JOIN xref x ON c.Code_Id = x.Code_Id
JOIN sections s ON s.Section_Id = x.Section_Id
JOIN lineItems l ON l.LineItem_Id = x.LineItem_Id

id code section line
1 "5525~112617"/"5525~112617"/"5525~112616" Consensus Model Cost of Sales
2 "5525~112617"/"5525~112617"/"5525~112616" Consensus Model Cost of Sales
3 "5525~112617"/"5525~112617"/"5525~112616" Consensus Model Total Revenue

Answer 3

Hi @T.Zacks ,

The performance of user-defined functions is generally poor. As many data as you have, the user-defined functions will be called how many times. Therefore, this is a relatively inefficient choice in query.

Custom functions and even system functions can easily lead to invalid indexes, so they should be avoided as much as possible, especially in on and where.

If a function is used in the SELECT or WHERE, the function can be called many, many times. If the function is very resource-intensive, it could be causing your query to be very slow – and you would never see the execution of the function within the execution plan of the calling query.

As people suggest, you can use stored procedures instead, and you can also try to create some suitable indexes to improve query performance.

If you have any question, please feel free to let me know.

Regards
Echo

---

If the answer is helpful, please click "Accept Answer" and upvote it.
Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.