Optimizing slow expression with and OR statement over different tables (terrible OR)

[Q&A] General
1

I have an expression that does not perform anymore.
The WHERE part of the expression contains an OR-statement over two different tables. This ons also has an IN lookup within each OR, making it even harder to perform.

How can this be optimized and how to avoid these kind of expressions?
The context of this expression is answers

boolean:or([
  this.in({
   productpropertyanswers,
   this.answer,
   boolean:and([
    this.property.productproperty.type.equals(fixedconfigurable:load(0)),
    this.property.product.equals(products:load(504574)),
    boolean:not(this.answer.isnull()),
    this.property.productproperty.in({
     productpropertygroupitems,
     this.property,
     boolean:and([
      this.group.equals(productpropertygroups:load(174)),
      boolean:not(this.property.isnull())
     ])
    })
   ])
  }),
  this.in({
   properties,
   this.answer,
   boolean:and([
    this.product.equals(products:load(504574)),
    this.productproperty.type.equals(fixedconfigurable:load(0)),
    boolean:not(this.answer.isnull()),
    this.productproperty.in({
     productpropertygroupitems,
     this.property,
     boolean:and([
      this.group.equals(productpropertygroups:load(174)),
      boolean:not(this.property.isnull())
     ])
    })
   ])
  })
 ])
2

Hello Redmer,

When we discussed this expression in person, we identified that this expression actually got evaluated real fast, and that it was another part in the expression (the fields, rather than the where), that caused the issue. That part did contain a “terrible OR” and was fixed.

However, this expression does contain some features of the “terrible OR” and “terrible IN”.

The “terrible OR” is an expression with an OR that needs more than 1 table to scan. This can often - but not always - lead to inefficient queries as SQL Server does not always see which indexes to use.

In these cases, the invididual parts of the OR are very efficient, use the index and only need a Index Seek for 1 or 2 rows; combined you get table scans which can be millions of rows.

Still, this expression does have a few parts that could trigger such a thing. In these cases, it often helps to non-normalize the database with Cached Functions. In this case, creating product() and productproperty() cached functions on ProductPropertyAnswers would be a way-to-go.

But in this case, that wasn’t the issue. If you can post the part that was the issue, I can help explaining the technique used.

boolean:or([
  this.in({
   productpropertyanswers,
   this.answer,
   boolean:and([
    this.productproperty().type.equals(fixedconfigurable:load(0)),
    this.product().equals(products:load(504574)),
    boolean:not(this.answer.isnull()),
    this.productproperty().in({
     productpropertygroupitems,
     this.property,
     boolean:and([
      this.group.equals(productpropertygroups:load(174)),
      boolean:not(this.property.isnull())
     ])
    })
   ])
  }),
  this.in({
   properties,
   this.answer,
   boolean:and([
    this.product.equals(products:load(504574)),
    this.productproperty.type.equals(fixedconfigurable:load(0)),
    boolean:not(this.answer.isnull()),
    this.productproperty.in({
     productpropertygroupitems,
     this.property,
     boolean:and([
      this.group.equals(productpropertygroups:load(174)),
      boolean:not(this.property.isnull())
     ])
    })
   ])
  })
 ])