Optimize a query with unexpected results

by Unknown Coder   Last Updated May 15, 2019 21:06 PM

I don't post in this SE often, so if I'm not explaining the query properly please let me know and I will edit.

I have to obfuscate some of the work that I am doing so I will try to explain my issue in generic terms and hope that I can get some answers on what to investigate.

I have a large table with a variety of columns. But for the purposes of this question, let's just say it is mytable and has columns RecordID and Category

If I do:

SELECT * 
FROM mytable

This query will take a very, very long time to run as in multiple hours.

But if I do something like

SELECT * 
FROM mytable
WHERE Category=1

This query will run very quickly. In fact, assuming I only have 2 categories, I can do something like

SELECT * 
FROM mytable
WHERE Category=1
UNION ALL
SELECT * 
FROM mytable
WHERE Category=2

And this query will run in mere minutes and still contain all of the records that the first query should have pulled back (again, assuming only 2 categories).

It should be noted that the following query does not show improvement over the benchmark, its is only the UNION statement that fixes it.

SELECT *
FROM mytable
WHERE Category IN(1,2)

It's counter intuitive to me that one basic query can be slower than a UNION of two queries. Why is that? To me, this goes beyond the usual problems with indexing - if the indexing wasn't right, none of the queries presented would run well. So, I'm really at a loss as to what I should research.

Is this a symptom that sounds familiar to anyone and can point me in the right direction for further research?



Related Questions


Updated September 28, 2016 09:02 AM

Updated July 03, 2016 08:02 AM

Updated October 20, 2018 14:06 PM

Updated July 04, 2018 11:06 AM

Updated February 28, 2017 14:06 PM