Queries are a powerful tool that allow you to retrieve, filter, and transform your data. In order to harness this power effectively, these best practices will help you create robust processes and avoid timeout errors.

Consider filter selections

In a prompt filter, selecting a large number of prompts can slow performance. When deciding between using 'IN' vs 'NOT IN', consider the number of prompts that will need to be selected.

For example, if we’re filtering for records who live outside the US, it’s more efficient to use a ‘NOT IN’ filter and select the United States, rather than using ‘IN’ and selecting every single other country in the list.

Avoid multiple OR statements

OR statements increase the number of permutations that Slate needs to evaluate. In many cases, these can be replaced with ‘Exists’/’Not Exists’ subqueries and/or ‘IN/NOT IN’ filters.

When multiple subquery filters are linked together with OR statements, it’s often a sign that the logic can be simplified. In this case, a single subquery filter can be used to check for the existence of either interaction code.

Avoid unnecessary and inefficient joins

Joins are a powerful way to retrieve or filter on data outside the base of your query. However, every join adds complexity to your query. We recommend removing unused joins, consolidating one-to-one joins, and using the most efficient join possible in every situation.

Remove unused joins

If a base join is not used in any exports or filters, remove it.

Use “by rank” base joins whenever possible

When joining to another table at the base of your query, “by rank” joins (also known as direct joins) are more efficient than subquery joins. If a “by rank” join can achieve the intended logic, use it instead.

Make one-to-one joins at the base

If a join is one-to-one, always make it at the base of the query, not inside of a subquery export or filter.

Avoid unnecessary subqueries

Every subquery adds complexity to the query. Subqueries shouldn’t be used to hide logic or simplify formulas. Not only should you avoid hiding complexity for the sake of maintenance and clarity, it also makes your query less efficient.

Don’t hide logic in subquery filters

It may be tempting to use a subquery filter as a “container” to encapsulate complex logic. Subquery filters that only contain direct filters are unnecessary and inefficient. Using parentheses makes your query clearer to understand at a glance, and allows it to run more efficiently.

Avoid nested subqueries where possible

Nested subqueries (subqueries inside of other subqueries) can be a powerful tool, but they also can add some unnecessary complexity to the query. Don’t hide parts of a formula inside a subquery export, and in general, consider whether your subquery logic can be achieved in a single subquery level.

Use subquery outputs other than Formula

Subquery formulas are powerful, but they can add unnecessary complexity and make your query harder to understand for less technical users. When possible, use subquery outputs like Existence, Concatenate, and Coalesce.

Existence

Concatenate

Coalesce

Try not to search free-text fields

Using formulas or free-text searches such as "Value Contains" in a filter can slow down query performance, especially on large tables. This is because formulas and the 'LIKE' operator used in free-text searches require the database to evaluate each record individually.

If you find yourself using free-text search on a specific field, consider whether your process can be changed to use a prompt-driven field instead.

Try not to filter against huge tables

Some tables in Slate may be much larger than others. Consider some of the one-to-many relationships in your database, or the many rows of data associated with communications.

Examples of some of these very large tables include:

• Address

• Device

• Test

• Message

• Message Track

• Ping

• Source

If your query runs slowly because it’s attempting to access these data points, try inactivating the associated exports or filters and see if it reduces run time.

Limit exports and filters with prep clauses

In Slate, filters and exports sometimes use prep clauses. Prep clause are created behind the scenes when creating or editing specific exports and filters, such as a Geographic Proximity. This happens because geographic proximity exports and filters require additional preparations to function. These preparations are necessary to calculate proximity correctly and requires extra processing power.

Limit sorting

Applying sorts to complex queries can increase run time. Limiting the number of sorts, or removing any additional format masks that may have been applied to sort exports, can improve efficiency. Some types of queries, like data integration queries, generally do not need to be sorted.

Break large queries into several smaller queries

If a query is becoming large and complex, it may be helpful to break it into several smaller queries. Not only can this help make each query more clear and understandable, it also gives each individual query its own timeout limit, increasing the overall time Slate has to process the data.

Consider your process

If a query you’re building is becoming overly complex, take a moment to consider your process. Query complexity can be a sign that another Slate feature can be used to simplify your process. For example, populations centralize the logic for a specific group of records, allowing you to query on the population without needing to worry about the specific logic that applies to those records.