Home Forums SQL Server 2005 T-SQL (SS2K5) Choosing the faster option; Stored Procedure or Function? RE: Choosing the faster option; Stored Procedure or Function?

  • For better or worse - running a function like that will usually end up being very slow. In my experience, functions (T-SQL functions that is) are NOT know for their speed.

    Not knowing anything about this calculation, it's going to be hard to be specific on things. That being said - for complicated calculations, I've found that CLR functions are faster than T-SQL functions, although they can sometimes run into resource issues if garbage collection can't keep up, etc... If appropriate - try setting the functions to deterministic and/or precise, so that the optimizer knows it can reuse previous results based on same input.

    The problem is that the function often 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 perf penalty that it can alomst never compensate for.

    As a result, you will probably find that building the calculation directly into the query, and possibly encapculating the entire recordset in a stored proc will be the fastest execution, assuming your indexing scheme is correct and helpful, etc...

    ----------------------------------------------------------------------------------
    Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?