Slate is a transactional database that uses industry-standard timeouts. Timeouts are limits placed on processes that must be completed in a specific period of time. If a process takes too long and is hogging resources, it will be automatically stopped to prevent the entire system from crashing. Different processes have different timeout limits, depending on their expected duration.

• Reports: time out after 5 minutes.

• Tracking Queries: time out after 15 minutes.

• Portal queries: time out after 30 seconds.

• Rule execution: times out after 1 minute.

• Other queries: time out after 30 minutes.

It is important to optimize your queries and reports to maximize efficiency and ensure they complete within the allotted time limit.

Avoiding Timeout Errors

Creating efficient queries and reports is key to avoiding timeout errors in your database system. While avoiding timeout errors is not always guaranteed, following these tips can optimize your system's performance and maintain its availability to users.

Learn More

These tips are a summary of the concepts covered in the Query Building Best Practices article, which has more information and visual examples of these ideas.

• Number of records: The number of records returned in the report or query will certainly impact the execution duration. Certain tables such as Source, Ping, Message, and Form Response are particularly large, often containing hundreds of thousands of rows. Any report or query referencing these tables will take longer to execute.

• Additional tables: Generally, the more tables you join to, the slower the query or report will render.

• Boolean operators: "OR" and "NOT" operators are less efficient in SQL Server and should be avoided. Filtering in the affirmative is generally more efficient. (An exception: using a "Not Exists" subquery filter is usually more efficient than the "NOT" operator.) 

• Prompts selected: In a prompt-driven filter, selecting an extremely large number of prompts can bloat the underlying SQL. To exclude one or two prompts, use "NOT IN" instead. 

• Duplicate joins: Make one-to-one joins at the base of the query. Don't make the same join multiple times in multiple separate subqueries.

• Too many subqueries: While subqueries are extremely powerful, they also have an efficiency cost. Reduce the number of subqueries, especially nested subqueries. 

• Formulas: Formulas are computationally expensive and will take longer to resolve. Do not use nested subqueries to make your formula easier to read.

• Break up large queries and reports: Some extremely large queries and reports can still time out, even if they're built efficiently. Breaking them into smaller queries/reports can help each one run faster.

• Try changing your process: For example, if an event query or report is timing out, you can reduce the number of event templates you use or move data when possible to a system field on the record rather than storing it on the form response table.

Query-Specific Tips

• Execution mode: As mentioned above, the timeout period of a tracking query is half of a regular query. Consider whether you need to save the results of each query run, especially if your query is already exporting a file.

• Sorts: Each sort adds complexity to your query, so consider whether they're needed. There is almost never a reason to sort the results of a query used in a data integration.

Report-Specific Tips

• High-level filters: Establish high-level part filters (the main Data/Charts level). For example, restrict the date range or use Matched Records Only filter for Ping data (which includes UTM).

• Replication: Sometimes, replicating high-level filters on the columns and rows can improve efficiency.