It is important to optimize your queries and reports to maximize your system's performance and maintain its availability to users. Creating efficient queries and reports is key to avoiding timeout errors in your database system.
While not all of these tips will apply to every query or report, keeping them in mind as you build can help make sure you build efficiently:
- 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.
- 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.
- 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.
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. For example: reports time out after five minutes, tracking queries time out after 15 minutes, and other queries time out after 30 minutes.
- Reports: time out after 5 minutes.
- Tracking queries: time out after 15 minutes. (A tracking query is a query with an execution mode of "Retrieve all records and save recent result history" or "Retrieve only the new records since query was last run.")
- Other queries: time out after 30 minutes.
Improving the efficiency of your query or report can help ensure it will run within the allotted timeout period.