Dataset joined with other datasets takes too long to refresh data

Question & Answer
, ,
1

Good Morning

I have designed a new data set (DA) which is composed by the join of a direct query and two other existing data sets (DB and DC). Three datasets are set up as SPICE data sets so the data is already “pre-loaded” in the system to avoid timings using “direct queries”.
Please see attached image to have a better understanding.

ig3
ig3993×351 25.3 KB

I then use a dynamic table to represent the info that this data set “DA” produces, problem is that when I use filters in the panel to filter the information, table takes too long to get refreshed with the message “loading” shown while we wait for the info to appear in the table.

Is there any way that we can get this waiting/loading time reduced?. It seems that when I filter the information in the pannel, the system re-runs the query even the info is set to be loaded as SPICE.

Thanks a lot!

2

Potential solutions to address the slow refresh:

  1. Review Data Model: First, optimize your data model structure. Focus on joins and filters as they’re your main issue. Simplify complex calculations where possible.

  2. Optimize Direct Query Performance: Tackle the direct query dataset by adding proper indexes and optimizing the SQL query itself. This is crucial since it’s your immediate bottleneck.

  3. Use SPICE for All Datasets: Convert your direct query to SPICE if possible. Having all three datasets in SPICE will significantly improve performance by eliminating real-time database queries.

  4. Optimize Filters: Streamline your filters, especially those across datasets. Reduce complex filter calculations that might be slowing down cross-dataset operations.

  5. Use Data Caching: Enable caching for frequently used query results to reduce repeated processing.

  6. Incremental Refresh: For your SPICE datasets, set up incremental refreshes to update only new data.

  7. Scheduled Refreshes: Schedule data updates during off-peak hours to manage load better.

  8. Data Sampling: Consider this as a last resort if other optimizations don’t provide enough improvement.

Let us know if this helps.
Thanks!

3

Hi @Jlabuelo,
It’s been awhile since we last heard from you, did you have any additional questions regarding your initial post or did one of the suggestions listed above help with your case?

If we do not hear back within the next 3 business days, I’ll close out this topic.

Thank you!