Generated queries from Metabase are very slow when specifiying date range filter

Get Help
1

We tried to figure out the reason of slowness but we didn't find any thing related to our system, when the same query executed outside Metabase the result came instantly.
Sample query from the log (columns names changed/renamed):

{:constraints {:max-results 10000, :max-results-bare-rows 2000},
:type :query,
:middleware {:js-int-to-string? true},
:database 2,
:query {:source-table 6, :filter [:between [:datetime-field [:field-id 83] :minute] "2020-09-27T12:30:00" "2020-09-27T12:30:00"], :aggregation [[:count]], :breakout [[:field-id 101]]},
:parameters ,
:async? true,
:cache-ttl nil},
:native
{:query
"SELECT COLUMNS, count(*) AS "count" FROM TABLE_NAME WHERE (DATE_COL >= ? AND DATE_COL < ?) GROUP BY COLUMN1 ORDER BY COLUMN1 ASC",
:params (#t "2020-09-27T12:30Z[UTC]" #t "2020-09-27T12:31Z[UTC]")}

Could you please advise what cause this issue:
Troubleshooting info:
Database version : Oracle 19C
Ojdbc version: ojdbc10
Column datatype: Date

{
  "browser-info": {
    "language": "en-US",
    "platform": "Win32",
    "userAgent": "Mozilla/5.0 (Windows NT 10.0; Win64; x64; rv:83.0) Gecko/20100101 Firefox/83.0",
    "vendor": ""
  },
  "system-info": {
    "file.encoding": "UTF-8",
    "java.runtime.name": "OpenJDK Runtime Environment",
    "java.runtime.version": "11-ea+28",
    "java.vendor": "Oracle Corporation",
    "java.vendor.url": "http://java.oracle.com/",
    "java.version": "11-ea",
    "java.vm.name": "OpenJDK 64-Bit Server VM",
    "java.vm.version": "11-ea+28",
    "os.name": "Linux",
    "os.version": "3.10.0-957.el7.x86_64",
    "user.language": "en",
    "user.timezone": "Asia/Riyadh"
  },
  "metabase-info": {
    "databases": [
      "h2",
      "oracle"
    ],
    "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": "2020-09-15",
      "tag": "v0.36.6",
      "branch": "release-0.36.x",
      "hash": "cb258fb"
    },
    "settings": {
      "report-timezone": "UTC"
    }
  }
}
2

Hi @radi
You should check the log on Oracle to see how indexes are used. On many other databases that is done with prepending EXPLAIN ANALYZE to the query.
https://www.metabase.com/blog/sql-best-practices/index.html#explain

The example query you posted isn’t “generated” by Metabase - that’s your own manual query. If you’re using Field Filters, then that’s another thing, but then please specify that.

You should migrate away from H2 if you’re using Metabase in production:
https://www.metabase.com/docs/latest/operations-guide/migrating-from-h2.html
Also, latest release is 0.36.8.1 of the 0.36-series and 0.37.0.1.

3

Hi @flamber
Thanks for your reply, I already mentioned in the title these queries generated from Metabase and we don't have control on them, the sample I provided is for the generated queries as per the Metabase log.
Kindly find the below generated logs after canceling the running question (removed/renamed columns for production database):

  {:constraints {:max-results 10000, :max-results-bare-rows 2000},
      :type :query,
      :middleware {:js-int-to-string? true},
      :database 2,
      :query {:source-table 6, :filter [:between [:datetime-field [:field-id 83] :minute] "2020-09-27T12:30:00" "2020-09-27T12:30:00"], :aggregation [[:count]], :breakout [[:field-id 101]]},
      :parameters [],
      :async? true,
      :cache-ttl nil},
     :native
     {:query
      "SELECT COLUMNS, count(*) AS \"count\" FROM TABLE_NAME WHERE (DATE_COL >= ? AND DATE_COL < ?) GROUP BY COLUMN1 ORDER BY COLUMN1 ASC",
      :params (#t "2020-09-27T12:30Z[UTC]" #t "2020-09-27T12:31Z[UTC]")}

Thank you for your advice regarding H2 database.

4

@radi Okay, but that’s very different from your original post. Here Metabase generates a query with parameters, which is parsed on to the driver, so it depends a lot on how the driver handles the query.

I don’t know which column type DATE_COL is, but I’m guessing datetime or timestamp.

I would recommend that you try ojdbc8.jar version 19.8

But it sounds like you’re seeing this issue:
https://github.com/metabase/metabase/issues/11837 - upvote by clicking :+1: on the first post

5

Thank you @flamber, I updated the original post with captured log and field type.
I will try your recommendation and update this thread with the result.

6

@radi If the DATE_COL is a date, then why are you trying to get minutes? Sounds like the Field Type might not be correctly set in Admin > Data Model.

7

Hi @flamber

I already imported the ojdbc version that you recommended, and I tried to use all available date format (date,time,timestamp) from data model. But still I am facing the same issue.
This is the where statement for current day records taking more than 10 minutes to return data as per our oracle logs which resulted to timeout at Metabase side:
WHERE trunc(DATE_COL, ‘dd’) = trunc(SYSDATE, ‘dd’)) WHERE rownum <= 2000"

Could you please let us know if this issue is resolvable and how, so we can proceed with Metabase or we should search for other solutions.

Thank you.

8

@radi Then you are seeing the issue I referenced above. I know we’re trying to address the issue, but I don’t have a timeline, so if that doesn’t work for you, then you should use a different tool.