Filter in the Affirmative

Whenever possible, select filters that affirmatively specify the records' attributes that should be returned in the query results. It's almost always more efficient to use IN rather than NOT or NOT IN. Ultimately, filtering to include records instead of excluding records improves both the accuracy and transparency of your queries.

For example, instead of filtering for person records with a Citizenship Status NOT IN US Citizen, Permanent Resident, or Blank, filter for Citizenship Status IN Foreign National.

Avoid multiple OR statements

OR statements multiply the permutations necessary to evaluate. When the filter logic for a query involves multiple OR statements, the better approach is often to build several smaller queries instead.

Filters should be combined whenever possible: 

Rather than adding a configurable filter multiple times, with a different selection in each version:

Limit Searching of Free-Text Fields

Filters such as "Application Field Value Contains" are written to look at the field index, i.e., the field value's first 64 characters. Behind the scenes, this uses a LIKE operator in SQL, which can be resource-intensive and may involve searching against several thousand characters per row (You might search against several thousand characters per applicant which could end up being several hundred megabytes of data).

Limit Filters that Reference Large Tables

Some tables may have tens of millions of rows of data, examples include:

• [source.index]

• [address]

• [device]

• [test]

• [message]

• [ping]

• [form.response.field]

Depending on how the query has been written, referencing tables like these in your query can potentially reduce efficiency in query run times. Therefore, if you find that your query is running slow and is attempting to reach these data points, try inactivating the associated exports or filters to see if it improves the query's run time.

Use Literals to add Static Values

When each row in the query results should have the same static value in a given column, a Literal is a more efficient way of setting that value than using a formula or custom SQL snippet.

Use Existence exports rather than a formula with case statements

Existence exports can return a specific result (string) when a record does or does not meet a set of criteria. We highly recommend that you use these in place of a formula with a case statement whenever possible. Example Formula Case Statement:

CASE WHEN (@Race like '%Indian%') then 'Y'ELSE 'N' END.

This example includes an existence export that filters for Race IN American Indian. When the race is American Indian, display a Y; otherwise, show an N.

Existence exports can be incredibly helpful in improving query efficiency, as the server needs to check whether anything is there, rather than evaluating for a specific value.

Tip

Existence exports are created using subqueries when building Configurable Joins Queries.

Limit the Use of Filters/Exports that Require "Prep" Clauses (e.g., Geographic Proximity)

In Slate, filters, and exports sometimes leverage prep clauses that can be set up in the Prep Clause, which are automatically set up in a Prep Clause setting behind the scenes when creating/editing specific exports and filters such as a Geographic Proximity.

Geographic proximity exports and filters require some additional preparations behind the scenes of the query you are creating. These preparations are necessary to calculate proximity correctly, but as you can imagine, it requires extra processing power. If not configured appropriately, some of these types of filters or exports may cause a query slowdown (e.g., an Upcoming Interview Geographic Proximity filter in a Prospects and Applicants query base).

Break a Large Query into Several Smaller Queries

When pulling data from several different tables where a standard query base exists, it can be considerably more efficient to create separate queries rather than one large query.

Limit the Use of Custom Query Bases (e.g., Departmental Query Bases)

Departmental query bases are typically hardcoded to evaluate the currently logged-in user's roles and permissions. This involves some behind the scenes layering of the query that increases complexity. As always, something that is more complex can affect the run times.

To put it simply, you're running queries within queries within your query.

Limit the use of Sorts

Applying sorts to complex queries can increase the time it takes for the query to execute successfully. Limiting the number of Sorts, or removing any additional format masks that may have been applied to the Sort exports, can reduce latency.

Consider Materialized Views for processes

Materialized views can sometimes provide a more efficient solution when a process does not require up to the second production data.

Leverage Populations and Rules to Offload Computational Complexity

Use a rule to set a field value or assign a record to a population, rather than manage it via a query.