question

TZacks-2728 avatar image
0 Votes"
TZacks-2728 asked EchoLiu-msft commented

How to optimize speed for a sql server function

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

sql-server-transact-sql
· 4
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

@TZacks-2728,

You have a decent version of SQL Server 2017, though you need to install a latest CU23.
So you can use STRING_SPLIT() function out-of-the-box instead of the proprietary SplitStringToTable() function.

0 Votes 0 ·
TZacks-2728 avatar image TZacks-2728 YitzhakKhabinsky-0887 ·

STRING_SPLIT() will be very faster compared with my custom function SplitStringToTable() ?

0 Votes 0 ·

Just tests will reveal the actual performance gains.

0 Votes 0 ·

Please also remember to accept the answers if they helped. Your action would be helpful to other users who encounter the same issue and read this thread.

Regards
Echo

0 Votes 0 ·
SQLRockstar avatar image
0 Votes"
SQLRockstar answered

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

5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

AndreiFomitchev avatar image
0 Votes"
AndreiFomitchev answered AndreiFomitchev edited
  • 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 @code 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



















5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

EchoLiu-msft avatar image
0 Votes"
EchoLiu-msft answered

Hi @TZacks-2728,

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.


5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.