November 1, 2013 at 11:44 am
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.:-D). 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)
[/url]
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)[/url]. As Sean was saying and demonstrated...
-- Itzik Ben-Gan 2001
November 1, 2013 at 12:19 pm
Alan.B (11/1/2013)
Looks like we have been reading the same articles 😉
More like he wrote the article you read 😛
November 7, 2013 at 9:40 am
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
November 7, 2013 at 9:58 am
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.
-- Itzik Ben-Gan 2001
November 7, 2013 at 11:41 am
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
(SELECTx = @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.
-- Itzik Ben-Gan 2001
November 7, 2013 at 6:25 pm
Almost there... This was the first draft and, though it is processing all your variables, I made a mistake(s) somewhere in my formulas; I had very limited time. The reason I posted this was to demonstrate that, YES, you can turn your function into an inline Table Valued Function. I will post an updated & corrected version when I get some more time.
CREATE FUNCTION dbo.udf_ResidualValue_iSVF
(@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 TABLE
AS
RETURN
WITH
dates(DateX,DateY,[sign]) AS
(
SELECTDateX =CASE
WHEN (@GARMENTISSUEDATE < @RVDate) THEN @GARMENTISSUEDATE
ELSE @RVDate
END,
DateY =CASE
WHEN (@GARMENTISSUEDATE < @RVDate) THEN @RVDate
ELSE @GARMENTISSUEDATE
END,
[sign]= CASE
WHEN (@GARMENTISSUEDATE < @RVDate) THEN 1 ELSE -1
END),
age_ratio(GarmentAgeInMonth,RVRatio) AS
(
SELECTGarmentAgeInMonth = [Sign] * (
SELECT
CASE
WHEN DATEPART(DAY,DateX) = DATEPART(DAY,DATEADD(day,1,DateY))
THEN DATEDIFF(MONTH,DateX, DATEADD(day,1, DateY))
WHEN DATEPART(DAY,DateX) = DATEPART(DAY,DATEADD(day,1,DateY))
THEN DATEDIFF(MONTH, DateX, DATEADD(day,1, DateY))
WHEN DATEPART(DAY, DateX) >= DATEPART(day,DATEADD(day,1,DateY))
THEN DATEDIFF(MONTH, DateX, DATEADD(day, 1, DateY)) - 1
ELSE DATEDIFF(MONTH, DateX, DATEADD(day, 1, DateY))
END),
RVRatio = ISNULL(@TERMINATIONDISCPCT,1.0)*((100.00 - @TERMINATIONDISCPCT) / 100.00)
FROM dates
)
SELECT return_value =
CAST((CASE
WHEN @LIFETIME > 0 AND GarmentAgeInMonth <= @LIFETIME
THEN
CAST( @StartPrice -
(CAST(((@StartPrice - @EndPrice) / @LIFETIME) AS DECIMAL(18, 3)) *
GarmentAgeInMonth ) AS DECIMAL(28, 2)) * RVRatio
WHEN @STS_CONTINUOUSCHARGING = 1
AND @LIFETIME > 0
AND GarmentAgeInMonth > @LIFETIME
THEN @ENDPRICE * RVRatio
ELSE 0
END *
CASE
WHEN( @RVDate < @GARMENTISSUEDATE )
OR(@SETQTY > 0 AND @GarmentNumber > @SETQTY)
OR(LEN(LTRIM(@RVMATRIXCODE)) = 0)
OR(@TERMINATIONDISCPCT = 100)
THEN 0 ELSE 1
END) AS DECIMAL(9,2))
FROM age_ratio;
I don't write scalar valued functions any more. My experience has been that the iTVF version is always equal in speed or faster; especially when there are many 4+ CPUs involved and the only thing preventing parallelism is a SVF (as Louis made reference to). Have a look at this, see the tricks I used to turn it into an iTVF and, in the future, write your functions as iTVFs.
-- Itzik Ben-Gan 2001
November 8, 2013 at 1:31 am
Alan,
I really appreciate your efforts but I would not want to waste your time.
I can see that it is probably feasible to turn this into the right form but I have strong reservations as whether it is desirable.
I feel a big challenge in writing any code is to try and make it both to understand and maintain.
As far as possible, I am trying to split my code in little bits easy to grasp (with lots of comments which I had removed in my sample for space reason).
In this case, going through the TSQL inline table function approach means grouping all the code into one unique (and therefore difficult to test) big blob of code, all of which to try and work around serious limitations in the (scalar) UDF implementation.
I am away from my desk for the next few days so I don't have access to my code but I will try to show you the equivalent C# code which IMHO seems way easier to understand/debug.
On the other hand, this would mean introducing a technology (CLR function) that my client is not (yet) familiar with.
When I get a minute, I will do another performance test (regardless of the exact values returned) between the CLR and inline table functions just to see if that tips the scale one way or another.
Once again, a big thank you for your efforts but please hold on fire until I do these speed tests.
Kind Regards
Eric 😀
PS: I am very interested in the Inline table function but I might use it for simpler requests...
Viewing 7 posts - 16 through 21 (of 21 total)
You must be logged in to reply to this topic. Login to reply