Builtin alternatives to UDF?

I'm using some simple scalar UDFs in expressions that update sets of
around 500,000 rows, but have some worries about possible performance
hits. Question is, are there relativly simple ways of accomplishing the
same inline using the builtin functions?
create function dbo.f_MaxOf(@.x1 float, @.x2 float) returns float
AS
begin
return(case when @.x1 is null then @.x2
when @.x2 is null then @.x1
when @.x1 < @.x2 then @.x2
else @.x1
end)
end
go
----
--
create function dbo.f_IfZero(@.x float, @.minValue float) returns float
AS
begin
return(case when ABS(@.x) < @.minValue then @.minValue
else @.x
end)
end
(simple) Example usage:
UPDATE T set colA = dbo.f_MaxOf(colB, colE) / dbo.f_IfZero(colC + colD,
0.001)
The "real life" code is somewhat more complex ... :-)
An obvious alternative to the 'f_IfZero' function would be something
like
(case when ABS(colC + colD) < 0.001 then 0.001 else colC + colD
end)
but this quickly gets ugly when the expression being evaluated is more
complex, and the update has multiple occurences of the same function
that need to be replaced ...
Ideas anyone?
/Erikhave you ever heard of COALESCE function? It might simplify some of you
logic. Lookup in SQL Books on Line. Also, it seems that all of what you are
doing can be done as one update with no functions.
<eox_conceptos@.despammed.com> wrote in message
news:1144239792.905239.41070@.i40g2000cwc.googlegroups.com...
> I'm using some simple scalar UDFs in expressions that update sets of
> around 500,000 rows, but have some worries about possible performance
> hits. Question is, are there relativly simple ways of accomplishing the
> same inline using the builtin functions?
> create function dbo.f_MaxOf(@.x1 float, @.x2 float) returns float
> AS
> begin
> return(case when @.x1 is null then @.x2
> when @.x2 is null then @.x1
> when @.x1 < @.x2 then @.x2
> else @.x1
> end)
> end
> go
> ----
--
> create function dbo.f_IfZero(@.x float, @.minValue float) returns float
> AS
> begin
> return(case when ABS(@.x) < @.minValue then @.minValue
> else @.x
> end)
> end
> (simple) Example usage:
> UPDATE T set colA = dbo.f_MaxOf(colB, colE) / dbo.f_IfZero(colC + colD,
> 0.001)
> The "real life" code is somewhat more complex ... :-)
> An obvious alternative to the 'f_IfZero' function would be something
> like
> (case when ABS(colC + colD) < 0.001 then 0.001 else colC + colD
> end)
> but this quickly gets ugly when the expression being evaluated is more
> complex, and the update has multiple occurences of the same function
> that need to be replaced ...
> Ideas anyone?
> /Erik
>|||Farmer wrote:
> have you ever heard of COALESCE function? It might simplify some of you
> logic. Lookup in SQL Books on Line. Also, it seems that all of what you ar
e
> doing can be done as one update with no functions.
>
I'm familiar with coalesce. Of course, the example update could have
been written as:
UPDATE T set colA =
(case when colB is null then colE
when colE is null then colB
when colB < colE then colE
else colB
end) / (case when ABS(colC + colD) < 0.001 then 0.001 else colC + colD
end)
but this gets messy and obscures the underlying logic.
Could you clarify what you mean by "can be done as one update with no
functions"? This *is* one update ...|||I guess the following could work instead of "f_IsZero":
COALESCE(NULLIF(ROUND( expression , 3, 1 ), 0.0), 0.001)
... and that just leaves finding a suitable replacement for
"dbo.f_MaxOf"
:-)