Improper use of logical operators has led to poor query performance

I have a table with huge amount of data (Nearly 15 million) and below structure.

create table test
(a int,--> /* There is a normal index on this column */
 b int,
)

There is a query selecting from this table and one of the conditions in the where clause is this :

where a!=1 or (a=1 and b!=0) /* The original condition */

The query was very slow and I thought the most of this poor performance might be because of the improper use of logical operators. I've changed the condition as you see below :

where not (a=1 and b=0) /* The edited version*/

and the performance changed dramatically ! What I need to be sure is that the two conditions are exactly the same so I don't miss any data. I was wondering If you could help me with this and tell me if you have better alternatives for the condition.

If you know any article regarding the correct use of logical operations and the way / order optimizer treat them , please share the link.

thanks in advance

In order to find out if two conditions are truly equivalent, you could try building the truth table for each of them and see if the two tables are identical.

Here is how you could go about building the truth tables. You have two variables, a, which may or may not be equal to 1, and b, which may or may not be equal to 0. Write and execute a query like this:

SELECT
  a
, b
, [a!=1 or (a=1 and b!=0)] = CASE WHEN a!=1 or (a=1 and b!=0) THEN 'True' ELSE 'False' END
, [not (a=1 and b=0)]      = CASE WHEN not (a=1 and b=0)      THEN 'True' ELSE 'False' END
FROM
  (
    VALUES
      (   1,    0)
    , (   1, 9999)
    , (9999,    0)
    , (9999, 9999)
  ) AS v (a, b)
;

For each variable, specify the value that the variable is compared against, so that a corresponding comparison is made either true or false (depending on whether it is = or !=), and another value that yields the opposite result. The value 9999 above is just an arbitrary value that stands for "not 1" when it comes to comparisons against a, and "not 0" when it's about b. (I went with something completely different than 1 or 0 so as not to make the resulting table too confusing.)

The above query will return the following output:

a
b
a!=1 or (a=1 and b!=0)
not (a=1 and b=0)

1
0
False
False

1
9999
True
True

9999
0
True
True

9999
9999
True
True

As you can see, both expressions give identical results for identical input values.

Note, however, that the above table only contains values that make the comparisons evaluate to either True or False. This is how things normally are in Boolean algebra. However, in the SQL world a boolean expression can evaluate to a third state, Unknown aka Null. If a is nullable and is indeed a null, then a=1 (or a!=1 for that matter) will evaluate to Unknown/Null. If nullability needs to be accounted for, then our truth tables should include nulls as input values.

Here is a modified version of the above script that includes nulls for both variables:

SELECT
  a
, b
, [a!=1 or (a=1 and b!=0)] = CASE
                               WHEN      a!=1 or (a=1 and b!=0)  THEN 'True'
                               WHEN NOT (a!=1 or (a=1 and b!=0)) THEN 'False'
                               ELSE 'Unknown'
                             END
, [not (a=1 and b=0)]      = CASE
                               WHEN      not (a=1 and b=0)       THEN 'True'
                               WHEN NOT (not (a=1 and b=0)     ) THEN 'False'
                               ELSE 'Unknown'
                             END
FROM
  (
    VALUES
      (   1,    0)
    , (   1, 9999)
    , (   1, NULL)
    , (9999,    0)
    , (9999, 9999)
    , (9999, NULL)
    , (NULL,    0)
    , (NULL, 9999)
    , (NULL, NULL)
  ) AS v (a, b)
;

And it gives the following output:

a
b
a!=1 or (a=1 and b!=0)
not (a=1 and b=0)

1
0
False
False

1
9999
True
True

1
null
Unknown
Unknown

9999
0
True
True

9999
9999
True
True

9999
null
True
True

null
0
Unknown
Unknown

null
9999
Unknown
True

null
null
Unknown
Unknown

Highlighted above is the one scenario where the two conditions do not yield the same results, which is when a is null and b is a non-null value that is not 0. In that case the first condition's result is unknown while the other one's is true.

Again, this is assuming that a can be null, and under that assumption your two logical expressions are not equivalent. But if, for example, only b can be null and a cannot, then you can see from the above output that the results in corresponding rows are identical.

You will find your answer, therefore, based on the nullability of the variables involved.

A couple of links for more reading:

• Truth table

• Three-valued logic

StackExchange Database Administrators Q#301557, answer score: 24