Does function get evaluated twice if used as criteria?

Hello
This newsgroup seems to have deleted my previous messages?
So thanks to Louis here for previous help. I am now getting the resultsets i
want. Another question:
For the select query below, does the function dbo.ACEOr get evaluted once or
twice? I.e is the reference to db.ACEOr evaluated again in the "WHERE"
criteria or does the sql server engine just treat it as a variable and
recognise that its already been evaluated in the SELECT DISTINCT part of the
query.
SELECT DISTINCT tblSalesTerritory.ACEId, dbo.ACEOR(tblSalesTerritory.ACEId,
@.Authority) AS AuthLevel, WHERE dbo.ACEOR(tblSalesTerritory.ACEId,
@.Authority) >0
There is no noticable performance hit at the moment but i expect i should
know this for future reference.
Thanks.
IanIan,
Something is missing in the statement.

> SELECT DISTINCT tblSalesTerritory.ACEId, dbo.ACEOR(tblSalesTerritory.ACEId
,
> @.Authority) AS AuthLevel, WHERE dbo.ACEOR(tblSalesTerritory.ACEId,
> @.Authority) >0
SELECT DISTINCT
tblSalesTerritory.ACEId,
dbo.ACEOR(tblSalesTerritory.ACEId, @.Authority) AS AuthLevel
from
tblSalesTerritory
WHERE
dbo.ACEOR(tblSalesTerritory.ACEId, @.Authority) > 0
go
I think that the function will be evaluated one time per each row to filter
the rows based on the where clause and one time per each row that passed the
filter.
You can verify this with the execution plan or you can create a dummy stored
procedure, include the statement in the sp and then debug the sp. Do the
debug with few rows.
Example:
use northwind
go
create function dbo.ufn_f1 (
@.orderid int
)
returns int
as
begin
return (@.orderid)
end
go
set showplan_text on
go
select orderid, customerid, orderdate, dbo.ufn_f1(dbo.orders.orderid) as c1
from dbo.orders
where dbo.ufn_f1(dbo.orders.orderid) between 10250 and 10260
go
set showplan_text off
go
drop function dbo.ufn_f1
go
Result:
StmtText
----
---
|--Compute
Scalar(DEFINE:([Expr1002]=[dbo].[ufn_f1](Convert([Orders].[OrderID]))))
|--Filter(WHERE:([dbo].[ufn_f1](Convert([Orders].[OrderID]))>=10250
AND [dbo].[ufn_f1](Convert([Orders].[OrderID]))<=10260))
|--Clustered Index
Scan(OBJECT:([Northwind].[dbo].[Orders].[PK_Orders]))
AMB
"Ian Evitable" wrote:

> Hello
> This newsgroup seems to have deleted my previous messages?
> So thanks to Louis here for previous help. I am now getting the resultsets
i
> want. Another question:
> For the select query below, does the function dbo.ACEOr get evaluted once
or
> twice? I.e is the reference to db.ACEOr evaluated again in the "WHERE"
> criteria or does the sql server engine just treat it as a variable and
> recognise that its already been evaluated in the SELECT DISTINCT part of t
he
> query.
> SELECT DISTINCT tblSalesTerritory.ACEId, dbo.ACEOR(tblSalesTerritory.ACEId
,
> @.Authority) AS AuthLevel, WHERE dbo.ACEOR(tblSalesTerritory.ACEId,
> @.Authority) >0
> There is no noticable performance hit at the moment but i expect i should
> know this for future reference.
> Thanks.
> Ian
>
>|||Kalen will probably know if the optimizer is always smart enough to
factor out a common sub-expression.
In standard SQL/PSM, you can declare a procedure to be DETERMINISTIC or
not, so the optimizer can take appropriate action. A non-deterministic
function has to be re-evaluated for each occurence and they can be a
problem to optimize.|||On Sat, 10 Sep 2005 22:56:30 +1200, Ian Evitable wrote:

>Hello
>This newsgroup seems to have deleted my previous messages?
Hi Ian,
You mean the two cursor-related questions that you posted some five
hours before this message? They are in the group, together with some
replies.
(snip)
>For the select query below, does the function dbo.ACEOr get evaluted once o
r
>twice?
Twice.
To prevent this, use a derived table:
SELECT DISTINCT ACEId, AuthLevel
FROM (SELECT ACEId,
dbo.ACEOR(ACEId, @.Authority) AS AuthLevel
FROM tblSalesTerritory) AS der
WHERE AuthLevel > 0
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)|||Hello,
Yes your right i must have screwed up the cut and paste. I meant to just rip
out the selected fields because they weren't really pertinent to the
question but it looks like i ripped out the FROM source as well.
Ian
"Alejandro Mesa" <AlejandroMesa@.discussions.microsoft.com> wrote in message
news:0265144C-7714-4675-9B16-CA4102B8CA38@.microsoft.com...
> Ian,
> Something is missing in the statement.
>
dbo.ACEOR(tblSalesTerritory.ACEId,
> SELECT DISTINCT
> tblSalesTerritory.ACEId,
> dbo.ACEOR(tblSalesTerritory.ACEId, @.Authority) AS AuthLevel
> from
> tblSalesTerritory
> WHERE
> dbo.ACEOR(tblSalesTerritory.ACEId, @.Authority) > 0
> go
> I think that the function will be evaluated one time per each row to
filter
> the rows based on the where clause and one time per each row that passed
the
> filter.
> You can verify this with the execution plan or you can create a dummy
stored
> procedure, include the statement in the sp and then debug the sp. Do the
> debug with few rows.
> Example:
> use northwind
> go
> create function dbo.ufn_f1 (
> @.orderid int
> )
> returns int
> as
> begin
> return (@.orderid)
> end
> go
> set showplan_text on
> go
> select orderid, customerid, orderdate, dbo.ufn_f1(dbo.orders.orderid) as
c1
> from dbo.orders
> where dbo.ufn_f1(dbo.orders.orderid) between 10250 and 10260
> go
> set showplan_text off
> go
> drop function dbo.ufn_f1
> go
> Result:
>
> StmtText
> ----
----
> |--Compute
> Scalar(DEFINE:([Expr1002]=[dbo].[ufn_f1](Convert([Orders].[OrderID]))))
> |--Filter(WHERE:([dbo].[ufn_f1](Convert([Orders].[OrderID]))>=10250
> AND [dbo].[ufn_f1](Convert([Orders].[OrderID]))<=10260))
> |--Clustered Index
> Scan(OBJECT:([Northwind].[dbo].[Orders].[PK_Orders]))
>
> AMB
> "Ian Evitable" wrote:
>
resultsets i
once or
the
dbo.ACEOR(tblSalesTerritory.ACEId,
should|||
> You mean the two cursor-related questions that you posted some five
> hours before this message? They are in the group, together with some
> replies.
>
Yes. I can see them in Google newsgroups but not via my Out look express
anymore? Weird.

>To prevent this, use a derived table:

>SELECT DISTINCT ACEId, AuthLevel
>FROM (SELECT ACEId,
> dbo.ACEOR(ACEId, @.Authority) AS AuthLevel
> FROM tblSalesTerritory) AS der
>WHERE AuthLevel > 0
Ahah. Ok great. That works nicely.
T-SQL's actually quite powerful and allows a reasonably high level of DAL
encapsulation/blackboxing if used correctly. Im genuinely chuffed to be able
to keep this "logic" at the DAL rather than pulling into the middle tier and
using 3-4GL langauges to do the job.
Thanks to all for the tips.
Ian|||On Sun, 11 Sep 2005 18:48:17 +1200, Ian Evitable wrote:

>
>Yes. I can see them in Google newsgroups but not via my Out look express
>anymore? Weird.
Hi Ian,
Off topic for this group, but...
Maybe you accidentally deleted your messages from the Outlook archive?
Or maybe Outlook deleted them as a result of some setting - check Extra
/ Options / Maintenance (I'm not sure if these are the exact names of
the options, as I'm translating from the Dutch version of OE) and check
how long you have chosen to keep the newsgroup messages in OE.
Or better yet: upgrade to a better news reader! <g>
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)|||> Or maybe Outlook deleted them as a result of some setting
If you mean the dumb question / dumb response setting you're probably right.
Outlooks clearly trying to save me from myself.
:)