performance question

I've got 3 functions. one that calculates a value, and two other ones that
return a value based on the value returned by the first.
I use these in a view
example:
select
dbo.calcvalue(t.[id]) as value,
dbo.calc1(calcvalue(t.[id]) )) as val1,
dbo.calc2(calcvalue(t.[id]) )) as val2
from
tabletest t
will calcvalue() get called 3 times? or would it be more efficient to use
two views.
example:
view 1:
select
dbo.calcvalue(t.[id]) as value
from
tabletest t
view 2:
select
dbo.calc1(value) as val1,
dbo.calc2(value) as val2
from
view1user defined functions must be deterministic - give the same output for the
same input. I think one reason this restriction is there is so that sql can
eliminate performing second and third calls to your function. That said, I
don't know if it will call it more than once. You can insure it won't by
using a derived table.
select t.id, value, dbo.calc1(value) as val1, dbo.calc2(value) as val2
from
(
select id, calcvalue(id) as value
from tabletest
) as t
Jeremy wrote:
>I've got 3 functions. one that calculates a value, and two other ones that
>return a value based on the value returned by the first.
>I use these in a view
>example:
>select
> dbo.calcvalue(t.[id]) as value,
> dbo.calc1(calcvalue(t.[id]) )) as val1,
> dbo.calc2(calcvalue(t.[id]) )) as val2
>from
> tabletest t
>will calcvalue() get called 3 times? or would it be more efficient to use
>two views.
>example:
>view 1:
>select
> dbo.calcvalue(t.[id]) as value
>from
> tabletest t
>view 2:
>select
> dbo.calc1(value) as val1,
> dbo.calc2(value) as val2
>from
> view1
--
Message posted via http://www.sqlmonster.com|||Jeremy,
The rules for scalar UDFs allow for many types of optimization,
including substitution. However, a simple test will show that both SQL
Server 2000 and SQL Server 2005 will not reuse the result of the UDF for
the same row.
So if you have an expensive dbo.calcvalue, and a very cheap dbo.calc1,
then the query
select dbo.calcvalue(id), dbo.calc1(dbo.calcvalue(id)) from t
will need almost twice as long to finish when compared to
select dbo.calcvalue(id), dbo.calc1(id) from t
--
Gert-Jan
Jeremy wrote:
> I've got 3 functions. one that calculates a value, and two other ones that
> return a value based on the value returned by the first.
> I use these in a view
> example:
> select
> dbo.calcvalue(t.[id]) as value,
> dbo.calc1(calcvalue(t.[id]) )) as val1,
> dbo.calc2(calcvalue(t.[id]) )) as val2
> from
> tabletest t
> will calcvalue() get called 3 times? or would it be more efficient to use
> two views.
> example:
> view 1:
> select
> dbo.calcvalue(t.[id]) as value
> from
> tabletest t
> view 2:
> select
> dbo.calc1(value) as val1,
> dbo.calc2(value) as val2
> from
> view1