Why functions are slower the stored procedures?

Posted by Allemahesh under Sql Server on 3/26/2014 | Points: 10 | Views : 489 | 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?




Responses

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

Up
1
Down
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
http://www.sqlservercentral.com/Forums/Topic513411-338-1.aspx

Thanks,
A2H
My Blog

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

Login to post response