Troubleshoot slow reports
Are you experiencing performance problems with your Holistics dashboards?
This post explains potential reasons why your dashboards might take a long time to load, and what can you do about it.
What happens when you open a report
If the query result is cached, step 2 to 5 of this flow would not be executed.
When a user opens a report in Holistics, it will typically go through these steps:
- Pre-Query Processing: Pre-query processing tasks like parsing the modeling layer, applying permission rules, building the query plan, etc.
- Job Queuing: A new job is pushed into the Job Queue, waiting for the next available worker.
- Query Execution: SQL query is executed against the customer’s Data Warehouse.
- Transfer the result set to Holistics server: Once the query finishes, the result set is transferred to Holistics servers.
- Post-Query Processing: Once the query is finished, the result set then goes through additional processing, mainly consisting of caching operations.
- Transfer the result set to user’s browser: The result set (or a part of it) is transferred to the user’s browser.
- Visualize the result set on user's browser: The visualizations are rendered from the result set on the user's browser.
Understanding the above process will help us pinpoint the bottlenecks and factors that cause performance problems with your reports. It is important to determine which steps are causing the problem, then troubleshoot the underlying issues.
Below are common factors that contribute to the long execution time of a Holistics report.
Common reasons that make your report job slow
Job waiting in the Queue for a long time
Holistics uses a job queue system to manage incoming report requests. There is a finite number of workers (to process jobs) in a queue.
As a result, if there are many jobs filing to the system at the same time (more than your current job queue size can handle), new jobs will likely wait for a long time to be processed.
To troubleshoot, go to your Jobs Monitoring dashboard and use the Status Filter. All unfinished statuses and check the jobs which fall under that category.
All unfinished statuses filter includes jobs that have one of these statuses:
- Pending
- Starting
- Running
See Life Cycle of a Job for more info about Job statuses.
Query taking a long time to run on your database
Holistics doesn’t store or process your raw data. Instead, for every report request, Holistics generates a SQL query and run them against your data warehouse. This means that your report’s performance depends on how fast/slow your data warehouse runs the SQL query.
Based on our experience, this step usually takes the most time in the report execution.
What are possible reasons for a long-running query?
- Complex SQL queries (usually involve many joins)
- Querying tables with a large amount of records
- Your database is experiencing a high workload.
- Show-rows-with-no-data is enabled ⇒ Enabling this option may slow down report performance because Holistics needs to execute additional queries to display rows with no corresponding data.
How can I troubleshoot this issue?
Head over to Investigate and troubleshoot data warehouse issues for detailed instructions on how to deal with this issue.
Result set taking a long time to transfer from your database to Holistics servers or from Holistics servers to your browser
Because Holistics is a cloud-based solution, there will be some over-the-network transferring of data between these servers: Your data warehouse → Holistics servers → End user’s browsers.
A high latency network (usually due to long distances) will add to the report loading time. The problem is made worse when the query’s result set contains a large number of records.
How can I troubleshoot this issue?
- Check the number of records returned from the query’s result set.
- Check and compare your DW server’s locations with Holistics’ server location and end user’s location. If you believe this is the problem, consider migrating to another region.
Additional overheads in pre-processing and post-processing on Holistics’ side
Because Holistics is a Business Intelligence tool with complex business and visualization logic involved, it requires some additional processing such as:
- Data Access Control
- Resolving row-level/column-level permissions
- Timezone check and shifting logic
- For special visualizations like Period-over-Period or Metric Sheet, extra logic is appended to the query plan
- etc.
Because of these operations, Holistics may appear to be not as fast as other (more) simplistic SQL/querying tools whose features are limited to only executing queries and displaying the raw queries’ results. However, they are crucial for Holistics to serve as a dynamic, powerful, and all-round Business Intelligence application.
Nevertheless, Holistics team does not take this for granted. We acknowledge the performance overhead as a trade-off and are constantly working to enhance the experience.
What should I do next?
- Consult the Best Practices to improve Reporting Performance
- Check out the available Job Controls
- If you have applied all of our best practices but are still unsatisfied with the performance, please help us:
- Collect jobs that you find underperforming on the Job Monitoring page.
- Then, share it with us via [email protected] with these details. Our Support team is happy to assist you with your case.