Performance issues when using existing question as a nested sub-query

First off: Metabase is an incredible tool, and this community is truly supportive and amazing. Thank you for taking a look at this question.

I discovered that you can use an existing question as a sub-query today in the docs: https://www.metabase.com/docs/latest/users-guide/13-sql-parameters.html#using-an-existing-question-as-a-sub-query

Which is an amazing feature. I have 4+ queries, written natively, that all use the same simple query as a base, call it “base_query”. Being able to use base_query as an “include” to the 4+ other queries would be awesome!

I created a new question that contained base_query and tried that in one of the existing questions. It produced the same results! Magic! I can significantly reduce my code maintenance!

But the new version, using:
FROM ({{ #base_query }} ) “source”

takes 9.5 seconds to complete.

The original question completes in <100ms. Caching is turned off.

Before I continue refactoring, I’m curious if there’s something under the hood of the {{#question}} feature that causes this difference in performance?

Besides caching, is there anything I can do? Is the performance of the original query due to some other caching mechanism?

Here’s the code segment from the new version of the question:

  SELECT "source"."object_id" AS "object_id",
         sum("source"."object_count") AS "sum",
         sum("source"."object_volume") AS "sum_2",
         count(DISTINCT "source"."id") AS "count"
  FROM {{#78}} as "source"
  WHERE true
    [[ AND "source"."id" = {{ ID }}]]
    [[ AND ({{ stock_date }}) ]]

  GROUP BY "source"."object_id"

Thank you again!

Looking at the logs, I guess somehow additional DB calls are being made:

Full native:
POST /api/card/77/query 202 [ASYNC: completed] 182.4 ms (8 DB calls) App DB connections: 1/10 Jetty threads: 2/50 (5 idle, 0 queued) (102 total active threads) Queries in flight: 1 (0 queued)

Nested sub-query:
POST /api/card/79/query 202 [ASYNC: completed] 8.9 s (11 DB calls) App DB connections: 1/10 Jetty threads: 2/50 (5 idle, 0 queued) (102 total active threads) Queries in flight: 0 (0 queued)

Hi @dolphinkickme

Please post “Diagnostic Info” from Admin > Troubleshooting.

That’s a massive overhead, but without knowing which database you’re querying and the data size, then it’s difficult to say what impact that has.

I might be misunderstanding something, but you’re referencing {{#78}}, while showing logs for question 77 at 182ms.

Also, you’re using a Field Filter ({{ stock_date }}), which doesn’t support table aliases, so not sure how that’s even working - or if that’s the root cause of the problem.

Can you create a query, where you simply replace {{#78}} with ( the-actual-nested-select-query ), since that’s basically what Metabase does before sending it to your database.

Also, check your database log to see the full query that it’s receiving and try to analyze that, since it might just be bad indexing.

Hi @flamber!

Question 77 is the native query I want to refactor (so this is { the-actual-nested-select-query }).
#78 is a section pulled from #77.
#79 is #77 rewritten to use {{ #78 }}, so in theory, the same as #77.

I never tested the field filter. I removed it and found the same performance result.

I’ll check the database log asap.

Here’s the “Diagnostic Info”:

{
“browser-info”: {
“language”: “en-US”,
“platform”: “MacIntel”,
“userAgent”: “Mozilla/5.0 (Macintosh; Intel Mac OS X 10_14_6) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/81.0.4044.138 Safari/537.36”,
“vendor”: “Google Inc.”
},
“system-info”: {
“file.encoding”: “UTF-8”,
“java.runtime.name”: “OpenJDK Runtime Environment”,
“java.runtime.version”: “11.0.5+10”,
“java.vendor”: “AdoptOpenJDK”,
“java.vendor.url”: “https://adoptopenjdk.net/”,
“java.version”: “11.0.5”,
“java.vm.name”: “OpenJDK 64-Bit Server VM”,
“java.vm.version”: “11.0.5+10”,
“os.name”: “Linux”,
“os.version”: “4.14.173-137.229.amzn2.x86_64”,
“user.language”: “en”,
“user.timezone”: “GMT”
},
“metabase-info”: {
“databases”: [
“postgres”
],
“hosting-env”: “elastic-beanstalk”,
“application-database”: “postgres”,
“application-database-details”: {
“database”: {
“name”: “PostgreSQL”,
“version”: “11.5”
},
“jdbc-driver”: {
“name”: “PostgreSQL JDBC Driver”,
“version”: “42.2.8”
}
},
“run-mode”: “prod”,
“version”: {
“date”: “2020-04-21”,
“tag”: “v0.35.3”,
“branch”: “release-0.35.x”,
},
“settings”: {
“report-timezone”: “US/Eastern”
}
}
}

Thank you for your help!

@dolphinkickme Seems like something really strange is going on - we now have an issue open on it:
https://github.com/metabase/metabase/issues/13572

Thank you @flamber - I’ve been avoiding nested queries since!

And thank you for your seemingly tireless support of the community.

1 Like