If you notice a drop in performance as your database grows in record volume and complexity, consider trimming and honing existing rules. Follow these best practices for writing efficient rules.
📖 Related reading: Troubleshooting Tips for Database Health
Best practices for rule-building
When troubleshooting rule efficiency, keep an eye out for these common issues:
OR: Avoid OR statements. Instead, opt for multiple simple rules.
NOT: Write rules in the affirmative. A void using NOT statements whenever possible.
Character Search: Avoid filters that must perform a character search . For example, School In List, Last Name Contains.
CEEB: Instead of filtering on School In List (CEEB), try the more efficient geomarket.
Expensive Tables: Source Format, Message, Message Track, Ping: Avoid filtering on Source Format, Message, Message Track, or Ping Data . These data tables will grow almost exponentially over the life of your database, and filters searching across those tables will see a marked decrease in performance over time. We often call these “computationally expensive” tables as they take a large amount of resources to query upon.
Do Nothing: By incorporating Do Nothing rules into Exclusivity Groups, you can prevent unnecessary records from being evaluated by your rules whenever possible. If the "Do Nothing" criteria would require many OR statements to define, this might be replaced by a set of streamlined Do Nothing rules preceding the rest of the action rules in this group.
Daisy Chain: Do not daisy chain rules. A rule cannot rely on the result of another rule.
Outdated Filters: Check for any outdated or no longer necessary filters. If a rule is filtering by date, it may no longer be necessary as no one could reasonably meet that criteria, and thus the rule should be inactivated. If you have not already, update your rules to use Configurable Joins query bases.
Preview Status: If you’re not actively using or testing a rule, be sure to set its status to Inactive. Rules left in Preview status will still execute queries to identify matching records, though they won’t apply any actions to those records.
Measuring an individual rule's speed
When a rule is opened, it will automatically begin to load matching rows. The Matching Rows number represents the records that currently qualify for that rule’s action:
If a number of rows doesn’t appear for several seconds, or even minutes, this is a strong sign that the filters in the rule reflect one or many of the issues above. Occasionally, a red error message will appear next to Matching Rows, which indicated that 5 minutes have passed and the query was still not able to calculate results. A rule that cannot load matching rows should be broken down into multiple rules, simplified, or even inactivated if you are experiencing a Rule Backup.
If you are interested in reviewing rules that may be inefficient, you can also navigate to the right side menu of the Rules overview page and click Check Rules:
Check Rules lists all rules in your database and runs through the logic. The Count column displays the number of matching rows, and the Duration column displays how much time (in seconds) it took to calculate matching rows.
In the interest of efficiency, Check Rules does not attempt to load a count after 60 seconds, and will display ‘ERR’ in the Count column when that threshold is reached. This does not necessarily mean that the rule is currently erroring, but its filter criteria should still be reviewed and broken down. If the count does return a number but the duration is displayed in orange or red, this means that it took longer than 15 or 30 seconds respectively. These rules should also be reviewed periodically and, in the unlikely event of a rule backup, they may need to temporarily inactivated to reduce strain.