Question slow on creation, but fast in dashboard

Get Help
1

I’m trying to create a dashboard showing information about, for example, a specific user of an app - e.g. which pages they’ve visited.

The query is plenty fast enough when filtered by a single user. However, to make the question compatible with filters on a dashboard level, I initially need to ask it with no user_id specified, which is too slow to complete. Not completing also prevents the question from from saving.

I’m working around this at the moment by specifying an arbitrary user_id when making the question, then going back and deleting it later. Even if the question doesn’t complete when deleting the filter, it allows me to overwrite it, and now the dashboard works.

Is there a better way of doing this?

Thanks very much!

2

Hi @dgmp

Please post “Diagnostic Info” from Admin > Troubleshooting.

It seems like your database is not optimized or something else is going on, since the query doesn’t complete, when you don’t use filtering.

You should be able to save the question without running the query.

If you are using Native question, then you can use default filters, or required filters, so the query doesn’t run without filtering.
https://www.metabase.com/docs/latest/users-guide/13-sql-parameters.html

3

Thanks very much for the speedy response @flamber !

Yep, indeed the database is not optimised to run the query across all users. I wouldn’t really expect it to complete until filters are applied - I guess my question is: is there a way to create a question that shouldn’t run until the dashboard level filters are applied?

A native question would definitely work here! That’s a reasonable solution, I was just curious if it was possible using the GUI (e.g. for my colleagues who don’t write SQL).

4

@dgmp
Please post “Diagnostic Info” from Admin > Troubleshooting.

Yes and no. So you don’t have the same options in GUI, but you can setup dashboard with a default filter - or you can limit the results of the question to 1 row.

But that still doesn’t answer why the query doesn’t complete. You’re just showing a raw table, which should be really fast.

5

Yes and no. So you don’t have the same options in GUI, but you can setup dashboard with a default filter - or you can limit the results of the question to 1 row.

Ah, OK. Fair enough, thanks!

But that still doesn’t answer why the query doesn’t complete. You’re just showing a raw table, which should be really fast.

Sorry, that wasn't clear from my question - it's not a raw table, there are a couple of group by's happening (e.g. count hits over time) that make it slow to do on the whole table, but fast enough after filtering.

Please post “Diagnostic Info” from Admin > Troubleshooting.

Happy to do so if it's still helpful, but I'm very confident this is just the DB struggling rather than a metabase issue.

Thanks again!

6

@dgmp Yep, please post “Diagnostic Info”, since I notice a lot of problems from that little piece of information.

7

sure! :slight_smile:

{
  "browser-info": {
    "language": "en-US",
    "platform": "MacIntel",
    "userAgent": "Mozilla/5.0 (Macintosh; Intel Mac OS X 10_15_4) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/83.0.4103.116 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.15.0-66-generic",
    "user.language": "en",
    "user.timezone": "GMT"
  },
  "metabase-info": {
    "databases": [
      "mysql",
      "bigquery"
    ],
    "hosting-env": "unknown",
    "application-database": "postgres",
    "application-database-details": {
      "database": {
        "name": "PostgreSQL",
        "version": "12.3 (Debian 12.3-1.pgdg100+1)"
      },
      "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",
      "hash": "1d424cb"
    },
    "settings": {
      "report-timezone": null
    }
  }
}
8

@dgmp Looks good, besides latest release is 0.35.4 - see I get something almost every time :wink: