Slow loading time in Holistics, compared to Snowflake Web UI (and BigQuery UI)

Ask the Community
,
1

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.

1. How a query works

In Snowflake Web UI (and BigQuery UI)

When we run a query in Snowflake Web UI:

  1. Snowflake executes the query internally
  2. The result is partially loaded to the users browser

In Holistics

When the same query is sent from Holistics to your Snowflake:

  1. 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;
  2. The query is sent to Snowflake, Snowflake executes the query internally;
  3. Holistics fetches the result of the query from Snowflake;
  4. Holistics processes the data internally, some notable steps are: caching data, normalizing data…
  5. 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.

To wrap up

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.

image
image801×198 11.4 KB

2. What to do if you face performance issues

  1. Check if your current queue is full and remove unnecessary jobs: Job Queue System and Workers | Holistics Docs.
  2. 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 :hearts:

4 Likes
2

Thanks for the above information and the link to the best practices.

However, I have noticed that the time it takes filters to load does not change if we enable caching or disable it.

This is important as we have users who are complaining that the performance of filters is slower than that of Data Studio, which is where we are migrating away from. This in turn is making the migration harder as people see this as a downgrade.

Is there any advice on this please?

3

Hi Craig,

Thank you for your comment!

In order to understand your issue better, would you mind making some clarifications for us?

  1. By “the time it takes filters to load”, do you mean the time it takes
    • for the filters UI to appear on top of your Dashboard page
    • or for the filters to load their option suggestions
    • or for the filters to be applied onto your reports
    • etc.?
  2. How did you “enable caching” or “disable it”?
  3. How did you compare the loading time after having enabled/disabled caching?

Besides, for us to handle your issue more effectively and in a more timely manner, would you mind submitting a ticket about this issue by sending an email to [email protected]?
We would also really appreciate if you can attach some screen recordings/measurements into the email so that we can see what exactly is happening on your side.

We will be looking forward to your response/ticket.

Have a good one!

4

Hey,

  1. Filters to load their options primarily but also when the whole page of the dashboard can take a while
  2. Within the Dashboard Preferences screen, I went to Cache Settings and have the Cache Duration set to 24 hours. But this doesn’t seem to effect the loading time.
  3. I have just used a stop watch on my phone to attempt to measure the time.

I will raise a ticket, thanks!

5

Hi Craig,

We have received your ticket and will handle it via our Support system.
In case our resolution for your issue is publicly helpful, we will post it back here.

Many thanks for submitting the ticket and providing us with such useful information!

6

Hi all,

I would like to share that the issue that Craig experienced is about some unoptimized loading of AML-based Dashboards. This is not really related to this thread that is trying to explain the difference in the query execution, because the aforementioned loading happens even before any query execution.

Nevertheless, through investigating Craig’s case, Holistics has released multiple patches to improve the loading of AML-based Dashboards. For instance, the initial loading of some Dashboards has been reduced about 3-4 times.

To all Holistics users, if you encounter any performance issues, please feel free to submit a ticket to us by sending a ticket using our support form.

Cheers,

3 Likes