Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago

Improving formula engine performance

I have a query that's taking 55 seconds for the FE and 1 for the SE   DAX Studio obviously gives me the SQL and SE timings, but I can't see how to either   a) see what's taking 55 seconds in the ...
Need Help
Greg_Deckler's avatar
Greg_Deckler
Community Champion
6 years ago

Have you messed around with the Power Query diagnostic options (Tools tab) or DAX Performance Analyzer (View tab)?

  • Anonymous's avatar
    Anonymous
    Not applicable
    6 years ago

    Hi Greg_Deckler - thanks again for your help

     

    I've used the DAX PA to generate performance data for export to DAX Studio, and have been running queries there - following the approach of commenting out fields to see what's causing issues.

     

    Haven't used PQ diagnostic tracing - will need to learn more about it. Does it help query optimisation?

     

     

    • mahoneypat's avatar
      mahoneypat
      Microsoft Employee
      6 years ago

      Query diagnostics will help you troubleshoot your power query refresh, but not your DAX expressions.  Can you post your DAX expression so we can propose optimization changes?

      Regards,

      Pat

       

      • Anonymous's avatar
        Anonymous
        Not applicable
        6 years ago

        Thanks mahoneypat - here's the DAX that's taking 56 seconds to run

         

        DEFINE
  VAR __DS0FilterTable = 
    TREATAS(
      {"2020 Q1",
        "2020 Q2",
        "2020 Q3",
        "2020 Q4"},
      'bonus_region_sales_target'[remuneration_period]
    )

  VAR __DS0FilterTable2 = 
    FILTER(
      KEEPFILTERS(VALUES('bonus_applications_details'[is_eligible_for_bonus])),
      NOT('bonus_applications_details'[is_eligible_for_bonus] IN {0,
        BLANK()})
    )

EVALUATE
  TOPN(
    502,
    SUMMARIZECOLUMNS(
      ROLLUPADDISSUBTOTAL(
        ROLLUPGROUP(
          'Application Received Date'[Application Recieved Date],
          'bonus_applications_details'[application_code],
          'bonus_applications_details'[fundraise_name],
          'bonus_applications_details'[investor_name],
          'adviser_contacts'[adviser_code],
          'adviser_contacts'[adviser_name],
          'current_intermediaries'[intermediary_code],
          'current_intermediaries'[company_name],
          'bonus_applications_details'[amount_on_application],
          'bonus_applications_details'[pending_payment_amount],
          'bonus_applications_details'[imputed_percentage],
          'dbt_marts oisales__bonus_accelerators'[activation_type],
          'bonus_applications_details'[sales_region],
          'bonus_applications_details'[remuneration_period],
          'bonus_applications_details'[investor_code],
          'bonus_applications_details'[is_cleared],
          'bonus_bdm_sales_target'[employee_name]
        ), "IsGrandTotalRowTotal"
      ),
      __DS0FilterTable,
      __DS0FilterTable2,
      "Amount", 'bonus_applications_details'[Amount]
    ),
    [IsGrandTotalRowTotal],
    0,
    'Application Received Date'[Application Recieved Date],
    1,
    'bonus_applications_details'[application_code],
    1,
    'bonus_applications_details'[fundraise_name],
    1,
    'bonus_applications_details'[investor_name],
    1,
    'adviser_contacts'[adviser_code],
    1,
    'adviser_contacts'[adviser_name],
    1,
    'current_intermediaries'[intermediary_code],
    1,
    'current_intermediaries'[company_name],
    1,
    'bonus_applications_details'[amount_on_application],
    1,
    'bonus_applications_details'[pending_payment_amount],
    1,
    'bonus_applications_details'[imputed_percentage],
    1,
    'dbt_marts oisales__bonus_accelerators'[activation_type],
    1,
    'bonus_applications_details'[sales_region],
    1,
    'bonus_applications_details'[remuneration_period],
    1,
    'bonus_applications_details'[investor_code],
    1,
    'bonus_applications_details'[is_cleared],
    1,
    'bonus_bdm_sales_target'[employee_name],
    1
  )

ORDER BY
  [IsGrandTotalRowTotal] DESC,
  'Application Received Date'[Application Recieved Date],
  'bonus_applications_details'[application_code]/*,
  'bonus_applications_details'[fundraise_name],
  'bonus_applications_details'[investor_name],
  'adviser_contacts'[adviser_code],
  'adviser_contacts'[adviser_name],
  'current_intermediaries'[intermediary_code],
  'current_intermediaries'[company_name],
  'bonus_applications_details'[amount_on_application],
  'bonus_applications_details'[pending_payment_amount],
  'bonus_applications_details'[imputed_percentage],
  'dbt_marts oisales__bonus_accelerators'[activation_type],
  'bonus_applications_details'[sales_region],
  'bonus_applications_details'[remuneration_period],
  'bonus_applications_details'[investor_code],
  'bonus_applications_details'[is_cleared],
  'bonus_bdm_sales_target'[employee_name]*/

         

         

    • Greg_Deckler's avatar
      Greg_Deckler
      Community Champion
      6 years ago

      Yes, it provides a breakdown of basically every step in your query and how long it is taking.

    • Greg_Deckler's avatar
      Greg_Deckler
      Community Champion
      6 years ago

      Yes, it provides a breakdown of basically each step of the query and how long it is taking.