Dax query fast in SSMS but slow in SSRS RRS feed

  • Question

  • Hello,

    I'am quite new in dax and tabular database, i'am more used to sql and mdx queries and reports.

    I've created a SSRS report using several multi-value parameters. The final query is in an expression using parameters values (classic).

    Final dataset is running fine but very slow (4 minutes for 6 month of historic). Yet when i run it in SSMS, it runs in 5 seconds.

    I've heard about "parameter sniffing" which could explain my problem but not in dax context.

    My final Query : 

    Define VAR FilterTableCaseClosure =   FILTER(VALUES('Case Closure Date'[DayKey]),    
    AND( 'Case Closure Date'[DayKey] >= VALUE(20180101), 'Case Closure Date'[DayKey] < VALUE(20180601))) 
    VAR FilterTableDim1 = TREATAS(  {"VALUE1","VALUE2",..."VALUE10"},'Dim1'[Dim1Name]) 
    VAR FilterTableDim2 = TREATAS(  {"VALUE1","VALUE2",..."VALUE10"},'Dim2'[Dim2Name]) 
    VAR FilterTableDim3 = TREATAS(  {"VALUE1","VALUE2",..."VALUE10"},'Dim1'[Dim3Name])   
    EVALUATE SUMMARIZECOLUMNS('Agent Team'[Team], 'Agent'[AgentFullname], 'Case Closure Date'[DayTimestamp]
    ,'Dim1'[Dim1Name], FilterTableDim1,FilterTableCaseClosure,FilterTableDim2,FilterTableDim3
    , "Measure1 count", [Measure1 count],, "Measure3 count", [Measure3 count], , "Measure3 count", [Measure3 count])

    I dit the same report in power bi and run a trace and the query sent was quite the same and very fast, i didn't find what can explain the delta.


    Monday, March 4, 2019 10:41 AM


  • Hi AsmaBou,

    Did you mean when you run the query in Query Designer, it will slower than SSMS? Or when you run report, the report will take long time?

    It seems that you use virtual relationship in query, if you use physical relationship  directly in model, then use corresponding column in query, will it work better? Or delete the dataset and recreate to see whether it will work well.

    In addition, if the report is slow, you could deploy the report on server, then run it , check executionlog in SSMS->report server database->view to see which part cost more time. Then you could refer to Troubleshooting Reports: Report Performance for details.

    Best Regards,
    Zoe Zhi

    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    • Marked as answer by AsmaBou Thursday, March 7, 2019 2:11 PM
    Tuesday, March 5, 2019 2:54 AM