Pivot question based on a saved question - makes multiple queries to the database

Hello,

I have created a question with a pivot table visualization that is sourcing its data from another saved question. I was hoping that all data (from the database) will be fetched by the saved question in a single query and the new pivot table question will 'work on' / 'use' the fetched data (and not hit the database again & again to get the data). But I see that there are multiple queries going to the DB.

The pivot I am trying to build is a typlical one - produce a monthly product orders (order #s, sum total of sales) for last 3 months from the saved all_orders question (which pulls all the orders in the DB with some transformations with SQL, not just last 3 months).

I notice that my pivot table question is making 32 queries (of the saved question) to the database - looking at the metabase log. I was expecting to see just 1 query from the saved question towards the database. This is drastically slowing up my pivot question.

Any tips on how to improve this from the metabase end?

Thanks,

PS: I dont have control on changing anything on the database server end. I can just write a query and extract data, which I am doing in the all_orders saved question already

Hi @sanbee

Post "Diagnostic Info" from Admin > Troubleshooting, and which database type you're querying.

The Pivot Table visualization will create queries to generate the subtotals.

Which "32 queries" are you referring to? It's not possible to see that in the log of Metabase - only on the query log on the database.

HI,
The diagnostic info is as follows:
{
"browser-info": {
"language": "en-US",
"platform": "Linux x86_64",
"userAgent": "Mozilla/5.0 (X11; Linux x86_64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/92.0.4515.131 Safari/537.36",
"vendor": "Google Inc."
},
"system-info": {
"file.encoding": "UTF-8",
"java.runtime.name": "OpenJDK Runtime Environment",
"java.runtime.version": "1.8.0_262-b10",
"java.vendor": "Oracle Corporation",
"java.vendor.url": "http://java.oracle.com/",
"java.version": "1.8.0_262",
"java.vm.name": "OpenJDK 64-Bit Server VM",
"java.vm.version": "25.262-b10",
"os.name": "Linux",
"os.version": "3.10.0-1127.19.1.el7.x86_64",
"user.language": "en",
"user.timezone": "UTC"
},
"metabase-info": {
"databases": [
"mysql"
],
"hosting-env": "unknown",
"application-database": "mysql",
"application-database-details": {
"database": {
"name": "MariaDB",
"version": "10.5.5-MariaDB"
},
"jdbc-driver": {
"name": "MariaDB Connector/J",
"version": "2.6.2"
}
},
"run-mode": "prod",
"version": {
"tag": "v0.39.4",
"date": "2021-06-15",
"branch": "release-x.39.x",
"hash": "f538050"
},
"settings": {
"report-timezone": "Asia/Calcutta"
}
}
}

The Pivot Table visualization will create queries to generate the subtotals.

Should'nt the pivot visualization work on the data fetched from the "save question"? From what I notice, it runs the query in the saved question "n" times and hence the performance impact - like you explained, creates multiple queries to build the subtotals. My understanding (or assumption) of building a question on top of an existing question was that we work with that data and not a new query itself. It is a dead end if this is how it works.

Which "32 queries" are you referring to? It's not possible to see that in the log of Metabase - only on the query log on the database.

Sorry if that 32 queries is misleading. That magic number 32 is probably specific to my data & my visualization. The log I am referring to is the output from running .jar file which we log/redirect to a file.
java -jar /var/metabase/metabase.jar >> /var/log/metabase.log
I was referring to the number of times the base query was triggered by counting it from the above log.

@sanbee Post the log - that would make it much easier for me to understand.

Metabase is not a database, so the subtotal-queries are made against your database - based on the saved question's query.

While it's possible to use saved questions for a lot of things, it's difficult for Metabase to fully understand the column results and to create perfectly optimized queries based on a SQL query.
I would recommend that you try with a database View, so Metabase gets much more metadata and is better able to generate optimized subtotal-queries.