Forum Discussion

TeeGee's avatar
TeeGee
Helper II
8 years ago

DirectQuery (bug?): all possible Date filter values are being output to SQL

I have a report with multiple visuals and 4 or 5 slicers on it, accessing an Azure SQL database via DirectQuery.

 

Performance is worse than I would expect, and in debugging I'm noticing that several SQL statements seem to be outputting a very suboptiomal where clause related to a Date filter.  It seems like every possible date value is being included in a huge IN() statement, rather than no filter (if none has been specified), or just a concise less than or equal or bwteen statement in the case of a range.

 

What might be going on here?

 

Here is just a very tiny excerpt of some SQL that I see:

 

 

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),
CAST( '19000101 00:00:00' AS datetime),CAST( '20140531 00:00:00' AS datetime),CAST( '20141012 00:00:00' AS datetime),CAST( '20140424 00:00:00' AS datetime),CAST( '20140905 00:00:00' AS datetime),
CAST( '20140318 00:00:00' AS datetime),CAST( '20140816 00:00:00' AS datetime),CAST( '20141228 00:00:00' AS datetime),CAST( '20140209 00:00:00' AS datetime),CAST( '20140710 00:00:00' AS datetime),
CAST( '20141121 00:00:00' AS datetime),CAST( '20141208 00:00:00' AS datetime),CAST( '20140120 00:00:00' AS datetime),CAST( '20140603 00:00:00' AS datetime),CAST( '20141101 00:00:00' AS datetime),
CAST( '20140427 00:00:00' AS datetime),CAST( '20140514 00:00:00' AS datetime),CAST( '20140925 00:00:00' AS datetime),CAST( '20140407 00:00:00' AS datetime),CAST( '20140819 00:00:00' AS datetime),
CAST( '20140301 00:00:00' AS datetime),CAST( '20140713 00:00:00' AS datetime),CAST( '20140730 00:00:00' AS datetime),CAST( '20141211 00:00:00' AS datetime),CAST( '20140123 00:00:00' AS datetime),
CAST( '20140623 00:00:00' AS datetime),CAST( '20141104 00:00:00' AS datetime),CAST( '20140103 00:00:00' AS datetime),CAST( '20140517 00:00:00' AS datetime),CAST( '20141015 00:00:00' AS datetime),
CAST( '20140410 00:00:00' AS datetime),CAST( '20140908 00:00:00' AS datetime),CAST( '20140321 00:00:00' AS datetime),CAST( '20140802 00:00:00' AS datetime),CAST( '20141231 00:00:00' AS datetime),
CAST( '20140212 00:00:00' AS datetime),CAST( '20140626 00:00:00' AS datetime),CAST( '20141124 00:00:00' AS datetime),CAST( '20140106 00:00:00' AS datetime),CAST( '20140606 00:00:00' AS datetime),
CAST( '20141018 00:00:00' AS datetime),CAST( '20140430 00:00:00' AS datetime),CAST( '20140911 00:00:00' AS datetime),CAST( '20140928 00:00:00' AS datetime),CAST( '20140324 00:00:00' AS datetime),
CAST( '20140822 00:00:00' AS datetime),CAST( '20140215 00:00:00' AS datetime),CAST( '20140304 00:00:00' AS datetime),CAST( '20140716 00:00:00' AS datetime),CAST( '20141214 00:00:00' AS datetime),
CAST( '20140126 00:00:00' AS datetime),CAST( '20140609 00:00:00' AS datetime),CAST( '20141107 00:00:00' AS datetime),CAST( '20140520 00:00:00' AS datetime),CAST( '20141001 00:00:00' AS datetime),
CAST( '20140413 00:00:00' AS datetime),CAST( '20140825 00:00:00' AS datetime),CAST( '20140307 00:00:00' AS datetime),CAST( '20140805 00:00:00' AS datetime),CAST( '20141217 00:00:00' AS datetime),
CAST( '20140129 00:00:00' AS datetime),CAST( '20140629 00:00:00' AS datetime),CAST( '20141110 00:00:00' AS datetime),CAST( '20141127 00:00:00' AS datetime),CAST( '20140109 00:00:00' AS datetime),
CAST( '20140523 00:00:00' AS datetime),CAST( '20141021 00:00:00' AS datetime),CAST( '20140416 00:00:00' AS datetime),CAST( '20140503 00:00:00' AS datetime),CAST( '20140914 00:00:00' AS datetime)

 

 

 

 

 

11 Replies

  • v-ljerr-msft's avatar
    v-ljerr-msft
    Microsoft Employee

    Hi TeeGee,

     

    What type(Between, Before, After, List, Dropdown, Relative) of Date Slicer are you using on the report? Could you try a different slicer type to see if there is any improvement?

     

     

    In addition, if the issue persists, I would suggest you create a support ticket on Power BI Support page for better assistance on this issue. :smileyhappy:

     

    Regards

    • TeeGee's avatar
      TeeGee
      Helper II

      The slicer is on the [Year] column in the [Date] table, and it is a simple single select dropdown slicer.

  • srinivt's avatar
    srinivt
    Microsoft Employee

    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? 

    • TeeGee's avatar
      TeeGee
      Helper II

      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)