Question: I’m using Snowflake, and I find that the running time in Holistics is much longer than that in Snowflake Web UI. How can I fix it?
You might feel that our app performance is not as fast as other applications (i.e: Snowflake Web UI), but in fact there are several reasons that can affect the performance.
In this post, we will clearly explain the differences in how Holistics and Snowflake fetch and display data, which somehow can help you understand why they are quite different in loading speed.
When we run a query in Snowflake Web UI:
- Snowflake executes the query internally
- The result is partially loaded to the users browser
When the same query is sent from Holistics to your Snowflake:
- The query enters the Holistics job queue system. Our job queue only allows a number of jobs to be executed simultaneously at the same time;
- The query is sent to Snowflake, Snowflake executes the query internally;
- Holistics fetches the result of the query from Snowflake;
- Holistics processes the data internally, some notable steps are: caching data, normalizing data…
- Holistics sends the final result to your viewers’ browsers
As you can see, there are multiple steps compared to Snowflake Web UI that a query will go through in Holistics. Normally, step (1) and (3) take most of the time of the whole process:
- Step 1: If the current queue is full, the query needs to wait on the Holistics side. You often see the job with the status “Queued” before its status switches to “Running” in Holistics. Even though this is a “Select 1”, it may feel slow because of this reason. You can read our detailed explanation here.
Step 3: Holistics will fully download the result from Snowflake, for example, if the result contains 90K rows, all of them need to be downloaded to Holistics.
In addition, network time and Snowflake’s client are two main factors affecting this downloading process.
In brief, despite the differences in how these two apps deal with a query, Snowflake Web UI makes you feel too fast mainly because it partially loads the result: Only a handful of rows are fetched and displayed in the users’ browser, not all the result. When the user scrolls through the result set, new data is then fetched.
You can simply test this out by executing a heavy query, then export the result. You would see the full data takes a much longer time to be exported compared to the result that’s being showed on the browser.
- Check if your current queue is full and remove unnecessary jobs: Job Queue Management | Holistics Docs.
- Enhance your reporting performance by following our guide: Best practices to improve Holistics reporting performance | Holistics Docs
In case you have applied our above best practices but the slow performance still significantly negatively impact your team, you can drop us a message at [email protected] with these following information. We will try our best to support you