Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

I have several times converted scalar functions into inline tvf. Usually this is the basic design I used in inline tvf:

CREATE FUNCTION TVF(@ID INT)
RETURNS TABLE
AS
RETURN(
    WITH BASE AS(
        SELECT @ID AS ID -- the collections of parameter
    )
    SELECT TOP 1 T1.ID -- TOP 1 is not needed if always 1 row returned
    FROM BASE
        LEFT OUTER JOIN T1(NOLOCK) -- left join to ensure 1 row returned
        ON T1.ID = BASE.ID
)

This is, used to replace the following function:

CREATE FUNCTION SCALAR(@ID INT)
RETURNS INT
AS
BEGIN 
    DECLARE @ID_RET INT;

    SELECT @ID_RET = T1.ID
    FROM T1(NOLOCK)
    WHERE T1.ID = @ID
    OPTION(MAXRECURSION 0)

    RETURN @ID_RET -- 1 row will be ensured to be returned
END

Considering the functionality, this works very well. The scalar can be replaced with TVF without any data changed. However, due to usage of LEFT OUTER JOIN at function level, sometimes I encountered with high read/write and CPU, almost 2 times than scalar function (in SQL Profiler). The duration, however is improving incredibly for large datasets (100k+).

My question is, is this the correct approach to develop one row result TVF? Is high read and CPU is acceptable in exchange of decreased duration? Please note I'm an inexperienced dba and mainly an app programmer, so I know very little about database performance tune up.

I'm not sure about the performance usage of OUTER APPLY compared with this approach.

This is the syntax I used for example. However, I cannot reproduce the high read and high CPU here.

-- TABLE
CREATE TABLE T1(
    ID INT NOT NULL PRIMARY KEY
);
WITH CT1 AS(
    SELECT 1 AS ID
    UNION ALL
    SELECT ID + 1 AS ID
    FROM CT1
    WHERE ID < 1000
)
INSERT INTO T1
SELECT CT1.ID
FROM CT1
OPTION ( MAXRECURSION 0)
GO

-- FUNCTIONS
CREATE FUNCTION TVF(@ID INT)
RETURNS TABLE
AS
RETURN(
    WITH BASE AS(
        SELECT @ID AS ID
    )
    SELECT T1.ID
    FROM BASE
        LEFT OUTER JOIN T1(NOLOCK)
        ON T1.ID = BASE.ID
)
GO
CREATE FUNCTION SCALAR(@ID INT)
RETURNS INT
AS
BEGIN 
    DECLARE @ID_RET INT;

    SELECT @ID_RET = T1.ID
    FROM T1(NOLOCK)
    WHERE T1.ID = @ID
    OPTION(MAXRECURSION 0)

    RETURN @ID_RET
END
GO

-- TEST
;WITH T1 AS(
        SELECT 1 AS ID
        UNION ALL
        SELECT ID + 1 AS ID
        FROM T1
        WHERE ID < 1000
    )
SELECT FN.*
FROM T1
CROSS APPLY DBO.TVF(T1.ID) FN
OPTION(MAXRECURSION 0)

;WITH T1 AS(
        SELECT 1 AS ID
        UNION ALL
        SELECT ID + 1 AS ID
        FROM T1
        WHERE ID < 1000
    )
SELECT DBO.SCALAR(T1.ID) AS ID
FROM T1
OPTION(MAXRECURSION 0)

DROP FUNCTION TVF
DROP FUNCTION SCALAR
DROP TABLE T1
share|improve this question
    
I'm not sure if we can tell you whether CPU and I/O are more important to you than duration. It depends on where your constraints and bottlenecks currently are. I also have had conversations with people who narrowly focus on logical reads as the only true measure of performance (personally, I know that end users don't care how costly it was to get their data, as long as it was fast, so I focus on duration first). –  Aaron Bertrand Aug 1 '13 at 13:00
1  
Also I hope that skeleton code wasn't very representative - TOP is meaningless and unpredictable without ORDER BY. –  Aaron Bertrand Aug 1 '13 at 13:01
2  
This doesn't seem like an scenario that is going to benefit a great deal from a inline TVF (2 tables with a 1-1 relationship). Have you tried to join directly onto your table (w/o a TVF)? I understand this is an example and may not reflect your actual problem. My concern is the methods you are using might be limiting the optimizer's decision making capabilities, namely the cross apply. The cross apply is probably going to force a nested loop join, which can affect performance (increase IO), where as a direct join may have yielded a better plan. –  Adam Haines Aug 1 '13 at 14:26
    
@AaronBertrand yes, I just representing TOP as example. In real code it will has order by. The main concern is will it has side effect of having high read? I personally too prefer to has low duration. –  Fendy Aug 2 '13 at 1:23
    
@AdamHaines the main purpose is to logically replace scalar functions, while encapsulating some logic. Direct join / views don't allowed me to use input parameters and in most scenario I need to use parameters as a filter for it. Surely most of time I can replace using temptable / direct join but it will scatter the logic everywhere. –  Fendy Aug 2 '13 at 1:27
show 2 more comments

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Browse other questions tagged or ask your own question.