sql - 需要 SARGABLE 方式来过滤记录并为 NULL 指定默认值

Question

我正在创建一个适配器来接收来自客户端的自定义数据。尽管我可以建议新索引，但我无法更改它们的架构或修改它们表中的值。该方法是使用 CTE 来连接和重新格式化自定义数据，以使用我们的列名、枚举值等。重新格式化数据后，可以附加我们的标准 CTE，并从中伪造一个可以执行我们的标准分析的查询。

由于 LEFT JOIN 不匹配，或者由于数据中的值实际上为 NULL，因此重新格式化产生的某些值为 NULL。

我的任务是在许多字段中替换 NULL 的默认值，并允许将 WHERE 子句插入到查询中。目前，ISNULL 调用或CASE 语句用于处理默认值。目前，当满足 WHERE 条件时，此替换已经执行，以便有权访问我们的查询生成器的最终用户可以过滤可能是默认值的值。如果过滤器值是默认值，则应选择带有 NULL 值并替换为默认值的记录。

问题是，如果我将 myField = ISNULL(myField, 'MyDefault') 作为我的重新格式化公式，然后在洋葱的外层(后来的 CTE)中使用 WHERE myField = 'MyDefault'，那么这在哪里子句不可控制:查询优化器不会选择 myField 上的索引。

我想到的部分解决方案是不在内部 CTE 中执行任何 NULL 替换，然后使用插入 WHERE 子句的 CTE，然后使用执行所有 NULL 替换的外部 CTE。这样的查询可以使用索引。 (我已经验证了这一点。)但是，where 子句不能再期望针对默认值进行值测试也会选取具有 NULL 值的记录，因为该替换尚未发生。

有没有办法执行 null 替换，允许 SARGABLE where 过滤器，并过滤 NULL 值，就像它们保留默认值一样？

关于问题大小的注意事项:一个典型的示例涉及使用多对多关系将 600 万条记录表连接到 700 万条记录表，从而创建 1200 万条记录。当过滤器为SARGABLE时，查询大约需要10秒。当它不是 SARGABLE 时，在一台机器上需要 10 分钟以上，在更快的机器上需要 3 分钟以上。

对所选解决方案的评论:

巧妙地使用交集来允许在没有 ISNULL 或其他不可调整函数的情况下将字段与 NULL 或非 NULL 进行比较，可以将其插入到我们的代码中，而对遗留查询的更改最少。

评论 2:缺失案例

有以下六种情况:

1. 所选值不为空且不等于默认值且与过滤器值不匹配。应该排除。
2. 所选值不为空且不等于默认值且与过滤器值匹配。应该包括。
3. 所选值不为空且等于默认值且与过滤器值不匹配。应该排除。
4. 所选值不为空，并且等于默认值并且与过滤器值匹配。应该包括。
5. 所选值为 null，且过滤器值不是默认值。应该排除。
6. 所选值为 null，过滤器值为默认值。应该包括。

使用提供的解决方案，情况 4 不起作用。所选字段不为空，因此交集的前半部分有一条值为非空值的记录。但在交集的后半部分，NULLIF 语句创建了一条具有空值的记录。交集产生零记录。该记录被拒绝。我仍在寻找处理这种情况的解决方案。这么近...

更新解决方案:

我有一个解决办法。假设我正在拟合[县名称]，并且我的默认值为“未知”...

where EXISTS (
    select [County Name] 
    intersect 
    (select NULLIF('User selected county name', 'Unknown') union select 'User selected county name')
)

Answer 1

看起来您已经在动态构建查询，因此当您从工具中获取需要过滤的值时，您可以使用看起来像这样的 where 子句构建查询。

SQL Fiddle

MS SQL Server 2008 架构设置:

create table YourTable
(
  ID int identity primary key,
  Name varchar(20)
)

create index IX_YourTable_Name on YourTable(Name)

insert into YourTable values
('Name1'),
('Name2'),
(null)

查询 1:

declare @Param varchar(20)
set @Param = 'DefaultName'

select ID,
       coalesce(Name, 'DefaultName') as Name
from YourTable
where exists(select Name intersect select nullif(@Param, 'DefaultName'))

<强> Results :

| ID |        NAME |
--------------------
|  3 | DefaultName |

查询 2:

declare @Param varchar(20)
set @Param = 'Name1'

select ID,
       coalesce(Name, 'DefaultName') as Name
from YourTable
where exists(select Name intersect select nullif(@Param, 'DefaultName'))

<强> Results :

| ID |  NAME |
--------------
|  1 | Name1 |

上述查询的查询计划将使用 IX_YourTable_Name 进行查找。

引用号:Undocumented Query Plans: Equality Comparisons