Report and Query Timeout Errors

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 time out. 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 will then impact the execution time. Each report essentially comprises three queries on the back end, so this issue is even more critically important in reports.

Maximizing efficiency and avoiding timeouts on new queries:

  • Query Results: The number of records returned in the report or query will certainly have an impact on the execution duration.  Certain tables such as Source, Ping, Message, and Form Response are particularly large, often containing hundreds of thousand of rows. Any 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" statements and "NOT IN" filters are less efficient in SQL Server and should be avoided. Filtering in the affirmative is more efficient.
  • Formulas: Formulas are computationally expensive and will take longer to resolve.

Improving efficiency on existing queries or reports:

  • Filtering: Filter in the affirmative to avoid "NOT IN" filters whenever possible, and reduce the use of 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: In reports, try replicating high level filters from the base (Data/Chart) level in a report on the columns and rows. Since a report essentially comprises three 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 move data when possible to a system field on the record rather than storing it on the form response table.
Was this article helpful?
0 out of 1 found this helpful