-1

I know, this was discussed several times (most discussions are some years back...).

But - looking through these discussions - I get the feeling, that there is a broadly spread common sense which is simply wrong: TV-UDFs are bad in performance.

Many people prefer SPs to collect data, why?

When I say TV-UDF, I'm speaking about the single-statement-UDF ("Inline-UDF") only. The optimizer will handle this right as clever, as if this part was written directly into the query. Multi-statement-UDFs perform much worse...

From my point of view, why you ALMOST EVER should use an UDF to collect data, I see these points:

  • Best performance (I did a lot of comparisons!)
  • You can easily use them with JOIN or APPLY and reach very complex but still readable queries (I use one "univers" query consisting of more than 30 functions coming up with more than 1000 columns - in seconds!)
  • You can call them from everywhere (e.g. to fill an EXCEL-sheet with one call via ODC)
  • You can mix them with XML-calls perfectly
  • If your select needs only a sub-set of the columns the optimizer will skip unneeded parts - not so with SP
  • With inline-UDFs the optimizer can predict the row count and is able to use all its capabilities of indexes, statistics... not so with SP
  • With inline-UDFs there's no need to write the whole table definition for an insert when you want to use the data in the latter
  • You could "wrap" an UDF with Select count(*) from(select * from dbo.MyFunc()) to predict the row count. The optimizer will execute only the parts needed to get the count...
  • And - last but not least - as the UDF never writes anything, it is much lighter in point of locking

I use multi-statement-UDFs when the result is one distinct row in any case and SPs when - which is VERY rare - a cursor or dynamic sql is needed.

So: Why are so many people using SPs to collect data? Why do so many people think, that UDFs are bad? Is there any good reason or is this just old common sense?

Thank you for your inputs!

Shnugo
  • 66,100
  • 9
  • 53
  • 114
  • OK, I understand this "on hold" due to "primarily opinion-based" in a way... But I do like technical discussions... My question is not "opinion based" and is not "free of facts"... I wanted to start a discussion because - in my point of view - using SPs to execute a SELECT is just bad practice. But I see that even very experienced developers to it this way and I think, that a discussion could be worthy. So if you want to reopen, I'm happy... – Shnugo Jul 08 '15 at 20:26
  • Since you've "pinged" me about this question elsewhere, I'll respond here - it is opinion based and there's no clear concensus on these issues. You've stated " I wanted to start a discussion because..." but please look at [What types of questions should I avoid asking?](http://stackoverflow.com/help/dont-ask): "If your motivation for asking the question is “I would like to participate in a discussion about ______”, then you should not be asking here." – Damien_The_Unbeliever Jul 10 '15 at 10:52
  • @Damian, OK, this might be the wrong place for a general discussion. But still: A SP is an awfull way to select data! Your argument with ORDER BY is wrong (btw: You can add a sorted field with ROW_NUMBER to your resultset - which is even better. Many people do this that way - why not discuss it (not "opinion based" but with clear arguments? – Shnugo Jul 10 '15 at 11:01

1 Answers1

1

I think you are comparing apples and oranges and I at least have never seen any discussions about this. There are discussions about if you should use UDFs at all and there are discussions about if you should use stored procedures or ad hoc SQL.

An inline UDF is something you can use in a query and a stored procedure is something you can execute and most of your bullet points is a consequence of that difference.

An inline UDF is more like a view than a stored procedure. A parameterized view that can be used in queries and can sometimes be used to speed things up.

Best performance (I did a lot of comparisons!)

I would very much like to see a scenario where an inline UDF and stored procedure does the same thing and have different performance.

And - last but not least - as the UDF never writes anything, it is much lighter in point of locking

If the stored procedure never writes anything there is no difference in locking.

So: Why are so many people using SPs to collect data?

Don't know about people but for me it is all about the discussion on stored procedure vs ad hoc sql. I prefer stored procedures other prefer ad hoc. If you want to use user defined functions instead of procedures you end up in the ad hoc sql camp.

Community
  • 1
  • 1
Mikael Eriksson
  • 136,425
  • 22
  • 210
  • 281
  • Most data collecting SPs I cross by are either quite old and never changed (hence there was a time where UDF was "bad") or are done by people who just don't know how to avoid cursors, loops, variable declarations. Many don't even know about the difference of a "normal" and an "inline" UDF. So - mainly for tradition - people keep saying that UDFs are bad. For its not apples and oranges... To keep in pictures: Collecting data with a SP is rather carrying a letter with a truck :-) – Shnugo Jul 08 '15 at 13:20