data present as table about 4~5 seconds.
data present as pivod table cost 1 min 20 seconds.
Hi @Jay
Post "Diagnostic Info" from Admin > Troubleshooting, and which database you're querying.
Pivot Table does a lot of extra queries to calculate the subtotals, so if your database is not fully indexed for such queries, then those queries will take a lot longer. Check your database query log.
my db is SQL SERVER 2019
{
"browser-info": {
"language": "zh-TW",
"platform": "Win32",
"userAgent": "Mozilla/5.0 (Windows NT 10.0; Win64; x64) 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": "11.0.10+9",
"java.vendor": "AdoptOpenJDK",
"java.vendor.url": "https://adoptopenjdk.net/",
"java.version": "11.0.10",
"java.vm.name": "OpenJDK 64-Bit Server VM",
"java.vm.version": "11.0.10+9",
"os.name": "Linux",
"os.version": "5.4.0-73-generic",
"user.language": "en",
"user.timezone": "GMT"
},
"metabase-info": {
"databases": [
"h2",
"sqlserver"
],
"hosting-env": "unknown",
"application-database": "h2",
"application-database-details": {
"database": {
"name": "H2",
"version": "1.4.197 (2018-03-18)"
},
"jdbc-driver": {
"name": "H2 JDBC Driver",
"version": "1.4.197 (2018-03-18)"
}
},
"run-mode": "prod",
"version": {
"date": "2021-03-17",
"tag": "v0.38.2",
"branch": "release-x.38.x",
"hash": "91f0ed6"
},
"settings": {
"report-timezone": "Asia/Taipei"
}
}
}
@Jay
Check your database logs, which might help you to explain why the queries are slow.
You can try the latest release, 0.40.2, since there's constantly performance improvements:
https://github.com/metabase/metabase/releases/latest
You should migrate away from H2 if you are using Metabase in production:
https://www.metabase.com/docs/latest/operations-guide/migrating-from-h2.html
I try to upgrade my Docker to MariaDB DB, but when RUN
java -jar metabase.jar load-from-h2 /path/to/metabase.db
it show metabase.jar: No such file or directory
and I can't fine metabase.jar in docker environment.
@Jay If you are not familiar with Docker, then I would highly recommend that you just use JAR:
https://www.metabase.com/learn/getting-started/installing
As noted in the migration guide, it is recommended to run the migration by itself, since it's just a one-off process: https://www.metabase.com/docs/latest/operations-guide/migrating-from-h2.html#migrating-when-using-docker
Hi Flamber:
After many times try and error, I got some progress on the H2->Postgrsql, but get some error as below. Need you help!!
at clojure.java.jdbc$insert_multi_BANG_.invoke(jdbc.clj:1619)
at metabase.cmd.copy$insert_chunk_BANG_.invokeStatic(copy.clj:112)
at metabase.cmd.copy$insert_chunk_BANG_.invoke(copy.clj:106)
at metabase.cmd.copy$copy_data_BANG_$fn__74842$fn__74843.invoke(copy.clj:141)
... 51 more
Caused by: org.postgresql.util.PSQLException: ERROR: column "semantic_type" of relation "metabase_field" does not exist
Position: 62
at org.postgresql.core.v3.QueryExecutorImpl.receiveErrorResponse(QueryExecutorImpl.java:2553)
at org.postgresql.core.v3.QueryExecutorImpl.processResults(QueryExecutorImpl.java:2285)
at org.postgresql.core.v3.QueryExecutorImpl.execute(QueryExecutorImpl.java:323)
at org.postgresql.jdbc.PgStatement.internalExecuteBatch(PgStatement.java:851)
... 66 more
Command failed with exception: Error copying instances of Field
@Jay It looks like you have tried to migrate and upgrade at the same time, which is not recommended:
https://www.metabase.com/docs/latest/operations-guide/migrating-from-h2.html#before-you-migrate
So first make sure you're using 0.41.4 on H2, verify everything works, then migrate (again with 0.41.4), and then run 0.41.4 on Postgres.