April 10, 2018 at 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
April 10, 2018 at 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;
April 10, 2018 at 5:07 am
Eirikur Eiriksson - Tuesday, April 10, 2018 3:47 AMWhat 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.
April 10, 2018 at 7:20 am
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
April 10, 2018 at 8:04 am
ermuditsirohi - Tuesday, April 10, 2018 5:07 AMEirikur Eiriksson - Tuesday, April 10, 2018 3:47 AMWhat 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
April 10, 2018 at 8:42 am
ermuditsirohi - Tuesday, April 10, 2018 3:20 AMHi,
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_idIF @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
ENDRETURN;
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];
April 10, 2018 at 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
April 10, 2018 at 1:42 pm
Chris Harshman - Tuesday, April 10, 2018 12:21 PMIt 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.
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply