I am considering migrating to Metabase from a different BI platform.
For the testing I had set up Metabase 0.36.2 using docker with PostreSQL 12.3 as the application DB.
All of the team’s work is in native SQL, and a lot of the queries use results from other queries (what is know in metabase as using existing question as a sub-query)
We noticed that Metabase is very slow compared to the other BI platform when running exactly the same queries on the exact same DB (Both connect to MySQL DB).
When I started investigating why, I found that when I access our dashbaord, which contains 6 or so queries (tables only without any visualisations), the CPU of the machine running MySQL DB reaches 400%.
This does not happen on the other BI platform.
I did some more digging around and found that when I run the same query, only instead of using the saved questions results i use nested queries, the query with the saved questions is 3 times slower than the one that is implemented with nested queries.
The calculations, joins, and everything else is the same.
- Why does using nested queries over saved questions improve performance so drastically?
- Why is using results from saved questions so intensive on MySQL DB which is not even the application DB? What operations other than getting the content are performed on that DB?
- How can I improve this?
To clarify - All the queries only read from the DB. No insert, alter, update delete actions are being performed.