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 MemberI have implemented all your suggested changes and execution is now screaming!!! Alright, I exaggerate but I'll take 15 mins over 60+ mins anytime of the week. This project has been the best accelerated learning on how to use M. Seriously, thanks a lot.
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.
- Element115
Unfortunately, due to the problem domain, all 1,000 records need to be queried daily multiple times. This is supposed to be a 'real-time' API, which technically it is not, but it's as close as it gets to providing the most up-to-date status of a 1,000 devices. So the ETL has to run multiple times a day. Thankfully, all IDs are unique, new handleIDs (which are just UNIX timestamps) being generated continuously by the API provider.
- Element115
Anonymous AlexisOlson There is one last thing I am confused about re where and how many times Table.Buffer should be used.
1__In other words, why not just buffer at the very top of the code like so:
let Source = some_DB, transform0 = Table.Buffer( Source ), ... last_transform = ... in last_transformWould this way make all subsequent transformations use the buffered table? And so accomplish the same gains as when buffering multiple subsequent transformation steps?
2__Last question: In the 2 code snippets below--only the first 2 lines are different--will Table.Buffer have a different impact or will it be the same? My understanding is that every step that follows the use of Table.Buffer will operate on the in-memory table. So whether step type_change2 is buffered or the step immediately following, ie Table.Pivot, it makes no diff because all the steps after these 2 will use the in-memory table. Is that not so? And if that is so, then why not just use Table.Buffer once, at the very beginning of the script?
#1 code snippet type_change2 = Table.Buffer( Table.TransformColumnTypes(#"Filtered Rows",{{"Key", type text}, {"Value", type text}}), [BufferMode = BufferMode.Eager] ), #"Pivoted Column" = Table.Pivot(type_change2, List.Distinct(type_change2[Key]), "Key", "Value"), #"Changed Type" = Table.TransformColumnTypes(#"Pivoted Column", ...), #"Replaced Value" = Table.ReplaceValue(#"Changed Type", ...), #"Replaced Value1" = Table.ReplaceValue(#"Replaced Value", ...), #"Replaced Value2" = Table.ReplaceValue(#"Replaced Value1", ...), #"Replaced Value3" = Table.ReplaceValue(#"Replaced Value2", ...), #"Changed Type1" = Table.TransformColumnTypes(#"Replaced Value3", ...), #"Extract Post Delim" = Table.TransformColumns(#"Changed Type1", ...), #"Replaced Value4" = Table.ReplaceValue(#"Extract Post Delim", ...), #"Renamed Columns1" = Table.RenameColumns(#"Replaced Value4", ...) in #"Renamed Columns1"vs
#2 code snippet type_change2 = Table.TransformColumnTypes(#"Filtered Rows",{{"Key", type text}, {"Value", type text}}), [BufferMode = BufferMode.Eager] ), #"Pivoted Column" = Table.Buffer( Table.Pivot(type_change2, List.Distinct(type_change2[Key]), "Key", "Value") ), #"Changed Type" = Table.TransformColumnTypes(#"Pivoted Column", ...), #"Replaced Value" = Table.ReplaceValue(#"Changed Type", ...), #"Replaced Value1" = Table.ReplaceValue(#"Replaced Value", ...), #"Replaced Value2" = Table.ReplaceValue(#"Replaced Value1", ...), #"Replaced Value3" = Table.ReplaceValue(#"Replaced Value2", ...), #"Changed Type1" = Table.TransformColumnTypes(#"Replaced Value3", ...), #"Extract Post Delim" = Table.TransformColumns(#"Changed Type1", ...), #"Replaced Value4" = Table.ReplaceValue(#"Extract Post Delim", ...), #"Renamed Columns1" = Table.RenameColumns(#"Replaced Value4", ...) in #"Renamed Columns1"- AlexisOlson
Super User
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
Anonymous AlexisOlson
I removed one buffer from one intermediated steps and removed the buffer also from both Table.AddColumn calls, and instead of buffering Table.Pivot, moved the buffer to the step immediately prior. That gained 2 minutes. Actually, the refresh time in the Service is 9-10 mins now vs 10-12 mins before these changes. (15 mins when running on the Desktop). Interesting and .... strange.
1__One last thing I am still not clear on is this:
By 'breaking folding', do you mean something like this? Using Table.StopFolding immediately as the step after all the DB related calls are done? I currently have it like this: let Source0 = Sql.Databases("DB"), DB = Source{ [Name="DB_name"] }[Data], dbo_vOLC = DB{ [Schema="dbo", Item="vOLC"] }[Data], stop_folding = Table.StopFolding(dbo_vOLC), type_change0 = Table.TransformColumnTypes(stop_folding, { {"ID_External", Int64.Type} }), #"Renamed Columns" = Table.RenameColumns(type_change0, { ..., remove_cols = Table.Buffer( Table.RemoveColumns( #"Renamed Columns",2__Finally, I am confused about this buffering business. For instance, let's say you use buffer once at the top. Would this not mean that all subsequent steps will be performed on an in-memory table anyway? I thought that that is what the PQ engine would do and hence I do not quite understand why multiple buffering is required. I mean, how else are the transformations computed if not in memory?
3__which makes me wonder... if some transformations can be done at the source, then PQ instead of doing them on a in-memory table will fold them to the source, say as native SQL?
4__but then, if the source table is huge (> 1 million rows), and can't all fit in memory, is it not dangerous to use Table.StopFolding as I did in the code snippet above? Wouldn't that prevent some transformations and thus never getting a final result that could be used in the model?
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.