使用CASE的效率当... ISNULL / COALESCE不是空的

Consider the following scenario:

请考虑以下情形:

There are three kinds of entities, say Foo, Bar and Baz.

有三种实体,比如Foo,Bar和Baz。

Every Foo must be associated with a Bar or a Baz, but not both at the same time.

每个Foo必须与Bar或Baz关联,但不能同时关联。

The scenario is already implemented in the following way:

该方案已按以下方式实施:

There are three tables: Foo, Bar and Baz.

有三个表:Foo,Bar和Baz。

Foo has two foreign key fields: Bar_ID and Baz_ID.

Foo有两个外键字段:Bar_ID和Baz_ID。

Exactly one of those foreign key fields must be NULL.

其中一个外键字段必须为NULL。

Now I would like to build a query displaying the list of Foos, including the description of the Bar or Baz each Foo is associated to. Actually, the description of a Bar is a quite complicated formula of the fields of the corresponding row in the Bar table. The same applies to Baz.

现在我想构建一个显示Foos列表的查询,包括每个Foo关联的Bar或Baz的描述。实际上,Bar的描述是Bar表中相应行的字段的非常复杂的公式。这同样适用于Baz。

My current query looks like the following:

我当前的查询如下所示:

SELECT    Foo.*,
          CASE
            WHEN Foo.Bar_ID IS NOT NULL THEN
              -- a formula, say...
              ISNULL(Bar.LotNumber + '-', '') + Bar.ItemNumber
            WHEN Foo.Baz_ID IS NOT NULL THEN
              -- another formula, say...
              ISNULL(Baz.Color + ' ', '') + Baz.Type
          END AS 'Ba?Description'
FROM      Foo
LEFT JOIN Bar ON Bar.Bar_ID = Foo.Bar_ID
LEFT JOIN Baz ON Baz.Baz_ID = Foo.Baz_ID

Is the preceding query is more, less or equally efficient than...

前面的查询是否比...更多,更少或同等效率

SELECT    Foo.*,
          ISNULL( -- or COALESCE
            ISNULL(Bar.LotNumber + '-', '') + Bar.ItemNumber,
            ISNULL(Baz.Color     + ' ', '') + Baz.Type
          ) AS 'Ba?Description'
FROM      Foo
LEFT JOIN Bar ON Bar.Bar_ID = Foo.Bar_ID
LEFT JOIN Baz ON Baz.Baz_ID = Foo.Baz_ID

...?

3 个解决方案

#1

In theory, the CASE shlould be because only one expression is evaluated. Several chained ISNULLs will all require processing.

从理论上讲,CASE应该是因为只评估了一个表达式。几个链式ISNULL都需要处理。

However, you'd need to have a large (10000s of rows) dataset to notice any difference: most processing goes into the actual table access, JOINs etc.

但是,您需要有一个大的(10000行)数据集来注意任何差异:大多数处理进入实际的表访问,JOIN等。

Have you tried it? You can use SQL profiler to see CPU etc for each query.

你试过吗?您可以使用SQL事件探查器查看每个查询的CPU等。

#2

I believe that ISNULL is more efficient. CASE statements are always evaluated first and I believe restrict the options available to the query optimiser.

我相信ISNULL更有效率。始终首先评估CASE语句,我相信限制查询优化器可用的选项。

What does the execution plan say? Is that your actual question?

执行计划说了什么?这是你的实际问题吗?

#3

COALESCE is an ANSI function while ISNULL is an SQL Server proprietary function.

COALESCE是ANSI函数,而ISNULL是SQL Server专有函数。

They differ in type handling and some other things, and COALESCE may accept more than two arguments.

它们在类型处理和其他一些方面有所不同,而COALESCE可能会接受两个以上的参数。

For your task (two arguments, both VARCHAR) they are equivalent.

对于您的任务(两个参数,两个VARCHAR),它们是等效的。

#1