7

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,
<other columns>)

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

2
  • 5
    Can either a or b be NULL? Oct 24 at 2:27
  • OR prevents the database from using indexes. You should avoid the OR clause entirely if you can help it. J.D.'s answer is one way to handle it. Your refactor is another way assuming a is not null. And to your question, optimizer does not have "order of operations" for choosing indexes.
    – DKATyler
    Oct 26 at 16:32
24

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:

8
  • 2
    @Pantea for a!=1, if a is Null then this becomes Null!=1 is Null. Likewise for (a=1 and b!=0) if a is Null then this becomes (Null=1 and b!=0) which reduces to (Null and b!=0) which can only be False or Null. Combining we get Null or (Null and b!=0). Null or True is True, but Null or {False, Null} is Null. Since we already know that (Null and b!=0) can only be False or Null, then we have Null or {False, Null}. In both cases the result will be Null. Oct 24 at 15:49
  • 2
    (The base rules I am using are Null and {T,F,N} ==> {N,F,N} and Null or {T,F,N} ==> {T,N,N}) Oct 24 at 15:55
  • 1
    @Pantea: What RBarryYoung said. I've also added some helpful (I hope) links for more information.
    – Andriy M
    Oct 24 at 19:40
  • 1
    @Pantea: Not off the top of my head, sorry. I've seen it mentioned often that you should try and avoid using OR in conditions, and also that "not equals" conditions are typically not very efficient (though sometimes you just have to use those, so...). If I needed a reminder why those are bad or what other issues you need to be aware of regarding conditions in SQL, I'd just resort to searching by keywords. Haven't had to deal with those lately so don't have any links handy, sorry again.
    – Andriy M
    Oct 24 at 23:34
  • 1
    @Pantea: Personally I don't think so. Transact-SQL is a hybrid language combining the procedural features of control operators like IF or WHILE with the declarative nature of SQL (the language). Where conditions are used in procedural elements, short-circuiting might occur. (I've never checked.) But in statements like SELECT, which are purely declarative statements, short-circuiting doesn't make much sense, because SQL Server needs to consider all the predicates in order to find the best plan to execute the query. It might find short-circuiting applicable, or it might not.
    – Andriy M
    Oct 25 at 19:06
4

The condition not (a=1 and b=0) is equal to a!=1 or b!=0 which of course not equal to a!=0 or (a=1 and b!=0)

For instance:

a = 0
b = 0

a!=0 or (a=1 and b!=0) => false or (false and false) => false

not (a=1 and b=0) => not (false and true) => true
1
  • I'm so so sorry , I did a mistake and I've just edited my post . The original condition is this "where a!=1 or (a=1 and b!=0)".
    – Pantea
    Oct 23 at 17:49
3

Unfortunately I don't think your rewrite is logically equivalent. In your first predicate you have where a != 0 or ... which means any records where a does not equal 0 will be returned. That would include records where a = 1 and b = 0 (this is specifically due to using an OR clause). Your second re-written predicate where not (a = 1 and b = 0) would exclude that same case.

But you should be able to test this by comparing the row counts of both predicates, to verify. If needed, you can create a small temp table with each combination of values you're testing for (e.g. (a,b) = {(0,0), (0,1), (1,0), (1,1)}), and then apply each predicate to see the outcome.

One hint I can offer up is sometimes OR clauses can be re-written more efficiently as a UNION between the two sides of the clause in a separate query. For example:

SELECT ...
FROM test
WHERE a!=0 

UNION

SELECT ...
FROM test
WHERE a=1 and b!=0

Depending on your indexes and your predicates, that may allow for an index to be seeked on efficiently. Though I'm not sure if that would help in your case when using an inequality operator like !=.

But outside of that, for performance improvement suggestions, we'd probably need to see your execution plan.

0

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.