Is it better to use sprocs or functions?

Question

Post reply

Is it better to use sprocs or functions?

John-250225

Mr or Mrs. 500

Points: 588
More actions
June 14, 2006 at 7:27 pm

#113384

Is their a performance difference with using either or?

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

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

PW-201837 SSC-Insane Points: 20805 More actions · Answer 1

They both serve a different purpose, so performance comparison is not really applicable.

colin.Leversuch-Roberts SSC Guru Points: 52643 More actions · Answer 2

unless you want to degrade performance in which using functions badly will certainly achive this!

[font="Comic Sans MS"]The GrumpyOldDBA[/font]
www.grumpyolddba.co.uk
http://sqlblogcasts.com/blogs/grumpyolddba/

John-250225 Mr or Mrs. 500 Points: 588 More actions · Answer 3

can you give me some examples of using functions badly? appreciate it!

PW-201837 SSC-Insane Points: 20805 More actions · Answer 4

A Select statement using a function to generate a column. So far so good - looks harmless enough:

Select dbo.SomeFunction(t.Column1) As FunctionResult

From YourTable As t

Where ...

The function definition:

Create Function dbo.SomeFunction(@Param As varchar)

As

Begin

Return (

Select Max(SomeValue)

From SomeOtherTable

Where SomeKey = @Param

)

End

Ouch. You've essentially created a cursor and created a performance issue. The function performs another SELECT, which has to get processed for *every* row through the main resultset. Horrible performance awaits. And a profitable consulting gig awaits the SQL professional who knows not to use functions like this and who gets called in to clean up the mess 🙂

Jeff Moden SSC Guru Points: 1004340 More actions · Answer 5

It really depends on the sproc you build... if it returns a scalar value, as does a function, then there's no advantage to a sproc and it's easier to use the function.

But, PW hit the nail on the head... The real key, as PW implied, is that both (Scalar proc or function) are a form of "RBAR" (Row By Agonizing Row). Sure, there are some very useful functions that you can build but they are not a panacea nor a replacement for good set based code.

Now, for GUI support code, functions are pretty good because, ususally, a GUI is working with one row at a time or a very small set of rows. Again, though, PW is correct... a function like the example PW gave just shouldn't be allowed in the database...

--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)