Efficiently written queries that are run manually have a window of five minutes to execute successfully before timing out. To prevent this, it's essential to keep the following strategies and considerations in mind when building a new query.
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.
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:
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).
Some tables may have tens of millions of rows of data, examples include:
- [source.index] (no longer available for use in queries or reports)
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.
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.
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.
Existence exports are created using subqueries when building Configurable Joins Queries.
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).
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.
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.
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.
Materialized views can sometimes provide a more efficient solution when a process does not require up to the second production data.
Use a rule to set a field value or assign a record to a population, rather than manage it via a query.