I have a nullable date field in a table I’m trying to filter on, none of the table has been indexed. When I try and filter based on the month, I run into serious performance issues - the query takes 20 seconds to run when it should be subsecond.
I believe it is this block of code which is giving me issues:
CAST(format(“dbo”.“F_Sales”.“Service_Date”, ‘yyyy-MM-01’) AS datetime) = CAST(format(dateadd(month, -1, getutcdate()), ‘yyyy-MM-01’) AS datetime))
This issue only happens when specifying the month field. If i specify a date range there’s no issues, and I believe it’s because metabase uses the between function in that case. I cannot replicate this issue with a non-nullable date field. Obviously I could circumvent this issue by writing a custom sql query, but then I’d lose drill-down capability. Any idea how I can solve this/find an alternate solution?
Is far as I can see (if the CASTing is the culprit) this relates to this existing GitHub issue linked below. Note I linked to a post in the issue with a proposed workaround with creating an index in the CAST.
Would be good to know if the workaround etc. helps you out or not!