Wrapper TSQL Table-valued Function is slow

Question

I built a wrapper function that currently just calls another table-valued Function but it just added a huge amount to the execution time as Client processing time. Is there a faster way to do this?

Without wrapper:

With Wrapper:

Wrapper function:

CREATE FUNCTION [console].[getCalculosRequisita]
(   

    @Disponivel BIGINT,
    @mediaDiaria float,
    @DiasStockArtigo INT, 
    @DiasAntes INT, 
    @SaidasPorMes float, 
    @QtdEncomendada2Meses BIGINT,
    @StockAtual BIGINT,
    @QtdRequisitada BIGINT,
    @caixaMinima INT

)
RETURNS @tbl TABLE 
(
    DiasAteRotura INT,
    AcaoRequisita varchar(10),
    Aconselhada BIGINT
)
AS
BEGIN

--future configuration check
--future log input

INSERT INTO @tbl SELECT DiasAteRotura, AcaoRequisita,Aconselhada
FROM [cartridge].[getCalculosRequisitaTSQL]
(
    @Disponivel ,
    @mediaDiaria ,
    @DiasStockArtigo , 
    @DiasAntes , 
    @SaidasPorMes , 
    @QtdEncomendada2Meses ,
    @StockAtual ,
    @QtdRequisitada ,
    @caixaMinima
)


--future log output

RETURN
END

GO

Thom A · Accepted Answer · 2021-01-28 12:28:30Z

2

Do it as an inline TVF, which is much, much faster:

CREATE FUNCTION [console].[getCalculosRequisita]
(   

    @Disponivel BIGINT,
    @mediaDiaria float,
    @DiasStockArtigo INT, 
    @DiasAntes INT, 
    @SaidasPorMes float, 
    @QtdEncomendada2Meses BIGINT,
    @StockAtual BIGINT,
    @QtdRequisitada BIGINT,
    @caixaMinima INT
)
RETURNS TABLE -- WITH SCHEMABINDING  -- preferable, but then you can't change the underlying function
(
    DiasAteRotura INT,
    AcaoRequisita varchar(10),
    Aconselhada BIGINT
)
AS RETURN
(SELECT DiasAteRotura, AcaoRequisita, Aconselhada
FROM [cartridge].[getCalculosRequisitaTSQL]
(
    @Disponivel ,
    @mediaDiaria ,
    @DiasStockArtigo , 
    @DiasAntes , 
    @SaidasPorMes , 
    @QtdEncomendada2Meses ,
    @StockAtual ,
    @QtdRequisitada ,
    @caixaMinima
) AS t
);
GO

Obviously, if you do this then you cannot do any other inserts. In any case logging would be impossible, so I'm not sure what you were planning on doing.

You have not given the code for the underlying function. Perhaps that can be done as an iTVF also.

edited Jan 28, 2021 at 12:28

Thom A♦

97.7k12 gold badges67 silver badges102 bronze badges

answered Jan 28, 2021 at 12:06

Charlieface

78.7k8 gold badges35 silver badges77 bronze badges

Sign up to request clarification or add additional context in comments.

7 Comments

user6788933 Over a year ago

You understood and are 100% correct, but the goal of the wrapper is exactly to alow future code like the logging or a config check...

Charlieface Over a year ago

Logging is impossible as the only DML statements allowed are "INSERT, UPDATE, and DELETE statements modifying local table variables." Config check can be done inline with a bailout, if you give an example of what you'd like I can show how to do that. @Larnu I think I'm right about that?

Thom A Over a year ago

Functions can perform DML statements against table variables within them, yes, they would have to if they are a multi-inline table-value function. As Charlieface says though, @user6788933 , mlTVF's are known to be slow. iTVF's are significantly faster and using one will be a great benefit for performance. Functions can't perform logging, as they can't perform DML statements against table objects. If you want logging that should be within the called Stored procedure, or perhaps in a (well written) TRIGGER.

Thom A Over a year ago

I must admit, however, the function seems redundant, @user6788933 . Why have a function, that calls a function, with no further transformations in the SELECT, or even a further JOIN or WHERE. Just call [cartridge].[getCalculosRequisitaTSQL] in your outer query and just use the columns you need. There is literally no point in the [console].[getCalculosRequisita] function. It's like having a VIEW that SELECT all but a few columns from a table, with no WHERE, or expressions. There's is no benefit, and could easily cause performance issues.

Thom A Over a year ago

I bet [cartridge].[getCalculosRequisitaTSQL] is also a mlTVF, @user6788933 . If so an mlTVF calling an mlTVF is a sure fire recipe for a slow function.

|

user6788933 · Accepted Answer · 2021-01-28 20:18:24Z

I really appreciate the replies. They were insightfull and I will upvote them all. I just post some code here for the sharing. Happy to hear your thougts if you have suggestions.

What I really wanted was to run Python from a View, I had to go complety for another direction and I think performance just went down the drain.

It seems Python runs only from Stored Procedures... so had to go for a OPENROWSET (!?) to have it as a VIEW. Not pretty, example below:

DROP PROC IF EXISTS PythonExample;
GO
CREATE PROC PythonExample
AS
BEGIN

set nocount on
SET FMTONLY OFF

    DROP TABLE [dbo].[MyRows]
    CREATE TABLE [dbo].[MyRows](
        [RowNum] [int] NULL
    ) ON [PRIMARY]
    

    INSERT INTO [MyRows] 
        ([RowNum]) 
    VALUES 
        (1),
        (2)

    DECLARE @tbl1 TABLE 
    (
       COL1 INT,
       COL2 INT
    )

    INSERT INTO @tbl1

---ATTENTION Identing is important for Python code...
    EXEC sp_execute_external_script  @language =N'Python',
    @script=N'
import pandas as pd
df= MyInput
df["newCol"]= df["RowNum"]*2
MyOutput = df;
    ',
    @input_data_1_name = N'MyInput',
    @input_data_1 =N'SELECT [RowNum] FROM [MyRows]', --- it seems it cannot handle a temp table
    @output_data_1_name =N'MyOutput'
    --WITH RESULT SETS ((MyColName int, MyColName2 int));

    SELECT * FROM @tbl1
END;
GO

DROP VIEW IF EXISTS ViewExample;
GO
CREATE VIEW ViewExample
AS
    SELECT * FROM OPENROWSET('SQLNCLI', 'Server=localhost;Trusted_Connection=yes;', 'exec [dbo].[PythonExample]')
GO

SELECT * FROM ViewExample

Collectives™ on Stack Overflow

Wrapper TSQL Table-valued Function is slow

2 Answers 2

7 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

7 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related