are stored procedures faster than functions or not

Question

I am using stored procedures at the moment but I have a lot of stuff like this:

if ... begin select 'success' return end
if ... begin select 'notgood' return end
if ... begin select 'someelse' return end

so I'm thinking that it would be a better idea to use functions instead, but is there any performance difference between functions and stored procedures ?

Answer 1

Generally, there shouldn't be much difference across platforms (I'm assuming that you are referring to SQL Server here though) as it should be the same operation; looking up the stored procedure/function, compiling if necessary, etc, etc.

I do know that functions in SQL Server can declare SCHEMABINDING in the CREATE FUNCTION statement, which will give you some performance gains, at the cost of being locked to the schema at the time the function is created.

Answer 2

Here is the answer to your question :)

Answer 3

The best way to tell for your scenario is to set up some tests, run them and compare the results. This isn't a cop-out answer: testing for yourself really is the best way to tell. There are many factor for your unique circumstance to take into consideration - e.g. database platform, server hardware, network latency, business/client application layers, server/network loads at different times, etc.

Answer 4

How and where the function is used matters. For example, functions called from the WHERE clause of a SELECT statement can be a performance killer. We once had a statement similar to:

SELECT * FROM TRANSACTIONS T
  WHERE IS_UGLY_TRANSACTION_CODE(T.TRANSACTION_CODE) = 'T' AND ...

The overall process was running way too slowly and the install date was headed our way way too quickly so I started instrumenting the code to figure out where the time was going. When I got the profiling report back I looked at it and saw that the job had processed 1000 transactions, and then noticed that IS_SALES_TRANSACTION had been called about half-a-million times. After a little thought and some study of the statement execution plan I realized that the routine in question was being used by the database as the primary filter in the query, and that this was causing two bad things to happen: first, the presence of the routine in the query was preventing an index from being used so the query was scanning the table, and secondly the routine was being called for every row in the table during the scan. Replacing the function call with equivalent checks on the TRANSACTION_CODE column dropped the runtime from 15 minutes to 5 seconds.