Bug report SQL Server

Question

Hi,
we have a select in our SQL Server 2014 which is very slow (16 sec) when we do "select xxx where A = C or B = D".
Exactly the same select becomes very fast (less than 1 sec) when we do "select xxx where A = C union select xxx where B = D"
wich of course gives the same results.
I think this must be a bug of SQL Server 2014. Am I right? Is this bug solved in SQL Server 2019?

Thanks in advance,
Pedro

Answer 1

No, as long as the result is correct is not a bug. A person I know who has worked with the SQL Server optimizer likes to refer to things like this as "a limitation".

The optimizer is a very complex piece of software, but it is built on rules that it applies to transform queries (or rather query trees, an internal representation). While there are many rules, there are still many more possible rules and transformation that are missing.

And this is indeed on of them. I have more than once rewritten queries with OR conditions with disastrous performance to be UNION queries which have performed very well.

In defense of Microsoft, the OR-UNION rewrite is not likely to be trivial in the general case. That is, while you and I rewrite the queries and are happy with the result, it could be that they are not 100% equivalent. The deviations may be with edge cases we don't care about or we know that cannot happen. But SQL Server can of course make no such assumptions. It can only make transformations that it knows for sure does can never affect the result.

All this said, Microsoft makes changes to the optimizer in every release, and SQL 2019 comes with quite some news in what they call "intelligent query processing". I have not heard anything about handling OR queries better, but sometimes a new release offer surprises. So you may try it. Just don't hold your breath.

Answer 2

Hi,
please talk to the software development department of the next version of SQL Server to give more priority to this bug,
because I lost almost 200 hours of work solving the bad performance of the primary functionality of my site,
and my company almost broke because of this bug with all our clients angry about the slow performance of SQL Server!

Thanks in advance,
Eric

Answer 3

Hi anonymous userRamos-7104,

Using OR in a query will often cause the Query Optimizer to abandon use of index seeks and revert to scans.
https://stackoverflow.com/questions/15361972/why-is-union-faster-than-an-or-statement

https://bertwagner.com/2018/02/20/or-vs-union-all-is-one-better-for-performance/
https://sqlserverplanet.com/optimization/using-union-instead-of-or

---

If the answer is helpful, please click "Accept Answer" and upvote it.
Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.