问题描述
我有一个查询，它使用一组复杂的CASE语句，一些嵌套，一些用CASE语句的COALESCE，这是一个很难管理的问题。
相同的基本逻辑适用于SELECT中的大约12列，并且可以被模块化并放置在一个函数中 - 具有包括一致性和原型设计的便利性等优点。
将逻辑放入一个函数会严重影响性能吗？ 函数本身是否比较慢？
SELECT从5个表中拉出大约100,000行，所以性能是非常重要的。
SQL Server 2005
通常，选择标量函数的结果不会太大伤害，但是按它过滤可能很容易花费数百秒（但不一定）。 / p>
如果您需要使用标量函数结果进行筛选（ WHERE col = dbo.scalar_function（）），帮助妈妈而不是内联表值函数。它将返回其值作为结果表的唯一一行。然后，您可以使用函数结果执行 inner join ，从而有效地过滤返回的值。这是有效的，因为SQL Server总是能够展开内联表值函数并将它们内联到调用查询中。
注意，如果函数是多步骤一。这些不能解开。
I have a query which uses a complicated set of CASE statements, some nested, some with a COALESCE of CASE statements, and it is a pain to manage.
The same basic logic applies to around 12 columns in the SELECT, and could be modularised and placed in a function - with benefits including consistency and ease of prototyping.
Will putting the logic into a function massively impede performance? Are functions inherently slower?
The SELECT Pulls from 5 tables each of around 100,000 rows, so performance is of some importance.
SQL Server 2005
Normally, selecting a result of a scalar function won't hurt much, but filtering by it may easily cost hundreds of seconds (not necessarily though).
If you need to filter by a scalar function result (WHERE col = dbo.scalar_function()), it often helps to make an inline table-valued function instead. It would return its value as the only row of the result table. You would then do inner join with the function result, effectively filtering by the returned value. This works because SQL Server is always able to unwind inline table-valued functions and inline them into the calling query.
Note this trick won't work if the function is a multi-step one. These cannot be unwound.
这篇关于将使用函数来简化SQL查询的大规模映像表现？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！