SQL Server Performance Forum – Threads Archive

Performance of Stored Procedure vs functions

Hi, Is there any performance advantage in using stored procedures over UDF in situations where I have the liberty to choose either?
I believe both are pre-compiled. So no edge on that angle. I have heard that functions involve more overhead than stored procedures. Could any one if this is correct and if so what are the additional overheads associated with functions? Thanks in advance,
Alvin

The nice thing about UDFs is that you can use them within rowset operations, either for in-line values or for retrieving data in a table format. If you would call a stored procedure to do the same, you would have to open a cursor and loop through it to get the same thing done – plus you would probably need a temp table from which to return the accumulated results as a rowset. So in this respect UDFs are great, and stored procedures can be a burden. However, there are a number of issues with UDFs, mostly caused by programmers new to T-SQL, who are glad that they recognize the familiar concept of ‘function’ in a (to them) alien programming language, and who then start using UDFs in ways that kill performance. For instance, they would create a UDF to implement some complicated logic, which can also be handled in a WHERE clause. With a WHERE clause, SQL Server can use its built-in intelligence to find shortcuts to cover the whole of the data, using indexes. With a UDF, those shortcuts are simply closed off and all of the data gets to be processed. So this can lead to a lot of redundant processing. So in all, it really depends on what you’re trying to achieve to see whether you need a stored procedure or a UDF. I don’t think there’s any particular overhead involved either way.

Thanks!!

Hi Adriaan, Can you give me an example of using an UDF instead of a cursor? Thanks,
Ram "It is easy to write code for a spec and walk in water, provided, both are freezed…"

You can get information by referring to Books online and linkshttp://www.extremeexperts.com/SQL/Articles/UDFFunctions.aspx,http://msdn.microsoft.com/msdnmag/issues/03/11/DataPoints/ also. Satya SKJ
Contributing Editor & Forums Moderator
http://www.SQL-Server-Performance.Com
This posting is provided â€œAS ISâ€ with no rights for the sake of knowledge sharing.

Thanks Satya. It was really a useful link for understanding UDFs Thanks,
Ram "It is easy to write code for a spec and walk in water, provided, both are freezed…"

Refer this to know about the usage of Function where Cursor also used in such cases
http://sqljunkies.com/WebLog/amachanic/archive/2004/11/10/5065.aspx?Pending=true Madhivanan Failing to plan is Planning to fail

]]>