Why functions are slower the stored procedures?

Posted by Allemahesh under Sql Server on 3/26/2014 | Points: 10 | Views : 438 | Status : [Member] [MVP] | Replies : 1
I am reading on post and is about "are stored procedures faster than functions or not".

Link : http://stackoverflow.com/questions/1979319/are-stored-procedures-faster-than-functions-or-not

I got the answer saying that functions are much slower then sp.

Now my question is why functions are slower then stored procedures?

Is this because of Stored procedure are precompiled objects. so execution time is much better than functions?


Posted by: A2H on: 3/26/2014 [Member] [MVP] Bronze | Points: 25

Hi Alle Mahesh,

AFAIK Function forces SQL Server to have to touch each and every single row one at a time, instead of using set-based retrieval and calculations, which end up being a huge performance penalty.
So even though a CLR function is more efficient than the T-SQL version, both are starting with such a huge penalty that it can never compensate for.
You can check with the below link for more details

Please mark my reply as answer if it helps to resolve your problem

Allemahesh, if this helps please login to Mark As Answer. | Alert Moderator

Login to post response