Forum Discussion
DirectQuery (bug?): all possible Date filter values are being output to SQL
We are making some small improvements in the product to handle cases where the large IN slice can be eliminated. However, there could still be a valid use-case where we have large IN slice.
Have you tried rewriting the SQL query with the large IN slice into an equivalent range filter? Does that improve the query performance for you on the SQL side?
Running the SQL in SSMS, if I change:
WHERE ( ([t1].[DateKey] IN (CAST( '20140315 00:00:00' AS datetime),CAST( '20140206 00:00:00' AS datetime),CAST( '20140620 00:00:00' AS datetime),CAST( '20141118 00:00:00' AS datetime),
to:
WHERE t1.Year = '2015'
The execution time changes from 4m52s to 1 second.
([t1] is the Date table)
Thanks for confirming that.
I'm thinking the root cause may be a Measure containing ALL(), for example:
AP Balance =
CALCULATE (
-sum('3 Accounts Payable'[Amount Master]),
FILTER (
ALL ( 'Date' ),
'Date'[DateKey] <= MAX ( 'Date'[DateKey] )
)
)If you think about it, perhaps this makes sense: when I set a slicer filter on [Date].[Year], the engine has no way of knowing that [Date].[Year] = YEAR([Date].[DateKey]), at least not in my case as the data is physically populated in the underlying SQL table.
So the question now becomes: is there a way for me to re-architect this such that in can infer that fact and properly optimize this query?
Well, I tried adding a new calculated (in M) column and using that in the slicer, same problem.
= Table.AddColumn(dbo_DimDate, "YEAR_CALC", each Date.Year([DateKey]), Int64.Type)
Also tried adding a new calculated column in the model and putting a slicer on that, same problem.
Thinking a bit more though....my cannot optimize theory above is actually wrong. The slicer options are basically a "select distinct [SlicerColumn] from the underlying table, the fact that it is (or is not) YEAR(DateKey) is completely irrelevant; all [Date] rows that match can be selected simply by: WHERE [Date].[Year] = {selected slicer value}
Any ORM has to handle this scenario, so I can't understand why it can't be optimized here. What it is doing now is actually far more complicated than the efficient statement would be to generate, so something weird must be going on????