SQL Server performance issues when using EXCEPT in query

Question

In general, I have a SELECT query combined with three independent SELECT queries. I am using EXCEPT and UNION operators in the query. When executing the queries independently, I'll receive the results in 1-2 seconds, but when having EXCEPT operator, the query will take hours.

The query structure (as simplified) is as follows:

SELECT DISTINCT FIELD_1, FIELD_2, FIELD_3 FROM MYTABLE

EXCEPT

(
SELECT DISTINCT FIELD_1, FIELD_2, FIELD_3 FROM MYTABLE WHERE XXX

UNION

SELECT DISTINCT FIELD_1, FIELD_2, FIELD_3 FROM MYTABLE WHERE YYY
)

Are there any ways to speed up the whole query or is the EXCEPT operator in general so slow that it should be avoided?

Answer 1

You can do this with GROUP BY

SELECT FIELD_1, FIELD_2, FIELD_3 
FROM MYTABLE
GROUP BY FIELD_1, FIELD_2, FIELD_3 
HAVING MAX(CASE WHEN (XXX) OR (YYY) THEN 1 ELSE 0 END) = 0