代码之家 › 专栏 › 技术社区 › Abe Miessler

在select执行计算时提高SQL性能

performance sql-server-2005 sql-server sql

Abe Miessler · 技术社区 · 15 年前

在下面的查询中,将case语句移动到一个udf是否更好?如果是为什么?

SELECT id,
       name,
       case
         when v1 = 'Y' then 'something'
         when v2 = 'K' then 'something else'
         when v3 is null then 'dont know'
         else 'default'
       end
from table

4 回复 | 直到 15 年前

Michael Bray 15 年前

不,我会照原样留下。使用UDF(通常)可以 wierd side effects and limitations 而这个查询并不能证明使用它们是正当的。UDF性能不应该比您从这个直接查询中获得的性能更好。

Irwin M. Fletcher 15 年前

UDF通常比查询中的case语句执行得最差。但是对于大多数SQL,最好同时尝试两者并比较结果。

Paul Sasik 15 年前

如果你进入一个单独的UDF,它可能不会有任何影响。为了提高性能,SQL Server优化了这些类型的操作,您的UDF可能无论如何都会“内联”。

momo 15 年前

顺便说一句(抱歉,我记不起我在哪里读过这个),在使用case语句的“short”语法形式时,优化器有机会获得更好的执行计划。即使最终简短的形式只是语法上的糖分,它也给优化器一个提示,即所有的比较都是基于简单的等式,而不涉及复杂的表达式。

由于您使用了三个不同的列v1、v2和v3,因此无法切换给定的case语句。但是,如果您使用的是同一列,比如v1,那么这种修改有时会表现得更好:

SELECT
   id,
   name,
   case Coalesce(v1, 'dont know')
      when 'Y' then 'something'
      when 'K' then 'something else'
      when 'dont know' then 'dont know'
      else 'default'
   end
from table

好吧,也许这不太理想,因为处理空值时需要设置Continctions。我只是想指出在某些情况下可能进行的优化,因为您正在寻找一般的优化提示。

就其价值而言,我完全同意UDF的表现会更差。内联几乎可以普遍地提高性能,因为它避免了进行调用和从调用返回所需的所有工作(除非优化器为您秘密地内联它)。

在这种特定情况下的另一个想法是,您可以考虑使用一个带有派生表的联接,该派生表中包含文本值:

SELECT
   id,
   name,
   coalesce(x.result, 'dont know')
from
   table t
   left join (
       select 'Y', 'something'
       union all select 'K', 'something else'
       union all select '%', 'default'
   ) x (value, result) on t.v1 like x.value

同样,这可能不是完美的,因为我选择了用like进行空处理的方式。然而,它充分展示了这一技术。

其他要点:

测试总是为了找出在任何情况下什么性能最好。填充一个满10万或100万行的表,并在运行探查器的情况下进行一些测试,以捕获CPU、读、写和持续时间。在较短的时间内,更喜欢较低的CPU/读取。
虽然执行计划通常是很好的指南,但不要完全信任执行计划成本,因为它们完全忽略了UDF的内部成本,并且不总是正确地公开CPU/读取权衡。
如果能避免少量的读操作,那么花一点CPU通常会更好。像我上面使用的派生表的执行计划中显示的“常量扫描”的成本总是很低的。这一成本远低于几乎所有涉及磁盘访问的读取次数。