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


Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Today @ 5:59 AM
Points: 3,660, Visits: 7,986
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 @ 9:16 PM
Points: 37,102, Visits: 31,655
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 @ 9:16 PM
Points: 37,102, Visits: 31,655
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: Today @ 12:46 AM
Points: 906, Visits: 2,866
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, September 16, 2014 3:05 PM
Points: 1,191, Visits: 791
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


Mr or Mrs. 500

Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500

Group: General Forum Members
Last Login: Yesterday @ 8:06 PM
Points: 581, Visits: 2,711
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)
My blog
Post #1510733
Posted Friday, November 1, 2013 12:19 PM


Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Today @ 5:59 AM
Points: 3,660, Visits: 7,986
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, September 16, 2014 3:05 PM
Points: 1,191, Visits: 791
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


Mr or Mrs. 500

Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500

Group: General Forum Members
Last Login: Yesterday @ 8:06 PM
Points: 581, Visits: 2,711
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)
My blog
Post #1512358
Posted Thursday, November 7, 2013 11:41 AM


Mr or Mrs. 500

Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500

Group: General Forum Members
Last Login: Yesterday @ 8:06 PM
Points: 581, Visits: 2,711
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)
My blog
Post #1512392
« Prev Topic | Next Topic »

Add to briefcase ««123»»

Permissions Expand / Collapse