Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago
Solved

Troubleshooting slow cross-filtering

I am having some trouble with cross-filtering being extremely slow (20 seconds) compared to using a slicer to achieve the same result (<1 second).   My report consists of the following: 1. "Summar...
Need Help
  • Anonymous's avatar
    Anonymous
    6 years ago

    I've worked around the issue using the following method:

     

    1. "Cache" the results of the direct query into a table on my database.

    2. Change the query for the summary table to query the newly created table.

     

    It is now working extremely fast, due to the speed of doing a simple select query against a database.

     

    This adds the complexity of having to keep that table updated for the report users, but at least the performance is better!

     

    Would still be interesting to learn how to optimise cross-filtering queries, struggling to find any good reference material online.

amitchandak's avatar
amitchandak
Super User
6 years ago

Anonymous , if there are any bi-directional join, make them single directional.  Check the query that is generating and check if there are  indexes to support that query.

 

Also refer: https://www.youtube.com/watch?v=4kVw0eaz5Ws

  • Anonymous's avatar
    Anonymous
    Not applicable
    6 years ago

    amitchandak 

     

    Thank you for the reply.

     

    The joins are single-directional.

     

    How would an index assist in this issue? I am having an issue with the Import table - not the DirectQuery table.

    To clarify:

    1. The Summary table visual has the DirectQuery data source. This is the table visual where I select rows in order to cross-filter the other visual.

    2. The Sales History matrix visual is an Import data source, so from my understanding indexes would not be relevant as the entire data set already exists in the model?

    • amitchandak's avatar
      amitchandak
      Super User
      6 years ago

      Anonymous  , are taking data from both sources (import/direct) together. Can you share relationship diagram with Direct/import table labelled

      • Anonymous's avatar
        Anonymous
        Not applicable
        6 years ago

        Yes, I am using a mix of Import and Direct.

        As stated in the OP, I did also try using only DirectQuery for both tables, but the result was the same.

         

        Data model:

        vw_PBI_Redundant... is the DirectQuery table I am using for the "Summary" table visual.

        Sales History NotTraceable is the import table I am using for the "Sales History" table visual.

         

        The other two tables in the model provide information on additional visuals in the report. Could they be impacting performance?