Forum Discussion

Gabry's avatar
Gabry
Icon for Super User rankSuper User
1 year ago

Table.combine - Append process takes too long. What to do?

Hello guys,

 

I have one table with 100 million rows and another with about 250K rows. I'm using one dataflow to retrieve this data from a SQL Server, and then a second dataflow to append them. The problem is that the append operation takes a very long timeā€”around 2 hours. How is that possible for such a simple append? I'm using the Table.Combine function in M. Both tables have the same columns, and I've enabled the Enhanced Compute Engine. What can I do to optimize this process? I'm using PPU

Avoiding setting incremental refresh on the computed table.

Thanks for any suggestion.

 

PS. My code is just:

 

Source = Table.combine(table1, table2)

 

Need Help

9 Replies

  • lbendlin's avatar
    lbendlin
    Icon for Super User rankSuper User
    1 year ago

    Do the tables have the same columns?  In the same order?  How many columns?

     

    It takes that long because your operation breaks query folding.  Both tables have to be ingested completely before they can be appended.

     

    What's wrong with incremental refresh?

    • Gabry's avatar
      Gabry
      Icon for Super User rankSuper User
      1 year ago

      Hi lbendlin ,

      thanks for your reply.

      Yes they have the same number of columns, also same names. There are 37 columns. Yes same order of the columns (why does this really matter?).

       

      Which operation breaks query folding? Append? Keep in mind that this transformation does not query the SQL source but operates on linked tables. generating a computed table. So does query folding still matters? I checked the dataflow's logs and under Compute engine it says cached and reduced.

       

      I do incremental refresh on the first dataflow that gets data from the SQL source but I would prefer to avoid incremental refresh on this computed entity because I need to do some transformation on it. As a last resort, I could implement it, but I would like to understand why it takes so long for a simple append and if there is a way to optimize it.

      • lbendlin's avatar
        lbendlin
        Icon for Super User rankSuper User
        1 year ago

        The Dataflow Power Query Editor should indicate the point where the folding breaks and why.

         

        Can you append them via "&" ?  (shouldn't really make a difference but worth a try)

         

        What if you change the append order, using the smaller table first?  

         

        Use a Table.Buffer on the smaller table.