ISNULL vs " IS NULL and field = 0"

Question

我有一个使用以下条件联接表的查询：

(Apps.mfrId = Manufacturer.id OR Apps.mfrId IS NULL OR Apps.mfrId = 0)

查询运行需要17秒，使用探查器查询会导致5-30条消息(每次运行都会有所不同)，并出现错误"错误：1222，严重性：16，状态：18"。

我保留了完全相同的查询，但我将上述条件更改为：

(Apps.mfrId = Manufacturer.id OR ISNULL(apps.mfrId, 0) = 0)

现在，与此更改相同的查询在140ms内运行，并且没有锁定错误。

为什么会发生这种情况？

请注意，在对表Manufacturer和Apps进行测试之前，我已经使用repair_bebuild选项运行了DBCC CHECKTABLE，并在这两个表上重新构建了索引。

还要注意，没有其他查询同时针对数据库运行。

这是一个有错误的查询的简化版本：

select top 2000 Apps.object_id
from 
Manufacturer
INNER JOIN Apps ON (
Apps.mfrId = Manufacturer.Id
OR Apps.mfrId IS NULL
OR Apps.mfrId = 0 
) 
where
Apps.OBJECT_ID = 6879149

如果使用"top 1000"而不是"top 2000"，则查询将在100毫秒以上完成。

Answer 1

如果Apps.mfrId为空，则表示您加入了每个制造商。

根据您的测试，"OR ISNULL(apps.mfrId，0)=0"比"OR apps.mfrId为NULL或apps.mfrId=0"便宜

这些看起来确实相当等效，而且你已经有了解决方案，所以问题是为什么和/或如何让非执行者工作。

当谈到这样的性能时，如果您所说的一切都是正确的，当我们开始问为什么时，我们开始指向查询优化器。如果对相同的参数执行不同的操作，则会有不同的查询计划。您可能会看到一个表扫描而不是索引使用，或者其他一些对糟糕性能的解释。

我鼓励你比较查询计划，或者找其他人来帮助你找到它的不同之处。但是，你已经可以看出它在做一些不同的事情。

一种可能性是，当Query Optimizer修复程序发布时，默认情况下不会打开它们，除非打开标志4199(用于所有修复程序，或用于特定修复程序的其他标志)。这是因为常规修复程序可能对大多数人都有好处，但也可能会破坏在现有怪癖环境中优化的应用程序。

https://dba.stackexchange.com/questions/102292/trace-flag-4199-enable-globally

打开4199有帮助吗？

select top 2000 Apps.object_id
from 
Manufacturer
INNER JOIN Apps ON (
Apps.mfrId = Manufacturer.Id
OR Apps.mfrId IS NULL
OR Apps.mfrId = 0 
) 
where
Apps.OBJECT_ID = 6879149
OPTION(QUERYTRACEON 4199)

另一个相关主题是参数探查。有时，一个查询计划可能会被缓存，这对一个参数来说是最优的，但对另一个参数却很糟糕。在你的情况下，一个应用程序可能会返回一个制造商，但另一个应用可能会返回所有制造商，这就是为什么这一点值得一提。当给定不同的参数时，这通常表现为同一代码的不一致性表现不佳。您可以尝试关闭参数探查或强制重新编译，以帮助诊断这是否是问题的一部分。

OPTION(未知优化)和OPTION(可重构)之间的主要区别是什么？

我看到过这样的情况，if觉得查询优化器放弃了仔细阅读索引，只是因为语句变得太复杂了。但在你的例子中，这似乎不太可能；除了查询优化器错误的主题得到了修复，但要利用它们，您仍然必须打开查询标志，否则您安装的补丁可能什么都不做。

有时，您还可以尝试帮助指导sql优化器。如果有一个索引应该始终使用，那么它可以作为查询提示给出。

我也很想知道以下是否消除了问题：

select top 2000 Apps.object_id
from
(
select Apps.object_id, Apps.mfrId 
from Apps
where Apps.OBJECT_ID = 6879149
) Apps
left join Manufacturer  ON (
Apps.mfrId = Manufacturer.Id
OR Apps.mfrId IS NULL
OR Apps.mfrId = 0 )

如果所有的查询优化器修复程序都已打开，并且问题仍然存在，我们必须询问查询优化器为什么要这样做。它只能使用现有的索引，并且只能在统计数据存在的情况下计算使用索引是否有益。与此同时，过时的统计数据将导致糟糕的选择。定期重建/重新组织索引和更新统计数据可能会很好。你可以试着这样做，看看它是否有任何影响。

结论

既然你已经有了一个有效的解决方案，那就一定要使用它。但你的问题是，为什么两个非常相似的事情会产生非常不同的结果。假设使用了相同的参数，并且考虑到您的两个选项非常相似，则问题指向查询优化器做出了错误的选择。这表明您可能需要同时安装最新的修补程序(如果未安装)，还需要启用4199才能真正启用您已经通过sql server修补程序安装的所有查询优化器修复程序。这包括将OPTION(QUERYTRACEON 4199)添加到sql的底部，或全局启用4199，或类似操作。