I've been struggling to understand how to deal with a specific type of performance issue that shows up very often in this scenario: when you are wanting to apply multiple filters in a query, but you know that first filter will return a very small number of rows from a very large table.
For example, we have a 3rd-party heterogeneous table with 10M+ rows where the columns mean different things based on the "object type" in
TYPEID. Here is an example query:
SELECT ID, NAME, INT109 FROM DATA WHERE TYPEID = 8301514 AND INT109 = 1
In this query, there are no covering indexes for the two filters, but there is an index on the 'TYPEID' column. What is perplexing is that even though there are only about 500 rows out of the 10M in the table with
TYPEID = 8301514, this query sometimes takes many seconds to run.
If I simply remove the
INT109 = 1 filter at the end, the query runs almost instantly:
SELECT ID, NAME, INT109 FROM DATA WHERE TYPEID = 8301514
It makes no sense to me that having fewer filters would make a query run much faster. Also the behavior seems to be inconsistent - the first query can run really fast too if it has been run multiple times, like something is being cached. It's difficult to do reliable experiments (this is in SQL Azure). Is this normal behavior? Is this something that can be caused by a bad execution plan (even though I'm not using parameters) or statistics that are out of date?