Forum Discussion

JeffWeir's avatar
JeffWeir
Advocate V
7 years ago
Solved

Understanding why Table.Buffer makes a difference in dependency chain

I understand from Google that using Table.Buffer is often a good idea when multiple steps within a query will be referencing the same Table. But it seems from my experimentations that it's also a goo...
Need Help
Show and Tell
  • JeffWeir's avatar
    JeffWeir
    Advocate V
    7 years ago

    Wow, that's quite some thread ImkeF ! I'll read through it several times today, and try to comprehend it all :-)

    • JeffWeir's avatar
      JeffWeir
      Advocate V
      7 years ago

      Wow....putting Table.Buffer around any steps that referenced previous queries reduced my load time from potentially hours to one and a half minutes. (I say potentially hours, because I'd never dared to load all 30k rows of data for both Tables at once, nor attempted to make as many as 7 recursive steps. Even just loading one tenth of the data for 5 steps was taking a good 10 minutes or more).

       

      I take it that I want to buffer each input just once, as soon as it appears in the current query that is referencing previous ones?

      • JeffWeir's avatar
        JeffWeir
        Advocate V
        7 years ago

        This thread is also very enlightening: https://social.technet.microsoft.com/Forums/en-US/8d5ee632-fdff-4ba2-b150-bb3591f955fb/queries-evaluation-chain?forum=powerquery

         

        Say you have a query Q4 that references  Q2 and Q3. And say Q2 and Q3 both reference Q1. And say Q1 is pulling from a flat file. According to Erhen at Microsoft, if you're pulling from a flat file - and because File.Contents results aren't cached -  the flat file will be read 5 times! It gets read each time Q1 is directly or indirectly referenced: twice in Q4, once in Q3, Q2, and Q1. And I believe he’s saying that this is the case for either Excel or PBI.

         

        So…when doing any kind of complicated chain, the code produced from the UI just flat out sucks!  And that is an epic fail for a tool that is designed to be used by non-experts via the UI.

         

        I think a very low number of people facing performance issues because of this behaviour will be able to form a hypothesis about what's going wrong, find a thread on the web that explains the issue in plain English, then go wrap the first reference in any one query to other queries with Table.Buffer. And if you don’t diagnose the issue, you just think that PQ is a slow dog, and switch to using SQL.

         

        Anyone know if there a UserVoice request that addresses this specific behaviour? And whether this behaviour unaddressable now, given the architecture choices MS has hade?

  • pb296's avatar
    pb296
    Regular Visitor
    1 month ago

     @ImkeF 

     

    Please can you re-post the link to the article you have shared.  I have tried to access the link and the page can't be found