Table Valued Function causing performance issue

Question

Table Valued Function causing performance issue

ermuditsirohi

SSC Veteran

Points: 251
More actions
April 10, 2018 at 3:20 am

#410394

Hi,
I have a table valued function which is causing serious performance issue when call it from my queries. Can somebody suggest a solution how can i optimize my query or function to achieve better performance. Function;
CREATE FUNCTION fn_get_booking_value
( @hotel_id UNIQUEIDENTIFIER )
RETURNS @booking_value TABLE
(
    hotel_id            UNIQUEIDENTIFIER NOT NULL
    , booking_value        DECIMAL(19,2)    NOT NULL
)
AS
BEGIN
    -- check if hotel active
    DECLARE @is_ative BIT = 0;
    SELECT @is_ative = is_active
    FROM    hotel AS H
    WHERE    hotel_id = @hotel_id
    IF @is_ative = 1
    BEGIN
            INSERT INTO @booking_value
            (
                hotel_id
                ,booking_value
            )
            SELECT    A.hotel_id
                , SUM(A.booking_value) AS booking_value
            FROM    booking AS A
            WHERE    A.hotel_id = @hotel_id
            GROUP BY A.hotel_id
    END
    RETURN;
END
Query
SELECT    city_id
    , H.hotel_id
    , bookings.booking_value
FROM    hotel AS H
    CROSS APPLY ufn_get_booking_value_by_hotel(H.hotel_id) AS bookings
WHERE    city_id = 200

Viewing 9 posts - 1 through 8 (of 8 total)

You must be logged in to reply to this topic. Login to reply

Eirikur Eiriksson SSC Guru Points: 182882 More actions · Answer 1

What you have here is a classical example of a multi statement table valued function, absolute horror when it comes to performance!
😎

The good news is that this function can easily be converted to an inline table valued function, much more efficient than the other.

CREATE FUNCTION dbo.ITVFN_GET_BOOKING_VALUE ( @HOTEL_ID UNIQUEIDENTIFIER ) RETURNS TABLE WITH SCHEMABINDING AS RETURN SELECT A.hotel_id , SUM(A.booking_value) AS booking_value FROM booking AS A WHERE A.hotel_id = @hotel_id AND A.is_active = 1 GROUP BY A.hotel_id;

ermuditsirohi SSC Veteran Points: 251 More actions · Answer 2

Eirikur Eiriksson - Tuesday, April 10, 2018 3:47 AM
What you have here is a classical example of a multi statement table valued function, absolute horror when it comes to performance!
😎
The good news is that this function can easily be converted to an inline table valued function, much more efficient than the other.
CREATE FUNCTION dbo.ITVFN_GET_BOOKING_VALUE ( @HOTEL_ID UNIQUEIDENTIFIER ) RETURNS TABLE WITH SCHEMABINDING AS RETURN SELECT A.hotel_id , SUM(A.booking_value) AS booking_value FROM booking AS A WHERE A.hotel_id = @hotel_id AND A.is_active = 1 GROUP BY A.hotel_id;

Hi Erikur,
The is_active field is not the part of booking table, it belongs to hotel table. But anyway we can join the hotel table to get this field .
Thanks for the solution it solved my purpose. Can you guide me to a blog or something about performance of multistatement table valued functoins.

Eirikur Eiriksson SSC Guru Points: 182882 More actions · Answer 3

There are quite few articles and blog posts on the subject, suggest you start with this one.
😎

Luis Cazares SSC Guru Points: 183698 More actions · Answer 4

One simple test that I keep available for demonstrating performance of scalar functions.
There's no data access or any real work being done in the functions, it's just the cost of the call. When adding more work, the problems will increase.
/* Author: Luis Cazares Description: This script demonstrates the performance hit on the options available for user defined functions. It's meant to discourage the use of Scalar and Multi-statement Valued Functions. In my tests Results: Scalar function is 6 times slower Multi-statement TVF is over 200 times slower. */ IF OBJECT_ID(N'[dbo].[ScalarFunction]') IS NOT NULL DROP FUNCTION [dbo].[ScalarFunction]; GO CREATE FUNCTION [ScalarFunction]( @Value int ) RETURNS INT AS BEGIN RETURN @Value; END

GO IF OBJECT_ID(N'[dbo].[MultiStatementTableFunction]') IS NOT NULL DROP FUNCTION [dbo].[MultiStatementTableFunction]; GO CREATE FUNCTION [MultiStatementTableFunction]( @Value int ) RETURNS @SomeTable TABLE (ID INT) AS BEGIN INSERT @SomeTable (ID) SELECT @Value; RETURN; END;

GO IF OBJECT_ID(N'[dbo].[InLineTableFunction]') IS NOT NULL DROP FUNCTION [dbo].[InLineTableFunction] GO CREATE FUNCTION [InLineTableFunction]( @Value int ) RETURNS TABLE AS RETURN SELECT @Value AS ID; GO

SELECT TOP(100000) ISNULL(CHECKSUM(NEWID()) % 100000, 0) myID INTO PerformanceTestTable FROM sys.all_columns, sys.all_columns b;

DECLARE @Dummy int, @TimeStamp datetime2 = SYSDATETIME();

SELECT @Dummy = myID FROM dbo.PerformanceTestTable; SELECT 'Dry run', DATEDIFF(MS, @TimeStamp, SYSDATETIME()); SET @TimeStamp = SYSDATETIME();

SELECT @Dummy = x.ID FROM dbo.PerformanceTestTable CROSS APPLY dbo.MultiStatementTableFunction(myID) x SELECT 'MultiStatementTableFunction', DATEDIFF(MS, @TimeStamp, SYSDATETIME()); SET @TimeStamp = SYSDATETIME();

SELECT @Dummy = x.ID FROM dbo.PerformanceTestTable CROSS APPLY dbo.InLineTableFunction(myID) x SELECT 'InLineTableFunction', DATEDIFF(MS, @TimeStamp, SYSDATETIME()); SET @TimeStamp = SYSDATETIME();

SELECT @Dummy = dbo.[ScalarFunction](myID) FROM dbo.PerformanceTestTable SELECT 'ScalarFunction', DATEDIFF(MS, @TimeStamp, SYSDATETIME()); SET @TimeStamp = SYSDATETIME();

GO DROP TABLE dbo.PerformanceTestTable DROP FUNCTION dbo.MultiStatementTableFunction, dbo.InLineTableFunction, dbo.ScalarFunction

Luis C.
General Disclaimer:
Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

How to post data/code on a forum to get the best help: Option 1 / Option 2

WayneS SSC Guru Points: 95409 More actions · Answer 5

ermuditsirohi - Tuesday, April 10, 2018 5:07 AM
Eirikur Eiriksson - Tuesday, April 10, 2018 3:47 AM
What you have here is a classical example of a multi statement table valued function, absolute horror when it comes to performance!
😎
The good news is that this function can easily be converted to an inline table valued function, much more efficient than the other.
CREATE FUNCTION dbo.ITVFN_GET_BOOKING_VALUE ( @HOTEL_ID UNIQUEIDENTIFIER ) RETURNS TABLE WITH SCHEMABINDING AS RETURN SELECT A.hotel_id , SUM(A.booking_value) AS booking_value FROM booking AS A WHERE A.hotel_id = @hotel_id AND A.is_active = 1 GROUP BY A.hotel_id;
Hi Erikur,
The is_active field is not the part of booking table, it belongs to hotel table. But anyway we can join the hotel table to get this field .
Thanks for the solution it solved my purpose. Can you guide me to a blog or something about performance of multistatement table valued functoins.

Here's a post[/url] that I wrote (and it links to an even earlier post[/url]). I have an update in the works for handling new 2017 features (no ETA yet).

Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes

If you can't explain to another person how the code that you're copying from the internet works, then DON'T USE IT on a production system! After all, you will be the one supporting it!

Links:
For better assistance in answering your questions
Performance Problems
Common date/time routines
Understanding and Using APPLY Part 1 & Part 2

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 6

ermuditsirohi - Tuesday, April 10, 2018 3:20 AM
Hi,
I have a table valued function which is causing serious performance issue when call it from my queries. Can somebody suggest a solution how can i optimize my query or function to achieve better performance. Function;
CREATE FUNCTION fn_get_booking_value
( @hotel_id UNIQUEIDENTIFIER )
RETURNS @booking_value TABLE
(
    hotel_id            UNIQUEIDENTIFIER NOT NULL
    , booking_value        DECIMAL(19,2)    NOT NULL
)
AS
BEGIN
    -- check if hotel active
    DECLARE @is_ative BIT = 0;
    SELECT @is_ative = is_active
    FROM    hotel AS H
    WHERE    hotel_id = @hotel_id
    IF @is_ative = 1
    BEGIN
            INSERT INTO @booking_value
            (
                hotel_id
                ,booking_value
            )
            SELECT    A.hotel_id
                , SUM(A.booking_value) AS booking_value
            FROM    booking AS A
            WHERE    A.hotel_id = @hotel_id
            GROUP BY A.hotel_id
    END
    RETURN;
END
Query
SELECT    city_id
    , H.hotel_id
    , bookings.booking_value
FROM    hotel AS H
    CROSS APPLY ufn_get_booking_value_by_hotel(H.hotel_id) AS bookings
WHERE    city_id = 200

As Eirikur said, this can be rewritten as an inline table valued function:
CREATE FUNCTION [dbo].[fn_get_booking_value]( @hotel_id UNIQUEIDENTIFIER ) RETURNS TABLE WITH SCHEMABINDING AS RETURN SELECT .[hotel_id] , SUM(.[booking_value]) AS [booking_value] FROM [dbo].[booking] AS WHERE .[hotel_id] = @hotel_id AND EXISTS( SELECT 1 FROM [dbo].[hotel] AS [h] WHERE [h].[hotel_id] = .[hotel_id] AND [h].[is_active] = 1) GROUP BY .[hotel_id];

Chris Harshman SSC-Forever Points: 42192 More actions · Answer 7

It looks like from the OP that Hotel is in the query already, so I'm not sure it makes sense to hit this table again inside the TVF.
SELECT city_id , H.hotel_id , CASE WHEN H.is_active = 1 THEN bookings.booking_value END AS booking_value FROM hotel AS H CROSS APPLY ufn_get_booking_value_by_hotel(H.hotel_id) AS bookings WHERE city_id = 200

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 8

Chris Harshman - Tuesday, April 10, 2018 12:21 PM
It looks like from the OP that Hotel is in the query already, so I'm not sure it makes sense to hit this table again inside the TVF.
SELECT city_id , H.hotel_id , CASE WHEN H.is_active = 1 THEN bookings.booking_value END AS booking_value FROM hotel AS H CROSS APPLY ufn_get_booking_value_by_hotel(H.hotel_id) AS bookings WHERE city_id = 200

True, but the function may be used in other queries with out hotel in the query.