Spice sourced quicksight table take a long time to show

I have 7 million rows with 19 columns that includes 16 group by columns and 3 value columns. In the value box, I have two sum aggregation columns and another column that sums up the above two aggregation columns which is in a calculated fields as sum1+sum2.
May i know what is impacting the table to load for a long time, though the dataset is in spice? would very much appreciate it, if anybody can provide some suggestions to optimize it.

Are you saying it takes to long to preview in the dataset or in your analysis?

If you import it into SPICE the analysis and dashboard visuals should load fairly quickly. However, with that many rows it might take awhile to run for a preview.

Second thing to consider is are you selecting from a view that has more complex SQL like joining across tables/views?

Also, if you have a visual that has a lot of groups it can cause slow downs to render that visual.

For example if you have 100,000 groups and they are all rendering as different colors that can be an issue for performance.

1 Like

if data is in SPICE, 7 million rows should be able to show very quick, can share how much time it needs?

Do you mean that the data is already loaded into SPICE, but it takes long time to show in analyses/dashboard?
If yes, are you creating the “calculated fields” in analyses or dataset? creating calculated fields in dataset provides a better performance as it pre-calculates during data loading. if you create calculated field in analyses, it calculates on the fly which takes longer time

In another scenario, if it takes long time loading data into SPICE, you may consider using custom SQL to join all tables before load into SPICE. ref: Using SQL to customize data - Amazon QuickSight
Instead of joining data between huge tables in QuickSight, using custom SQL can push data joining into data warehouse(such as Redshift). Normally, it should be able to provide faster performance

And, we have a blog post showing some tips for building a high performance dashboard. you may also take a look

@Max @royyung In analysis, of course. Table visual takes 15 to 20 seconds to display the visual with all the data and when I switch pages, it again takes the same amount of time. I have no problem with any other visuals, it’s only with this table visual that has 7 million rows of data taking a long time.

I also run a simple custom SQL query on one of the table in Athena data source without any joins involved. In addition, there is no complicated calculation as such in the visual to affects its performance.

Data is already in SPICE or it is using Direct Query?

@royyung yes it is already in spice, yet, the table visual still take a long time to display, that’s my point.

toString() function, pivot table or complex calculated fields created in Analysis can be resource consuming. can try to take out those functions to test if they are the root causes.
Also, you may raise support ticket. backend engineers can see the SQL in the backend and provide insights why it takes long time to run