Forum Discussion

JulianC8's avatar
JulianC8
Frequent Visitor
2 years ago

Very low performance of query with Left Outer Join

Hi,

 

in my current PowerBI Project I import two Tables (xlsx):

  • Table1 contains 940.000 rows and 23 columns (~75 MB)
  • Table2 contains 200.000 rows and 4 columns (~4 MB)

The number of rows will increase further in both tables.

In the PowerBI Query for Table1 I use a Left Outer Join to get another column from Table2 into Table1.

This join is done with the help of an unique identifier which is present in both tables (type any, length of this string is always 23 chars)

The column which is added is from type text and might be between 4 and 20 characters long

 

Before and after this Join Step, I do various other smaller tasks (renaming/adding columns, replace values, ...)

 

Now I furhter reference the Table1 into a new table and do some more things with it, as for example split a column by a delimiter.

 

The problem I have now is that the queries are getting very very slow. It is almost impossible for me to furhter develop, as it takes roughly a minute to display the data in PBI every time I click on another step. On the right bottom side of the PBI window I can see, that every time I click on another step of any query, the data of Table1 as well as the date of Table 2 is calculated again and again to display the result of the current step.

 

I played around a bit with Table.Buffer but without any success. I dont even know if Table.Buffer is even the right tool to fix my performance issues. To me it is pretty obvious, that the Outer Join is the bottleneck in my calculation.

 

Has anyone an idea, what I can do to improve?

 

 

 

 

 

 

 

Need Help

4 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    2 years ago

    Tell us about the join columns, and the types and uniqueness.

     

    --Nate

    • JulianC8's avatar
      JulianC8
      Frequent Visitor
      2 years ago

      Hi Anonymous 

      I added the info to my initial post

  • Anonymous's avatar
    Anonymous
    Not applicable
    2 years ago

    Hi JulianC8 ,

     

    -- This join is done with the help of an unique identifier which is present in both tables (type any, length of this string is always 23 chars)
    If your unique identifier is a string, make sure it's set to the correct type and not 'any'.

    Filter data before performing a join. For example, remove unneeded columns and filter out unnecessary rows early in the query.

     

    -- I played around a bit with Table.Buffer but without any success.

    How do you use the Table.Buffer? Try caching smaller Table2 separately:

    let
         SourceTable1 = ...,
         SourceTable2 = ...,
         BufferedTable2 = Table.Buffer(SourceTable2),
         JoinedTable = Table.NestedJoin(SourceTable1, "UniqueID", BufferedTable2, "UniqueID", "JoinedTable", JoinKind.LeftOuter),

    Best Regards,
    Gao
    Community Support Team

     

    If there is any post helps, then please consider Accept it as the solution  to help the other members find it more quickly.
    If I misunderstand your needs or you still have problems on it, please feel free to let us know. Thanks a lot!

    How to get your questions answered quickly --  How to provide sample data in the Power BI Forum -- China Power BI User Group

  • PwerQueryKees's avatar
    PwerQueryKees
    Icon for Super User rankSuper User
    2 years ago

    Do you development with smaller tables. The calculating and loading of all intermediate results is very useful during developement, but slows everything down.

    The actual query run on your production report will take less time and may even be less of a problem unless you do a refresh very often.

    The problem with optimizing the performance for developementis: It will do just that! It may even harm the performance of your production reports.