subquery vs scalar functions

Question

subquery vs scalar functions

Sarsoura

Ten Centuries

Points: 1220
More actions
April 28, 2012 at 7:41 pm

#267996

performance wise which is better subquery or scalar function

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

The Dixie Flatline SSC Guru Points: 53354 More actions · Answer 1

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

Jeff Moden SSC Guru Points: 1003943 More actions · Answer 2

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

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Sarsoura Ten Centuries Points: 1220 More actions · Answer 3

Sarsoura

Ten Centuries

Points: 1220

April 29, 2012 at 3:20 pm

#1480986

Thank you all

Jeff Moden SSC Guru Points: 1003943 More actions · Answer 4

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

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

TheSQLGuru SSC Guru Points: 134017 More actions · Answer 5

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