数据库函数 VS Case 语句

2021-09-10 00:00:00 sql tsql sql-server sql-function

昨天我们遇到了一个场景，必须获取 db 字段 的类型，并在此基础上我们必须编写字段的描述.喜欢

Yesterday we got a scenario where had to get type of a db field and on base of that we had to write the description of the field. Like 

Select ( Case DB_Type When 'I' Then 'Intermediate'
                      When 'P' Then 'Pending'
                      Else 'Basic'
         End)
From DB_table

我建议写一个 db 函数而不是这个 case 语句，因为那样会更可重用.喜欢

I suggested to write a db function instead of this case statement because that would be more reusable. Like

Select dbo.GetTypeName(DB_Type) 
from DB_table

有趣的是，我们的一位开发人员说使用数据库函数将低效，因为数据库函数比Case慢声明.我在互联网上搜索以找到在效率方面更好的答案，但不幸的是我没有发现任何可以被认为是满意的答案.请赐教您的想法，哪种方法更好?

The interesting part is, One of our developer said using database function will be inefficient as database functions are slower than Case statement. I searched over the internet to find the answer which is better approach in terms of efficiency but unfortunately I found nothing that could be considered satisfied answer. Please enlighten me with your thoughts, which approach is better?

推荐答案

UDF function is always slower than case statements

请参考文章

http://blogs.msdn.com/b/sqlserverfaq/archive/2009/10/06/performance-benefits-of-using-expression-over-user-defined-functions.aspx

以下文章建议您何时使用 UDF

The following article suggests you when to use UDF

http://www.sql-server-performance.com/2005/sql-server-udfs/

总结:

使用用户定义的函数时会付出很大的性能损失.当查询将 UDF 应用于大量行(通常为 1000 行或更多)时，这种损失表现为较差的查询执行时间.由于 SQL Server 数据库引擎必须像处理一样创建自己的内部游标，因此会产生惩罚.它必须在每一行上调用每个 UDF.如果在 WHERE 子句中使用 UDF，这可能会作为过滤行的一部分发生.如果在选择列表中使用 UDF，则在创建查询结果以传递到查询处理的下一阶段时会发生这种情况.SQL Server 最慢的是逐行处理.

There is a large performance penalty paid when User defined functions is used.This penalty shows up as poor query execution time when a query applies a UDF to a large number of rows, typically 1000 or more. The penalty is incurred because the SQL Server database engine must create its own internal cursor like processing. It must invoke each UDF on each row. If the UDF is used in the WHERE clause, this may happen as part of the filtering the rows. If the UDF is used in the select list, this happens when creating the results of the query to pass to the next stage of query processing. It's the row by row processing that slows SQL Server the most.

相关文章