Dumb question, but why run function twice? - SQL Server Forums

Home | Weblogs | Forums

Site Sponsored By: SQLDSC - SQL Server Desired State Configuration

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Dumb question, but why run function twice?

Author

Topic

Vinnie881
Master Smack Fu Yak Hacker

1231 Posts

Posted - 2007-09-30 : 14:41:31

Here's probably a really dumb question, but is there a way to referance a field in the select list.

for example lests say I had this query


select a.*,dbo.myfunction(a.MyField) as Myvalue
from
TMp1 a
where dbo.myfunction(a.MyField) = 1

If I wanted to filter by myfunction, why do I have to make sql do that work twice (Once in the select line and once in the where clause), rather then just referance the field like


select a.*,dbo.myfunction(a.MyField) as Myvalue
from
TMp1 a
where 
Myvalue = 1

Obviously that is not permitted in Sql, but Assuming Myfunction is a complex function that does a lot of work, is my best option on this to create a tmp set?


Select * From
(
select a.*,dbo.myfunction(a.MyField) as Myvalue
from
TMp1 a
) aa
where 
aa.Myvalue = 1

Thanks!

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2007-09-30 : 15:16:54

yes that's the way to go.

_______________________________________________
Causing trouble since 1980
blog: http://weblogs.sqlteam.com/mladenp
SSMS Add-in that does a few things: www.ssmstoolspack.com

nr
SQLTeam MVY

12543 Posts

Posted - 2007-09-30 : 15:34:23

>> why do I have to make sql do that work twice
The optimiser doesns't execute the sql in the order you code it.
What you have done is saved oyurself coding the function call twice - you will have to look at the query plan to see if there's any change between the two statements.

As to the why - it's because the aliased columns in the resultset aren't available until the resultset is formed.

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2007-09-30 : 15:36:13

well in ss2k they aren't. you ca use aliases in SS 2k5 though

_______________________________________________
Causing trouble since 1980
blog: http://weblogs.sqlteam.com/mladenp
SSMS Add-in that does a few things: www.ssmstoolspack.com

Kristen
Test

22859 Posts

Posted - 2007-10-01 : 02:27:45

Can you give a small example please Spirit?

Thanks

Kristen

Vinnie881
Master Smack Fu Yak Hacker

1231 Posts

Posted - 2007-10-01 : 02:44:43

So is it confirmed, If I want to return a aliased column in my select list, and I also want to filter by it, Sql will need to evaluate the function twice?

Nr, I'm not sure if I'm following you. Are you saying in the below query:


Select * From
(
select a.*,dbo.myfunction(a.MyField) as Myvalue
from
TMp1 a
) aa
where 
aa.Myvalue = 1

it does not nessassarly mean my function will be ran just 1 time?

like in


select a.*,dbo.myfunction(a.MyField) as Myvalue
into #tmp
from
TMp1 a

select * from #tmp where myvalue =1

Can you please clarify a little more.

Thanks

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2007-10-01 : 03:19:03

Hmm.. i was 100% sure that ss2k5 supported alias reuse.
didn't it use to? or am i loosing my mind?

_______________________________________________
Causing trouble since 1980
blog: http://weblogs.sqlteam.com/mladenp
SSMS Add-in that does a few things: www.ssmstoolspack.com

Kristen
Test

22859 Posts

Posted - 2007-10-01 : 05:20:58

"Sql will need to evaluate the function twice? "

The optimiser may very well only evaluate it ONCE, but you'll have to review the Query Plan to be sure.

Kristen

Vinnie881
Master Smack Fu Yak Hacker

1231 Posts

Posted - 2007-10-01 : 18:58:48

I'm following you there, what do you feel is likley the best way to write the query?

Also spirit, is it possible you are thinking of the order by clause? You can use the re-use the aliased column there.

Kristen
Test

22859 Posts

Posted - 2007-10-02 : 04:22:21

I think the biggest problem with


select a.*,dbo.myfunction(a.MyField) as Myvalue
from TMp1 a
where dbo.myfunction(a.MyField) = 1

is that the code appears twice, and a programmer may forget to change it in both places during maintenance. That's much more expensive, in my book, than any performance issue!

So I quite like:


Select * 
From
(
    select a.*,dbo.myfunction(a.MyField) as Myvalue
    from TMp1 a
) aa
where aa.Myvalue = 1

(although I wouldn't use SELECT *)

Kristen

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-10-02 : 04:30:47

Also, if you are going to use the function as a filter, there is absolutely no need to put it in the SELECT statement.

select	a.*,
	1 as myvalue
from	tmp1 as a
where	dbo.myfunction(a.myfield) = 1

E 12°55'05.25"
N 56°04'39.16"

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-10-02 : 04:34:02

However this is going to be a little hard if you want a range back.

;WITH Yak
AS (
	SELECT	*,
		dbo.MyFunction(MyField) AS MyValue
	FROM	Table1
)

SELECT	*
FROM	Yak
WHERE	MyValue BETWEEN 1 AND 5

E 12°55'05.25"
N 56°04'39.16"

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2007-10-02 : 05:03:24

quote:
Originally posted by Vinnie881

I'm following you there, what do you feel is likley the best way to write the query?

Also spirit, is it possible you are thinking of the order by clause? You can use the re-use the aliased column there.

yes that's what i thought!
it kind of sucks that it can't be reused in iother parts... but it's understandable because then they'd have to completly rewrite the query parser

_______________________________________________
Causing trouble since 1980
blog: http://weblogs.sqlteam.com/mladenp
SSMS Add-in that does a few things: www.ssmstoolspack.com

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources