Why functions are slower the stored procedures?

Posted by Allemahesh under Sql Server on 3/26/2014 | Points: 10 | Views : 972 | Status : [Member] [MVP] | Replies : 1

Write New Post |

Search Forums | Answered

Resolved Posts |

Un Answered Posts |

Forums Home

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?

Reply | Reply with Attachment

Alert Moderator

Responses

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

1	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

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

Latest Posts