Performance core principles: 5. Predicates

  1. PREDICATES

What are the performance implications of how you create relationship predicates, how many you use and their order?

Ray Cologon’s Masterclass in London in 2014 introduced me to the idea that the order of predicates in a relationship matters because each line acts as a filter for what is passed to and processed by the next line.

Think about what is happening here:

You have daily records created one per day over the last twenty years ie 2001-2021 = 7300 records.

Alternative A:

  1. Show me records where year of creation is 2020
    then from those records
  2. Show me the records that were also created on a Monday?

Alternative B.

  1. Show me records created on a Monday then from those records
  2. Show me the records created in 2020?

In A the first line of the predicate gives us 365 records and the second reduces it to 52 records.

In B the first line of the predicate gives us 1042 records and the second reduces it to 52.

Ray explained that it is faster to filter out as many records as possible early on. By reducing the “interim found set” quickly you give the machine a smaller set to work on - which is faster.

It’s easy to build a simple model to test this and prove it to yourself. I did that in 2014. I have applied this idea ever since and have on some occasions seen an observable benefit when I changed the order of the predicates in a relationship.

I have used =, =<, and >= without noticing any particular difference in performance but I could be missing something there?

Cheers, Nick

2 Likes

I think most of the time one will go from bigger to smaller without thinking, like in your example in Alternative A. But sometimes the best way is not so obvious. Then it is good to be aware of this "rule".

Some discussions on Claris Community raised the question:

In this first one @erolst comes up with a killer test idea speed-of-case-function-vs-sequence-of-logical-operators

In this second one @PhilModJunk @coherentkris and several more explore key-order-in-relationships-does-it-matter-

1 Like