Manually-run queries have a window of five minutes to execute before they time out.

Avoid time-outs with these query building strategies:

Filter in the affirmative

Select filters that tell Slate which records should be included, not excluded.

Why?

• More efficient: It takes Slate less time to find the small subset of records that are IN your filter criteria than it does to rule out every other record as NOT or NOT IN your filter criteria.

• Less ambiguous: Filtering in the affirmative is easier to understand. It improves your query’s accuracy and its readability to other users.

Example: Citizenship status

Rather than filtering for person records with a

• Citizenship Status NOT IN US Citizen, Permanent Resident, or Blank

Instead filter for

• Citizenship Status IN Foreign National

❌ Not recommended	✅ Recommended

Avoid multiple OR statements

If a query involves multiple OR statements, it’s better off as several smaller queries.

Why?

OR statements increase the number of permutations that Slate needs to evaluate.

Example: Interaction codes

✅ Combine filters whenever possible:

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

Try not to search free-text fields

Filters like Application Field Value Contains are written to look at the field index, which represents the field value's first 64 characters.

Why?

To query on a field index, Slate creates a SQL LIKE operator. These are resource-intensive, searching against several thousand characters per row.

This might end up searching against several thousand characters per applicant, which might then be several hundred megabytes of data.

Try not to filter against huge tables

As a Relational Database, some Slate tables may have tens of millions of rows of data.

Examples of some of these very large tables include:

• [source.index]

• [address]

• [device]

• [test]

• [message]

• [ping]

• [form.response.field]

Why?

Referencing tables like these increases your query’s run time.

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.

📖 Further reading

How Slate works under the hood: Database Structure & Determination of Table Ranks

Use literals to add static values

When a given export should produce the same static value in each row, you can use a literal to set that value with minimal computational cost.

📖 Further reading

Literals

Use existence exports over formulas with CASE statements

Existence exports are a type of output you can set in a subquery.

return a specific result (a 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.

Why?

Existence exports make queries more efficient: the server only needs to check whether a value is present, rather than doing the work of evaluating for a specific value.

Example: Race IN American Indian or Alaska Native

Here, we’re looking for a yes or no answer to whether a record has its Race field set to American Indian or Alaska Native.

❌ Example of a 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 string Y

• otherwise, display string N.

✅ Example of an existence output in a subquery export:

Here, we create a subquery export called “Is American Indian?”

• This export has an output of Existence.

• The Value If Exists is Y.

• The Value If Not Exists is N.

• The existence we are looking for is found with the filter Race IN American Indian or Alaska Native.

Limit filters and exports that need 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.

Why?

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.

If not configured appropriately, some of these types of filters or exports may cause a query slowdown. For example, an Upcoming Interview Geographic Proximity filter in a Prospects and Applicants query base.

Break large queries 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 custom query bases

Some institutions have departmental query bases that are hard-coded to evaluate the currently logged-in user's roles and permissions.

This involves some behind the scenes layering of the query that increases complexity.

Why?

Naturally, something that’s more complex can take longer to complete.

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

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 reduce latency (run time).

Use materialized views

A materialized view is like a scheduled data export that stores its output in your database, rather than a third-party location.

They are a static, point-in-time snapshot of a result-set, and are a more efficient solution when a process doesn’t need up-to-the-minute production data.

📖 Further reading

Materialized views

Use populations and roles

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

📖 Further reading

• Populations

• Rules & Automation