sqlsql-servert-sqldemorgans-law

De Morgan to avoid ORs in SQL Server query

So from what I know OR's in a query can make it difficult to create a performant query plan using indices. Is it at all useful to use De Morgan's laws to turn WHERE (X OR Y) into WHERE NOT (NOT X AND NOT Y)?

If using De Morgan's laws is useful in this case, is it still useful if the actual WHERE clause I'm interested in is

SELECT * FROM Table1
WHERE [...other conditions...]
AND NOT (X NOT IN (v1,v2,v3,...) AND Y NOT IN (u1,u2,u3,...)
[...other conditions...]

Where the values in the NOT IN clauses change according to the query parameters (the query is generated dynamically).

I have an index: CREATE NONCLUSTERED INDEX IX_X_Y ON Table1 (X,Y)

Solution

  • I am not sure if "avoiding" ORs by using De Morgan will help. In the past I have tried to do so as well, but if I remember correctly, I have not noticed any performance improvements.

    However, if performance is a serious bottleneck here, you could consider using UNION or UNION ALL. Not the nicest solution, but it might be effective.

    The query

    SELECT * FROM Table1
WHERE (ConditionX) OR (ConditionY)

    would become something like

    SELECT * FROM Table1
WHERE (ConditionX)
UNION
SELECT * FROM Table1
WHERE (ConditionY)

    However, beware that such a solution using UNION or UNION ALL might behave differently if your original query would return duplicate rows. (But I assume that you also try to avoid duplicate rows in the result set.)

    Also note that X IN (A, B, C) might be interpreted internally as an OR as well: (X = A OR X = B OR X = C). I am not sure if this will have any effects on performance as well. (I simply assume it does, but I am not sure.)

    You could also consider using separate indexes for X and Y.

    Regarding all these suggestions, I would strongly consider Dale K's and Tim Biegeleisen's advice to investigate query performance by looking at query execution plans to see if indexes will be used or not and what their effects will be.

    Tip: If you perform query optimizations by completely changing a query's structure, don't forget to include some comments to explain to colleagues and your future self why you chose to do so. For documentation purposes, you might also want keep your original query in comments as well. ;)