Expression with extreme duration

[Q&A] General
1

We’re using an expression in a workflow and use the result in a loop. The expression that gives the right results takes more than 10 min to execute. The expected amount of records is ca. 10K.

Required expression

This expression takes 6768ms in the expression debugger (with a TOP 100)

persons,
this,
boolean:and([
  int:count({
   compliancychecks,
   1,
   boolean:and([contact.equals(parent.inherits_from_relation()),date_executed.equals(date:now())])
  }).equals(0),
  boolean:or([
   this.in({
    systemversions,
    persons:if(
     this.owner.contact_type().equals(contacttype:load(0)),
     this.owner_contact_person.connection_to.person(),
     this.owner.person()
    ),
    boolean:and([
     this.is_current_version.equals(yesno:load(1)),
     this.product.is_policy().equals(yesno:load(1)),
     boolean:not(this.is_ended_or_suspended())
    ])
   }),
   this.in({
    connectioncontacts,
    this.connection_to.person(),
    boolean:and([
     this.connection_from.has_active_insurances().equals(yesno:load(1)),
     this.connection_to.contact_type().equals(contacttype:load(1))
    ])
   })
  ])
])
}

The bottleneck

I’ve found the bottleneck in the expression above. When I remove the int:count() in the first boolean:and(), the expression takes 36ms in the expression debugger (also with a TOP 100). That is almost 200x faster!

This is the expression:

{
persons,
this,
boolean:and([
  boolean:or([
   this.in({
    systemversions,
    persons:if(
     this.owner.contact_type().equals(contacttype:load(0)),
     this.owner_contact_person.connection_to.person(),
     this.owner.person()
    ),
    boolean:and([
     this.is_current_version.equals(yesno:load(1)),
     this.product.is_policy().equals(yesno:load(1)),
     boolean:not(this.is_ended_or_suspended())
    ])
   }),
   this.in({
    connectioncontacts,
    this.connection_to.person(),
    boolean:and([
     this.connection_from.has_active_insurances().equals(yesno:load(1)),
     this.connection_to.contact_type().equals(contacttype:load(1))
    ])
   })
  ])
])
}

But, the int:count() in the first expression is necessary.

We’ve validated correct indices on the compliancychecks table, eg. contact and date_executed as included columns. I assume it has something to do with the date check.

Who is the genius that can help us in the right direction?

2

In a workflow or process I’d let the first step be a cache of a list I want to iterate about. I wouldn’t want this to be dynamic, because I can then not assure a loop to end.

So first step: cache your list of persons (or in workflows, cache the ids of the persons). THen the rest of the workflow and loop will just be “keep a record of the last person processed. (initially will be NULL) and then cache the next person that has a bigger id than the previous one, that is in the list of to be processed records; ordered by id asc.”

Then the first step may take 7 seconds but is only called once. All other calls are to a list of integers to find out the next one.

Also I think it might be useful to check your or expression that to me seems a bit too wide.
In the count part you have a lot of references to the contact of the compliancychecks table. Make sure you have an index set on that field.

3

Hi Helena,

Thanks for your suggestions. A couple of remarks:

  1. We already use the workflow method you suggest (with only one call)
  2. The first step doesn’t take 7 secs, but 10 minutes (!)
  3. As I mentioned in my initial post, all possible indices are present

We can’t run the query for 10 minutes, since our server is clipping in CPU and blocks other queries and calls.

When I transform the expression to SQL, I assume it has something to do with the cast on the date. Do you have any thoughts on that topic? (because without this check the query runs 200x faster).

      NOT(
        EXISTS (
          SELECT
            *
          FROM
            [compliancychecks] AS q1t1 WITH (READUNCOMMITTED)
          WHERE
            (
              (
                (
                  q1t1.[contact] = q0t1.[id]
                  AND q1t1.[contact] IS NOT NULL
                  AND q0t1.[id] IS NOT NULL
                )
                AND (
                  q1t1.[date_executed] = CAST('2025-06-19 00:00:00' AS DATETIME)
                  AND q1t1.[date_executed] IS NOT NULL
                )
              )
            )
        )
      )
4

Ik denk dat het execution plan hier veel verschil maakt. Soms is dat een handje te helpen door de volgorde binnen je and() een handje te helpen.

{persons,
this,
boolean:and([

boolean:or([
this.in({
systemversions,
persons:if(
this.owner.contact_type().equals(contacttype:load(0)),
this.owner_contact_person.connection_to.person(),
this.owner.person()
),
boolean:and([
this.is_current_version.equals(yesno:load(1)),
this.product.is_policy().equals(yesno:load(1)),
boolean:not(this.is_ended_or_suspended())
])
}),
this.in({
connectioncontacts,
this.connection_to.person(),
boolean:and([
this.connection_from.has_active_insurances().equals(yesno:load(1)),
this.connection_to.contact_type().equals(contacttype:load(1))
])
})
]), int:count({
compliancychecks,
1,
boolean:and([contact.equals(parent.inherits_from_relation()),date_executed.equals(date:now())])
}).equals(0)
])
}

Of zelfs eerst de and en dan de or gaat een stuk sneller dan (en in dit geval ook op jullie testomgeving)