用户定义函数和存储过程之间的性能差异 [英] performance difference between User Defined Function and Stored Procedures
问题描述
如果一个语句返回的行在数据库上做了简单的选择，那么使用函数和过程实现它时性能有差别吗？
我知道最好是用函数来做，但它真的更快吗？
在一个函数内部运行的查询和一个程序内部运行之间的速度。
存储过程在汇总结果时存在问题，它们不能与其他存储过程合成。 onyl解决方案非常麻烦，因为它涉及到捕获过程输出到 INSERT ... EXEC ... 的表格中，然后使用结果表格。
函数具有高度可组合性的优点，因为表值函数可放置在希望使用表表达式的任何位置（FROM，JOIN，APPLY，IN等）。但函数在函数中允许的内容有什么非常严格的限制，什么不是，完全是因为它们可以出现在查询的任何地方。
所以真的是苹果以橙子。决定不是由绩效驱动的，而是由需求决定的。作为一般规则，任何返回数据集的东西都应该是一个视图或一个表值函数。任何操纵数据的东西都必须是一个过程。
If a statement return rows doing a simple select over the data base, is there performance difference between implement it using Function and Procedures? I know it is preferable to do it using function, but it is really faster?
There is no difference in speed between a query run inside a function and one run inside a procedure.
Stored procedures have problems aggregating results, they cannot be composed with other stored procedures. The onyl solution is really cumbersome as it involves catching the procedure output into a table with INSERT ... EXEC ... and then using the resulted table.
Functions have the advantage of being highly composable as a table value function can be placed anywhere a table expression is expected (FROM, JOIN, APPLY, IN etc). But functions have some very severe limitations in terms of what is permitted in a function and what is not, exactly because they can appear anywhere in a query.
So is really apple to oranges. The decision is not driven be performance, but by requirements. As a general rule anything that returns a dataset should be a view or a table valued function. Anything that manipulates data must be a procedure.
这篇关于用户定义函数和存储过程之间的性能差异的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
