April 28, 2012 at 7:41 pm
performance wise which is better subquery or scalar function
April 28, 2012 at 8:52 pm
As a general rule, stay away from scalar functions, especially ones that contain SELECT statements. I assume this is what you are talking about because you want to compare it to a subquery. Unlike subqueries, ctes, and inline table-valued functions, scalar functions do not inform the optimizer when it puts together an execution plan. In other words, the optimizer cannot estimate the work involved in a scalar function, or determine an optimal sequence of operations. This results in poor performance.
Read up on inline table-valued functions. They are a *much* better bet for good performance.
__________________________________________________
Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills
April 29, 2012 at 1:11 pm
I'll say that "It Depends". "Memory Only" scalar functions run very quickly even when they contain the likes of a WHILE loop. They can even beat some rather sophisticated "inline" methods. On the other hand, they can run horribly slow if they reference a table.
"Rules of Thumb" are nice but only if you're in a hurry. The only way to know for sure for any given instance is to test.
--Jeff Moden
April 29, 2012 at 3:20 pm
Thank you all
April 29, 2012 at 4:09 pm
CELKO (4/28/2012)
Scalar functions are proprietary crap that screw up optimization and porting.
I've found that the unfounded and mythological belief that effective code can be ported screws up optimization and proprietary crap. 😀
--Jeff Moden
April 30, 2012 at 8:29 am
99.x% of the time you should jump through any hoop imaginable to avoid using scalar UDFs!! I wrote a chapter for the SQL Server MVP Deep Dives 2 book entitled "Death by UDF". I only touched on a few of the MANY reasons they can/will screw you.
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply