Slate is a transactional database designed for operational efficiency. Timeouts (which are an important part of this configuration) allow essential processes to execute promptly, rather than waiting on reports or queries to generate. Reports must load within 5 minutes or they will timeout. The timeout for queries is 30 minutes and 15 minutes for tracking queries.
Every query and report establishes an execution plan in SQL Server and every aspect of the query will impact the efficiency of the execution plan which in turn will affect how long it takes to execute. Each Report is essentially 3 queries on the back end, so this issue is even more critically important in Reports.
To maximize efficiency and avoid timeouts on new queries:
- Query Results - The number of records referenced in the report or query will certainly have an impact on efficiency and certain tables such as Ping and Message and Form Response are particularly large, often containing hundreds of thousand of rows.
- Boolean Operators - Or statements and NOT-ins are less efficient in SQL Server and should be avoided whenever possible.
- Additional Tables - The more tables you join to in general, the slower the query or report will render.
- Formulas - Formulas are also computationally expensive and will take longer to resolve.
To improve efficiency on existing queries:
- Filtering - Filter in the affirmative to avoid Not-ins whenever possible and reduce OR statements.
- High-Level Filters - Establish high-level filters at the query base (Data/Chart) level in a Report. For example restrict the date range or use Matched Records Only filter for Ping data (which includes UTM).
- Replication - Try replicating high level filters from the base (Data/Chart) level in a Report on the columns and rows. Since a Report is essentially 3 queries, this has been known to improve efficiency.
- Break up Reports - Reports render by data part; therefore, breaking out the different sections of a Report into distinct data parts will help the overall Report render more effectively.
- Try changing your process - For example, if an Event query or report is timing out, you may be able to reduce the number of Event Templates you use or mad data when possible to a system field on the record rather than storing it on the Form Response table.