Forum Discussion
Understanding why Table.Buffer makes a difference in dependency chain
This thread contains a lot of information around how Table.Buffer works: https://social.technet.microsoft.com/Forums/en-US/34e454b5-3a18-4eef-b920-40703c93f390/tablebuffer-for-cashing-intermediate-query-results-or-how-workaround-unnecessary-queries-issue?forum=powerquery
Wow, that's quite some thread ImkeF ! I'll read through it several times today, and try to comprehend it all :-)
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?
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?
Amen to that as I have hit that wall as well just recently.
