Forum Discussion
Memorable MemberPower Query engine performance issue
Without seeing your query code, it's a bit difficult to diagnose for sure, but given the size of the data, I'd recommend buffering the table after loading and before any transformations like pivoting. Scroll down to the Buffering section in this article for some more detail about the Table.Buffer function.
Other posts related to buffering:
https://community.powerbi.com/t5/Desktop/Using-Table-Buffer/td-p/1535407
ImkeF has a nice list of various recommendations for improving query performance.
I'd consider anything that can comfortably fit into your RAM to not be a large table.
As far as buffering, I suggest buffering right after the API stuff so that any further transformations don't attempt to trigger API calls again. You could do this at this step:
rt_table = Table.Buffer(Table.AddColumn(type_change0, "RT_DATA", each getRTData([handleID], token), type record)),If this table fits in memory nicely, then any subsequent basic transformations should be pretty fast.
Hi AlexisOlson - I am wary of this suggestion because Table.Buffer may not like nested Tables or Binary objects. I have seen this happen with Dataflows.
Element115 - it will help to buffer before running the very expensive Table.Pivot function.I glad it starting to help. One thing that can slow the performance is the API Throttling Limits. You should check how many send and receives you can make per second or minute.
There are two other things would try, but this will depend on whether original data and API data must fully updated each time.
- try an incremental load using the LastReportTime to avoid reload all records each week.
- if ID_External and Handle_ID are not unique then try running them in a distinct batch to avoid using the API to call the same data more than once.
Buffering loads the table to memory as it exists at that particular step. Deciding when and where to do this is more of an experimental art rather than an exact set of rules to follow, especially without a deep understanding of exactly how the query optimization engine works.
Just because you have some version of the table loaded into memory doesn't mean that there's never a need buffer again after that point. If you do expensive calculations or extensive transformations on a table, sometimes it's worth buffering those intermediate results before doing any further steps so that you have those calculations/transformations stored in a format that can be referenced efficiently.
A rather extreme example is this function I wrote here. As ImkeF points out, after I've done some initial transformations to set up some chunks to loop through, buffering them to memory helps a lot since it's doing nested iterations on those chunks. Only buffering the initial input wouldn't be nearly as fast.
It's possible that buffering both before and after the pivot is the fastest but that's something that needs to be tested in your specific situation. Don't go too crazy with buffers though. Take them out anywhere they don't help.
Element115 -
For 1 - it depends, as AlexisOlson says it is experimental. However there is one firm rule that you should follow. If you are connecting to foldable datasource like a database, don't buffer until after Query Folding breaks. Buffering at the very start would break folding and effectively load the entire table to temporary memory.
For 2 - Firstly, I believe Pivot is the more expensive transformation. Second, I would want Power Query to full complete all the steps before starting the Pivot transformation. Hence, my strategy would be to place it before Pivot. However, testing might show that there is very little impact from using either approach.
Memorable MemberHi Alexis, I changed the code slightly by adding a Table.StopFolding right at the beginning:
let
remove_cols = Table.StopFolding(
Table.RemoveColumns(
#"Renamed Columns",
{
"ID",
...
}
)
),
...
in
query
and at the end of the M srcipt, where I used to have one Table.Buffer call, I wrapped to steps in Table.Buffer with options = BufferMode.Eager, and this accelerated the execution of the M code quite a bit--no more waiting more than 1 hour.
However, changing the data type on 1 or more columns seems to take forever though. Again, 1,000 rows and let's say 100 columns, so 100,000 data points.
Super UserI'd consider anything that can comfortably fit into your RAM to not be a large table.
As far as buffering, I suggest buffering right after the API stuff so that any further transformations don't attempt to trigger API calls again. You could do this at this step:
rt_table = Table.Buffer(Table.AddColumn(type_change0, "RT_DATA", each getRTData([handleID], token), type record)),
If this table fits in memory nicely, then any subsequent basic transformations should be pretty fast.
Hi AlexisOlson - I am wary of this suggestion because Table.Buffer may not like nested Tables or Binary objects. I have seen this happen with Dataflows.
Element115 - it will help to buffer before running the very expensive Table.Pivot function.- AlexisOlson
Yeah, I'd be wary of trying to buffer anything that isn't a flat table too. I've not tried buffering nested objects.
- Element115
That's a good point. getRTData() does return a record with one key and a list of records as a value for each row.
At the moment, each Table.AddColumn (where getHandleID() and getRTData() are called respectively) is buffered.
As I write this, the processing completed without errors in 25 mins. So I'll take that as a pleasant improvement. If it could be done in 5 mins or less, that would be awesome... but perhaps not possible.
- Element115
Right, I forgot to mention it... I did that but all the type casting transformations that come after still seem to take forever, as in more than 10 mins.
Does this mean that the subsequent Table.Buffer calls to wrap intermediary steps at the end of the script to buffer the new table after each transformation is unnecessary? Such as this one:
#"Pivoted Column" = Table.Buffer( Table.Pivot(type_change2, List.Distinct(type_change2[Key]), "Key", "Value"), [BufferMode = BufferMode.Eager] ),Thanks again.
- AlexisOlson
You shouldn't need buffers for each step but it's worth trying on steps that seem computationally expensive.
It seems odd that type casting on a buffered table is causing a major slowdown unless the transformation is throwing errors. Error handling can definitely slow things down. You could try adding a new logical custom column instead of replacing values in [CLO Enabled]. It could be as simple as
[CLO Enabled] = "True"but you could try writing a more robust version if needed
try if [CLO Enabled] = null then null else if [CLO Enabled] = "Yes" then true else if [CLO Enabled] = "No" then false else null otherwise null- Element115
I agree... strange that would I have to define a custom func to do the Replace value thing.
- Element115
This is what my system perfs look like as the PQ engine is still processing the script. So the sys is not overwhelmed by any stretch of the imagination (total RAM = 64GB).