Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase ««123»»

udf very slow? Expand / Collapse
Author
Message
Posted Thursday, October 31, 2013 4:19 PM


SSCarpal Tunnel

SSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal Tunnel

Group: General Forum Members
Last Login: Today @ 3:35 PM
Points: 4,046, Visits: 9,202
The lack of parallelism is just a part of the reasons why udfs slow down the code performance. I'll be glad to help (and I'm sure others will be as well) when we know what is intended with the function.


Luis C.
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?

Forum Etiquette: How to post data/code on a forum to get the best help
Post #1510449
Posted Thursday, October 31, 2013 4:38 PM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Yesterday @ 7:27 AM
Points: 35,769, Visits: 32,437
Sean Pearce (10/31/2013)
It is much faster to apply a table function.

CREATE TABLE Test1 (ID INT);
GO

INSERT INTO Test1
SELECT TOP 1000000 ROW_NUMBER() OVER (ORDER BY a.object_id)
FROM sys.all_columns a
CROSS JOIN sys.all_columns b
GO

CREATE FUNCTION UDF_INLINE (@Input INT)
RETURNS INT
AS
BEGIN
DECLARE @I INT;
SET @I = @Input * 0.14;
RETURN @I;
END;
GO

CREATE FUNCTION UDF_APPLY (@Input INT)
RETURNS TABLE
AS
RETURN (SELECT @Input * 0.14 AS Result);
GO

SET STATISTICS IO ON;
SET STATISTICS TIME ON;

-- Return the column with no function
SELECT
ID
INTO
#test1
FROM
Test1;

/*
Table 'Test1'. Scan count 1, logical reads 3345
CPU time = 437 ms, elapsed time = 533 ms.
*/

-- Return the inline function
SELECT
dbo.UDF_INLINE(ID) AS RowName
INTO
#test2
FROM
Test1;

/*
Table '#test2'. Scan count 0, logical reads 1001607
Table 'Test1'. Scan count 1, logical reads 3345
CPU time = 10389 ms, elapsed time = 13965 ms.
*/

-- Apply the function
SELECT
b.Result
INTO
#test3
FROM
Test1 t
CROSS APPLY
dbo.UDF_APPLY(t.ID) AS b;

/*
Table 'Test1'. Scan count 1, logical reads 3345
CPU time = 577 ms, elapsed time = 578 ms.
*/



+1. I don't know what others call the type of function you wrote for the "Apply" function but I call them "iSF" or "Inline Scalar Function". Of course, they're really just an Inline Table Valued Function (iTVF) that returns a single element.


--Jeff Moden
"RBAR is pronounced "ree-bar" and is a "Modenism" for "Row-By-Agonizing-Row".

First step towards the paradigm shift of writing Set Based code:
Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #1510452
Posted Thursday, October 31, 2013 4:43 PM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Yesterday @ 7:27 AM
Points: 35,769, Visits: 32,437
Eric Mamet (10/31/2013)
I know but I was hoping it would be fast...

Just talked to my customer and I'll try using CLR!
Yipee!!!


Heh... hate to throw a wet blanket on your fire but you don't need to resort to an SQLCLR function for something so simple (although if you're comfortable with that, then fire away! It can be a great solution when done properly.). Please see the example that Sean Pierce provided in his post above and please see the following article that demonstrates the problem and the fix (the "iSF").
How to Make Scalar UDFs Run Faster (SQL Spackle)


--Jeff Moden
"RBAR is pronounced "ree-bar" and is a "Modenism" for "Row-By-Agonizing-Row".

First step towards the paradigm shift of writing Set Based code:
Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #1510453
Posted Friday, November 1, 2013 12:41 AM


SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: 2 days ago @ 1:37 AM
Points: 997, Visits: 3,089
Jeff Moden (10/31/2013)

+1. I don't know what others call the type of function you wrote for the "Apply" function but I call them "iSF" or "Inline Scalar Function". Of course, they're really just an Inline Table Valued Function (iTVF) that returns a single element.

I was really struggling with terminology when I wrote that




The SQL Guy @ blogspot

@SeanPearceSQL

About Me
Post #1510508
Posted Friday, November 1, 2013 4:28 AM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Tuesday, December 2, 2014 3:51 PM
Points: 1,191, Visits: 794
Indeed this is what I have done and the result is very impressive.

I don't think I can detect any load due to the SQLCLR function.
I have even been able to convince the DBA in charge to CLR enable the database
Perfect!
Post #1510544
Posted Friday, November 1, 2013 11:44 AM


SSChasing Mays

SSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing Mays

Group: General Forum Members
Last Login: Yesterday @ 3:58 PM
Points: 646, Visits: 2,994
Jeff Moden (10/31/2013)
Eric Mamet (10/31/2013)
I know but I was hoping it would be fast...

Just talked to my customer and I'll try using CLR!
Yipee!!!


Heh... hate to throw a wet blanket on your fire but you don't need to resort to an SQLCLR function for something so simple (although if you're comfortable with that, then fire away! It can be a great solution when done properly.). Please see the example that Sean Pierce provided in his post above and please see the following article that demonstrates the problem and the fix (the "iSF").
How to Make Scalar UDFs Run Faster (SQL Spackle)


Looks like we have been reading the same articles

Alan.B (10/31/2013)


...Take a look at this article How to Make Scalar UDFs Run Faster (SQL Spackle). As Sean was saying and demonstrated...



-- Alan Burstein



Read this article for best practices on asking questions.
Need to split a string? Try this (Jeff Moden)
Need a pattern-based string spitter? Try this (Dwain Camps)

"I can't stress enough the importance of switching from a 'sequential files' mindset to 'set-based' thinking. After you make the switch, you can spend your time tuning and optimizing your queries instead of maintaining lengthy, poor-performing code. " -- Itzek Ben-Gan 2001

My blog
Post #1510733
Posted Friday, November 1, 2013 12:19 PM


SSCarpal Tunnel

SSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal Tunnel

Group: General Forum Members
Last Login: Today @ 3:35 PM
Points: 4,046, Visits: 9,202
Alan.B (11/1/2013)


Looks like we have been reading the same articles


More like he wrote the article you read



Luis C.
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?

Forum Etiquette: How to post data/code on a forum to get the best help
Post #1510756
Posted Thursday, November 7, 2013 9:40 AM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Tuesday, December 2, 2014 3:51 PM
Points: 1,191, Visits: 794
Hi everyone, sorry for being so slow but I just did not have time to go through the article before...

Yes, it is very interesting and intriguing but it looks like I won't be able to use that due to the complexity of my UDF.

CREATE FUNCTION dbo.udf_ResidualValue(@RVDate                 DATETIME
, @GARMENTSTATUS INT
, @RVMATRIXCODE VARCHAR(10)
, @LIFETIME INT
, @GARMENTISSUEDATE DATETIME
, @STARTPRICE NUMERIC(28,12)
, @ENDPRICE NUMERIC(28,12)
, @TERMINATIONDISCPCT NUMERIC(28,12)
, @STS_CONTINUOUSCHARGING INT
, @WEARERSTATUS INT
, @SETQTY INT
, @GarmentNumber INT
)
RETURNS DECIMAL(9,2)
AS
BEGIN

IF ( @RVDate < @GARMENTISSUEDATE )
RETURN 0.0;

IF @SETQTY > 0
AND @GarmentNumber > @SETQTY
RETURN 0.0;

IF LEN(LTRIM(@RVMATRIXCODE)) = 0
RETURN 0.0;

IF (@TERMINATIONDISCPCT = 100)
RETURN 0.0;

DECLARE @GarmentAgeInMonth INT

DECLARE @DateX DATE
DECLARE @DateY DATE
DECLARE @Sign INT

IF(@GARMENTISSUEDATE < @RVDate)
BEGIN
SET @DateX = @GARMENTISSUEDATE
SET @DateY = @RVDate
SET @Sign = 1
END
ELSE
BEGIN
SET @DateX = @RVDate
SET @DateY = @GARMENTISSUEDATE
SET @Sign = -1
END

SELECT @DateY = DATEADD(day, 1, @DateY);

SET @GarmentAgeInMonth = @Sign * (
SELECT
CASE
WHEN DATEPART(DAY, @DateX) = DATEPART(DAY, @DateY) THEN DATEDIFF(MONTH, @DateX, @DateY)
WHEN DATEPART(DAY, @DateX) = DATEPART(DAY, @DateY) THEN DATEDIFF(MONTH, @DateX, @DateY)
WHEN DATEPART(DAY, @DateX) >= DATEPART(DAY, @DateY) THEN DATEDIFF(MONTH, @DateX, @DateY) - 1
ELSE DATEDIFF(MONTH, @DateX, @DateY)
END
)

DECLARE @RVRatio FLOAT

IF @TERMINATIONDISCPCT IS NULL
SET @RvRatio = 1.0;
ELSE
SET @RvRatio = ( 100.00 - @TERMINATIONDISCPCT ) / 100.00;

IF @LIFETIME > 0 AND @GarmentAgeInMonth <= @LIFETIME
BEGIN
-- Calculate the monthly depreciation to 3 digits to imitate the exact Axapta calculation
DECLARE @MonthlyDepreciation DECIMAL(18, 3)
SELECT @MonthlyDepreciation = (@StartPrice - @EndPrice) / @LIFETIME;

RETURN CAST( @StartPrice - (@MonthlyDepreciation * @GarmentAgeInMonth ) AS DECIMAL(28, 2)) * @RVRatio;
END

IF @STS_CONTINUOUSCHARGING = 1
AND @LIFETIME > 0
AND @GarmentAgeInMonth > @LIFETIME
RETURN @ENDPRICE * @RVRatio;

RETURN 0.0;
END

In fact, there are even business type arguments around this function so I wrote another one returning the reason for the value rather than the value itself so that I can explain my results as well.

Having said that, I have learnt some very interesting stuff about UDFs.


Thanks


Eric

Post #1512345
Posted Thursday, November 7, 2013 9:58 AM


SSChasing Mays

SSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing Mays

Group: General Forum Members
Last Login: Yesterday @ 3:58 PM
Points: 646, Visits: 2,994
I am pretty sure that you can turn that into an inline table valued function. It isn't something I can do quickly but I will take a shot at this later today unless someone beats me to it.

-- Alan Burstein



Read this article for best practices on asking questions.
Need to split a string? Try this (Jeff Moden)
Need a pattern-based string spitter? Try this (Dwain Camps)

"I can't stress enough the importance of switching from a 'sequential files' mindset to 'set-based' thinking. After you make the switch, you can spend your time tuning and optimizing your queries instead of maintaining lengthy, poor-performing code. " -- Itzek Ben-Gan 2001

My blog
Post #1512358
Posted Thursday, November 7, 2013 11:41 AM


SSChasing Mays

SSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing Mays

Group: General Forum Members
Last Login: Yesterday @ 3:58 PM
Points: 646, Visits: 2,994
Eric Mamet (11/7/2013)
Hi everyone, sorry for being so slow but I just did not have time to go through the article before...

Yes, it is very interesting and intriguing but it looks like I won't be able to use that due to the complexity of my UDF.

CREATE FUNCTION dbo.udf_ResidualValue(@RVDate                 DATETIME
, @GARMENTSTATUS INT
, @RVMATRIXCODE VARCHAR(10)
, @LIFETIME INT
, @GARMENTISSUEDATE DATETIME
, @STARTPRICE NUMERIC(28,12)
, @ENDPRICE NUMERIC(28,12)
, @TERMINATIONDISCPCT NUMERIC(28,12)
, @STS_CONTINUOUSCHARGING INT
, @WEARERSTATUS INT
, @SETQTY INT
, @GarmentNumber INT
)
RETURNS DECIMAL(9,2)
AS
BEGIN

IF ( @RVDate < @GARMENTISSUEDATE )
RETURN 0.0;

IF @SETQTY > 0
AND @GarmentNumber > @SETQTY
RETURN 0.0;

IF LEN(LTRIM(@RVMATRIXCODE)) = 0
RETURN 0.0;

IF (@TERMINATIONDISCPCT = 100)
RETURN 0.0;

DECLARE @GarmentAgeInMonth INT

DECLARE @DateX DATE
DECLARE @DateY DATE
DECLARE @Sign INT

IF(@GARMENTISSUEDATE < @RVDate)
BEGIN
SET @DateX = @GARMENTISSUEDATE
SET @DateY = @RVDate
SET @Sign = 1
END
ELSE
BEGIN
SET @DateX = @RVDate
SET @DateY = @GARMENTISSUEDATE
SET @Sign = -1
END

SELECT @DateY = DATEADD(day, 1, @DateY);

SET @GarmentAgeInMonth = @Sign * (
SELECT
CASE
WHEN DATEPART(DAY, @DateX) = DATEPART(DAY, @DateY) THEN DATEDIFF(MONTH, @DateX, @DateY)
WHEN DATEPART(DAY, @DateX) = DATEPART(DAY, @DateY) THEN DATEDIFF(MONTH, @DateX, @DateY)
WHEN DATEPART(DAY, @DateX) >= DATEPART(DAY, @DateY) THEN DATEDIFF(MONTH, @DateX, @DateY) - 1
ELSE DATEDIFF(MONTH, @DateX, @DateY)
END
)

DECLARE @RVRatio FLOAT

IF @TERMINATIONDISCPCT IS NULL
SET @RvRatio = 1.0;
ELSE
SET @RvRatio = ( 100.00 - @TERMINATIONDISCPCT ) / 100.00;

IF @LIFETIME > 0 AND @GarmentAgeInMonth <= @LIFETIME
BEGIN
-- Calculate the monthly depreciation to 3 digits to imitate the exact Axapta calculation
DECLARE @MonthlyDepreciation DECIMAL(18, 3)
SELECT @MonthlyDepreciation = (@StartPrice - @EndPrice) / @LIFETIME;

RETURN CAST( @StartPrice - (@MonthlyDepreciation * @GarmentAgeInMonth ) AS DECIMAL(28, 2)) * @RVRatio;
END

IF @STS_CONTINUOUSCHARGING = 1
AND @LIFETIME > 0
AND @GarmentAgeInMonth > @LIFETIME
RETURN @ENDPRICE * @RVRatio;

RETURN 0.0;
END

In fact, there are even business type arguments around this function so I wrote another one returning the reason for the value rather than the value itself so that I can explain my results as well.

Having said that, I have learnt some very interesting stuff about UDFs.


Thanks


Eric



I am wont have time today but will take a stab at this tonight. In the meantime, to demonstrate how you would convert yours into an iTVF, I put this example together. It demonstrates the techniques that you would use. Note my comments:

-- Scalar Valued Function
CREATE FUNCTION dbo.SVF(@x int, @y int, @d date)
RETURNS int AS
BEGIN
DECLARE @return_value int;

IF @d>getdate()
RETURN 0;

IF @x=0
RETURN 0;

SET @x=@x+(2*@y)
SET @y=@y*2;

IF @x>@y
BEGIN
SET @return_value=@x*3
END
ELSE
BEGIN
SET @return_value=@y
END
RETURN @return_value
END
GO

-- the new inline table valued function version (aka iSVF)
CREATE FUNCTION dbo.iSVF(@x int, @y int, @d date)
RETURNS TABLE
AS
RETURN
WITH aggreagations AS
( SELECT x = @x+(2*@y),
y = @y*2,
d = @d)
SELECT return_value=
CASE WHEN x>y THEN x*3 ELSE @y END *
CASE WHEN @d>getdate() THEN 0 ELSE 1 END *
CASE WHEN @x=0 THEN 0 ELSE 1 END
FROM aggreagations;
GO

--test the functions and look at the query plans
SELECT * FROM dbo.iSVF(1,2,'1/1/2001')
SELECT dbo.SVF(1,2,'1/1/2001')


--using iSVF
CREATE TABLE #sample
(s_id int primary key,x int not null, y int not null, d date not null);

--sample data
INSERT INTO #sample
SELECT TOP 1000
ROW_NUMBER() OVER (ORDER BY (SELECT null)),
1.0+floor(10*RAND(convert(varbinary, newid())))-1,
1.0+floor(14*RAND(convert(varbinary, newid()))),
getdate()-4000+(1.0+floor(5000*RAND(convert(varbinary, newid()))))
FROM master..spt_values;

-- old way
SELECT s_id, x,y,d,dbo.SVF(s.x,s.y,s.d) AS val
FROM #sample s

--new way
SELECT s_id, x,y,d,return_value
FROM #sample s
CROSS APPLY dbo.iSVF(s.x,s.y,s.d)



Edit: added s_id to select statements.


-- Alan Burstein



Read this article for best practices on asking questions.
Need to split a string? Try this (Jeff Moden)
Need a pattern-based string spitter? Try this (Dwain Camps)

"I can't stress enough the importance of switching from a 'sequential files' mindset to 'set-based' thinking. After you make the switch, you can spend your time tuning and optimizing your queries instead of maintaining lengthy, poor-performing code. " -- Itzek Ben-Gan 2001

My blog
Post #1512392
« Prev Topic | Next Topic »

Add to briefcase ««123»»

Permissions Expand / Collapse