Filter Best Practices
The time it takes to calculate a Filter depends on a number of variables -- the use of Joins, the complexity of any groups or nested groups, the use of another Filter, the type of mathematical operators used, and so forth. This topic provides some useful tips and best practices for how to design your Filter to run as quickly as possible.
Logic Sequence
The most important consideration when building an efficient Filter is how you sequence the Filter criteria. The system will always start with the top-most criteria on the Filter screen, and work its way down. Records that are selected in the first step get passed to the second step, and so on.
Therefore, you should structure your Filter in such a way as to quickly narrow down the amount of data you are searching. Whichever criteria will eliminate the most candidate records should be the first step in your Filter. In this manner, subsequent steps have fewer records to "sift" through, resulting in a more efficient Filter. The "Show Stats" option is very useful in identifying the number of records counted at each stage (see Calculate Counts for more details on this feature). This option can help you rearrange your steps in the most efficient manner.
Similarly, steps that take a longer time to process (such as Activities, for example), should be located toward the end of the Filter, after the set of potential candidate records has been narrowed down.
Grouping Layers
The Group command allows you to organize Filter criteria into logical groupings. Each time you utilize a group within another group, the system creates a "layer." Creating a Filter with many layers within layers could impact system performance. The system will warn you when you reach ten layers.
Nested Levels
Engage+'s SQL database imposes a hard limit of 32 "nested levels" when you're building a Filter. Nested levels are a database concept that describe how SQL queries can be nested inside each other. As you build more complex Filter query logic, the platform requires more nesting levels to execute the Filter query against the database.
In addition, when you assign a Filter to a Campaign, the platform's back-end processing requires the use of some of those 32 allowable nested levels. Therefore, you may run into a scenario where the Filter runs properly when you calculate Filter counts, but an error occurs when you actually deploy the Campaign with that Filter.
For more information, please see: Nested Levels in Filters.
Other Filters
Filters that utilize the result sets from OTHER Filters can take a long time to run, and should be avoided whenever possible. Including other Filters will also increase the number of nesting levels which could lead to exceeding the recommended limit of ten levels, and the system limit of 32 levels.
Calculated Fields
Using Calculated Fields instead of query logic is a good method for improving your Filter's efficiency, especially if you schedule the Calculated Fields to run overnight. The use of Calculated Fields will also reduce the number of nesting levels since you're querying off of a single field rather than using logic statements.
Table Joins
Whenever possible, you should try to use Existing Joins instead of Property Joins. Existing Joins are indexed and will run much faster as a result. If you do have to use a Property Join, note that joins on integer type fields run much faster than joins on string type fields.
Include vs. Exclude
Using "include" logic is faster than using "exclude" logic. Wherever possible, structure your Filter to use inclusion statements, rather than exclusions. If you do have to use exclusions, try to use them toward the end of the Filter, when the set of candidate records has been narrowed down.
Mathematical Operators
The different mathematical operators supported by the system can have an impact on Filter efficiency. The following chart lists the different operators, and their potential impact on large, complex Filters.
|
Operator |
Potential Impact |
|
Ends with |
High |
|
Less than or equal to |
Medium / high |
|
More than |
Medium / high |
|
More than or equal to |
Medium / high |
|
Less than |
Medium / high |
|
Contains |
Medium |
|
Starts with |
Medium |
|
Between |
Medium |
|
In |
Medium |
|
Equal to |
Low |
|
Not equal to |
Low |
|
Is not null |
Low |
Generally speaking, try to use the "High" impact operators toward the end of the Filter.