Reduce function call in a Select statment

I have a stored procedure that is something like this :
SELECT TableA.*, value = dbo.functionA(TableA.id, x,x)
FROM TableA
WHERE TableA.id = 'SomeValue'
AND dbo.functionA(TableA.key, x,x) > 0
This procedure is executed over 1000 times in a hour, and the function calls
in the proc is called twice each time this procedure is called.
Is there a way to rewrite this statement so that the function call is only
made once'
Thanks
MikeSee if you can rewrite without the function - that's the fastest. Failing
that:
select
*
from
(
select
*
, value = dbo.functionA(id, x,x)
from
TableA
where
id = 'SomeValue'
) as x
where
value > 0
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinpub.com
.
"Mike" <y4forums.t.mdgoyal@.xoxy.net> wrote in message
news:%23oPVPH4UGHA.2492@.TK2MSFTNGP11.phx.gbl...
I have a stored procedure that is something like this :
SELECT TableA.*, value = dbo.functionA(TableA.id, x,x)
FROM TableA
WHERE TableA.id = 'SomeValue'
AND dbo.functionA(TableA.key, x,x) > 0
This procedure is executed over 1000 times in a hour, and the function calls
in the proc is called twice each time this procedure is called.
Is there a way to rewrite this statement so that the function call is only
made once'
Thanks
Mike|||Thanks
"Tom Moreau" <tom@.dont.spam.me.cips.ca> wrote in message
news:OJBQ%23N4UGHA.4348@.TK2MSFTNGP09.phx.gbl...
> See if you can rewrite without the function - that's the fastest. Failing
> that:
> select
> *
> from
> (
> select
> *
> , value = dbo.functionA(id, x,x)
> from
> TableA
> where
> id = 'SomeValue'
> ) as x
> where
> value > 0
> --
> Tom
> ----
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
> SQL Server MVP
> Columnist, SQL Server Professional
> Toronto, ON Canada
> www.pinpub.com
> .
> "Mike" <y4forums.t.mdgoyal@.xoxy.net> wrote in message
> news:%23oPVPH4UGHA.2492@.TK2MSFTNGP11.phx.gbl...
> I have a stored procedure that is something like this :
> SELECT TableA.*, value = dbo.functionA(TableA.id, x,x)
> FROM TableA
> WHERE TableA.id = 'SomeValue'
> AND dbo.functionA(TableA.key, x,x) > 0
> This procedure is executed over 1000 times in a hour, and the function
> calls
> in the proc is called twice each time this procedure is called.
> Is there a way to rewrite this statement so that the function call is only
> made once'
> Thanks
> Mike
>