需要SARGABLE方法来过滤记录，并指定NULL的默认值-Java 学习之路

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

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

我的任务是在许多字段中替换NULL的默认值，并允许将WHERE子句插入到查询中 . 目前，ISNULL调用或CASE语句用于处理默认值 . 目前，在WHERE条件被命中时，已经执行了此替换，因此有权访问我们的查询构建器的最终用户可以过滤可能是默认值的值 . 如果过滤器值是默认值，则应选择具有使用默认值替换的NULL值的记录 .

问题是，如果我将myField = ISNULL（myField，'MyDefault'）作为我的重新格式化公式，并且稍后在洋葱的外层（后来的CTE）中有WHERE myField ='MyDefault'，则此where子句不是sargable：查询优化器不会在myField上选择我的索引 .

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

有没有办法执行空替换，允许SARGABLE在哪里过滤器，并过滤NULL值，就好像它们保持默认值？

关于问题大小的注释：一个典型的例子涉及将600万条记录表加入到700万条记录表中，该记录表具有创建1200万条记录的多对多关系 . 当过滤器是SARGABLE时，查询大约需要10秒钟 . 当它不是SARGABLE时，一台机器需要10分钟，而更快的机器需要3分钟以上 .

对CHOSEN解决方案的评论：

巧妙地使用交集来允许将字段比较为NULL或非NULL而不使用ISNULL或其他非可搜索函数可以通过对我们的遗留查询的最少更改来检测到我们的代码 .

评论2：遗失案件

有这六种情况：

所选值不为空且不等于默认值且与过滤器值不匹配 . 应该排除 .
所选值不为空且不等于默认值且DOES与过滤器值匹配 . 应该包括 .
所选值不为空且DOES等于默认值且与过滤器值不匹配 . 应该排除 .
所选值不为空且DOES等于默认值且DOES与过滤器值匹配 . 应该包括 .
所选值为空，过滤器值不是默认值 . 应该排除 .
所选值为空，过滤器值为默认值 . 应该包括 .

案例4无法使用提供的解决方案 . 所选字段不为空，因此交集的前半部分具有带anon-null值的记录 . 但是在交集的后半部分，NULLIF语句创建了一个空值的记录 . 交叉点产生零记录 . 该记录被拒绝 . 我仍然在寻找处理这种情况的解决方案 . 很近...

更新方案：

我有一个修复 . 说我适应[县名]，我的默认值是'未知'...

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

4 回答

0

如果使用临时表而不是CTE，则可以填充数据然后将其编入索引 .

回复于 2024-05-03T14:22:15+08:00
3

因此，最终问题是在发出数据时不使用 ISNULL （或 COALESCE ），而是使用过滤 . 问题在于，如果在谓词中使用 OR 子句，通常不能使用索引，而这几乎就是那些语句 .

最终，解决方案是使用动态sql，如果你必须在sql server中执行此操作，或者只是在sql server之外构建结束查询 . 如果您是在存储过程中执行此操作，则最终只会进行sql查询，然后在生成的查询上调用 sp_executesql .

你将在这里做的关键点是你将检查你要过滤的值（即'My Default'），如果它是那个值，那么你将添加谓词来过滤值，或者你将谓词添加到将值约束为 IS NULL

如果您需要有关如何完成此操作的更多信息，我可以提供示例查询 .

回复于 2024-05-03T14:22:15+08:00

你说你不能改变架构，但我在这里开箱即用 . 您可以添加一个具有查看现有视图的新数据库数据库 . 例如：

use NewViewDb
GO

CREATE VIEW dbo.[T1T2View]
AS
SELECT field1, field2, COALESCE(field3, 'default value'), ...
FROM RealDb.dbo.Table1 t1 LEFT JOIN RealDb.dbo.Table2 t2 
ON t1.Id = t2.Id

GO

回复于 2024-05-03T14:22:15+08:00

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

SQL Fiddle

MS SQL Server 2008 Schema Setup ：

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)

Query 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 |

Query 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进行搜索 .

enter image description here

参考：Undocumented Query Plans: Equality Comparisons

回复于 2024-05-03T14:22:15+08:00

需要SARGABLE方法来过滤记录，并指定NULL的默认值

4 回答

相关问题