为什么 UNION 比 OR 语句快 [英] Why is UNION faster than an OR statement

问题描述

我有一个问题，我需要查找具有与值匹配的度量值，或根本没有该度量值的记录.我用三种或四种不同的方法解决了这个问题，使用 JOINs，使用 NOT IN 和使用 NOT EXISTS.但是，查询最终每次都非常慢.然后我尝试将查询一分为二，它们都运行得非常快(三秒).但是使用 OR 组合查询需要五分钟以上的时间.

I have a problem where I need to find records that either have a measurement that matches a value, or do not have that measurement at all. I solved that problem with three or four different approaches, using JOINs, using NOT IN and using NOT EXISTS. However, the query ended up being extremely slow every time. I then tried splitting the query in two, and they both run very fast (three seconds). But combining the queries using OR takes more than five minutes.

阅读 SO 我尝试了 UNION，它非常快，但对于我正在使用的脚本来说非常不方便.

Reading on SO I tried UNION, which is very fast, but very inconvenient for the script I am using.

所以两个问题:

1. 为什么 UNION 快这么多?(或者为什么 OR 这么慢)?
2. 有什么方法可以强制 MSSQL 对 OR 语句使用不同的方法来实现速度更快?

1. Why is UNION so much faster? (Or why is OR so slow)?
2. Is there any way I can force MSSQL to use a different approach for the OR statement that is fast?

推荐答案

原因是在查询中使用 OR 通常会导致查询优化器放弃使用索引查找并恢复到扫描.如果您查看两个查询的执行计划，您很可能会看到使用 OR 的地方进行扫描，并寻找使用 UNION 的地方.在没有看到您的查询的情况下，您无法真正了解如何重构 OR 条件.但是您可能会发现将行插入到临时表中并连接到它可能会产生积极的结果.

The reason is that using OR in a query will often cause the Query Optimizer to abandon use of index seeks and revert to scans. If you look at the execution plans for your two queries, you'll most likely see scans where you are using the OR and seeks where you are using the UNION. Without seeing your query it's not really possible to give you any ideas on how you might be able to restructure the OR condition. But you may find that inserting the rows into a temporary table and joining on to it may yield a positive result.

此外，如果您想要所有结果，通常最好使用 UNION ALL 而不是 UNION，因为您消除了行匹配的成本.

Also, it is generally best to use UNION ALL rather than UNION if you want all results, as you remove the cost of row-matching.

这篇关于为什么 UNION 比 OR 语句快的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！