I have a SQL query that in my SQL IDE runs in about 40s, returning ~100k rows. When connecting to the same database in QuickSight and trying to load those same ~100k records into a table, the visualisation is timing out. We are doing some small formatting changes in the table compared to the SQL query, would that be the cause for it timing out? Are there any best practices for getting the table to render as quickly as possible? It feels a little odd to me that a 40s query is taking over 2 minutes to render as a table
When you’re experiencing performance issues with QuickSight tables, especially with large datasets, there are several factors to consider and best practices to follow
Potential Causes of Slow Performance:
Complex calculations or formatting in QuickSight
Large number of columns being displayed
Sorting operations on large datasets
Network latency between QuickSight and your database
Database connection performance.
Potential Best Practices to Improve Performance:
a) Data Preparation:
Use SPICE instead of Direct Query when possible
Pre-aggregate data where possible
Create calculated fields in the database rather than QuickSight
Limit the number of columns to only what’s necessary
b) Query Optimization:
Consider creating a materialized view in the database
Index frequently queried columns
Optimize the SQL query itself
SPICE Recommendations:
If you’re not already using SPICE, consider importing your data into SPICE
Schedule regular SPICE refreshes during off-peak hours
Monitor SPICE usage and optimize accordingly
Troubleshooting Steps:
Check QuickSight logs for specific timeout errors
Monitor database performance during QuickSight queries
Test with a smaller dataset to isolate if it’s a data volume issue
Review any calculated fields or formatting for optimization opportunities
If these steps don’t resolve the issue, you might want to:
Contact AWS Support for specific performance analysis