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 MemberHere is the code in full--comment at the very end explain the last step, a type cast, that caused the error, which went away once the type cast step was removed. Now: I am casting from text to logical using text values of 'False' and 'True'. Used to work in the past if I remember correctly.
let
source1 = Sql.Databases("DB_NAME"),
source2 = source1{ [Name="###"] }[Data],
dbo_vOLC = source2{ [Schema="dbo", Item="vOLC"] }[Data],
#"Changed Type" = Table.TransformColumnTypes(dbo_vOLC,{ {"ID_External", Int64.Type} }),
#"Renamed Columns" = Table.RenameColumns(#"Changed Type", { {"FK_ID_###", "ID_###"} } ),
remove_cols = Table.StopFolding( //Table.Buffer( //
Table.RemoveColumns(
#"Renamed Columns",
{
"ID"
, "ID_OLC"
, "Longitude"
, "Latitude"
, "Is_remotely_managed"
, "Model"
, "FirmwareVersion"
, "HardwareAddress"
, "ControlSystem"
, "InstallationDate"
, "CommunicationStatus"
, "NumberOfMeteringChannels"
, "CLO_Enabled"
, "LastReportTime"
, "Is_metered"
, "UserSwitchType"
, "FactorySwitchType"
}
)
),
project_id = fnProjectID(),
getToken = () =>
let
URL = "https://api.###.com",
body = "app_key=###&app_secret=###&service=###&scope=###",
response = Web.Contents(
URL,
[
RelativePath = "oauth/accesstoken"
, Headers = [
#"Authorization"="Basic ###"
, #"Content-Type"="application/x-www-form-urlencoded"
]
, IsRetry = true
, Content = Text.ToBinary(body, BinaryEncoding.Base64)
]
),
jsonResponse = try Json.Document(response),
token = if jsonResponse[HasError] then
error jsonResponse[Error][Message]
else
jsonResponse[Value][token]
in
token,
getHandleID = (external_ID as number, token as text) =>
let
URL = "https://api.###.com",
relative_path = "###" & project_id & "###" & Number.ToText(external_ID) & "###",
headers = [
#"Authorization"="Bearer " & token
, #"Content-Type" = "application/json"
],
external_ID_txt = try Number.ToText(external_ID),
http_resp = if external_ID_txt[HasError] then
error "cannot convert external_ID from number to text"
else
try
Web.Contents(
URL
, [
RelativePath = relative_path
, Headers = headers
, IsRetry = true
]
),
response = if http_resp[HasError] then error http_resp[Error][Message]
else try Json.Document(http_resp[Value])
in
if response[HasError] then
response[Error][Message]
else
try response[Value]{0}[handleId] otherwise response[Value][Message],
token = getToken(),
add_handleID = Table.AddColumn(remove_cols, "handleID", each getHandleID([ID_External], token), type text),
type_change0 = Table.TransformColumnTypes( add_handleID, { {"handleID", type text} } ),
getRTData = (handle_ID as nullable text, token as text) as record =>
let
URL = "https://api.###.com",
relative_path = "###" & project_id & "###",
handleID_num = try Number.From(handle_ID),
rt_data = if handleID_num[HasError] then
[ERROR="Number.From(handle_ID) cannot convert; hanleID=" & handle_ID]
else
let
headers = [
#"Authorization" = "Bearer " & token
, #"Connection" = "keep-alive"
],
query = [handleId = handle_ID],
http_resp = try
Web.Contents(
URL
, [
RelativePath = relative_path
, Headers = headers
, IsRetry = true
, Query = query
]
)
,
data = if not http_resp[HasError] then try Json.Document(http_resp[Value])
else error http_resp[Error][Message],
result = if data[HasError] then [ERROR=data[Error][Message]]
else if Value.Is(data[Value], type list) and List.IsEmpty(data[Value]) then [ERROR="no data: wait for the next server refresh"]
// the API returns a list, ie [] JSON array, when data is available
else if Value.Is(data[Value], type list) and not List.IsEmpty(data[Value]) then data[Value]{0}
// the API returns a record, ie {} JSON object, only if there was an error
else if Value.Is(data[Value], type record) then [ERROR=data[Value][Message]] else null
in
result
in
// returns a record of real-time values or an error msg record
rt_data,
rt_table = Table.AddColumn(type_change0, "RT_DATA", each getRTData([handleID], token), type record),
expand_data = Table.ExpandRecordColumn(rt_table, "RT_DATA", {"status", "DateTime", "properties"}, {"status", "DateTime", "properties"}),
expand_prop_list = Table.ExpandListColumn(expand_data, "properties"),
#"Expanded properties" = Table.ExpandRecordColumn(expand_prop_list, "properties", {"Key", "Value", "Unit"}, {"Key", "Value", "Unit"}),
#"Removed Columns1" = Table.RemoveColumns(#"Expanded properties",{"Unit"}),
#"Filtered Rows" = Table.SelectRows(#"Removed Columns1", each ([Key] <> null)),
type_change1 = Table.TransformColumnTypes(#"Filtered Rows",{{"Key", type text}, {"Value", type text}}),
#"Pivoted Column" = Table.Pivot(type_change1, List.Distinct(type_change1[Key]), "Key", "Value"),
#"Replaced Value" = Table.ReplaceValue(#"Pivoted Column","No","False",Replacer.ReplaceText,{"CLO Enabled"}),
#"Replaced Value1" = Table.ReplaceValue(#"Replaced Value","Yes","True",Replacer.ReplaceText,{"CLO Enabled"})
// and here was the step to cast this [CLO Enabled] column to type logical, after the addition of which I got the OLE DB type mismatch error
in
#"Replaced Value1"
Element115 - this is how I would approach this to see if it improves performance.
remove_cols - insert a Table.Buffer after this step. Please check this column is folding as the previous change of data type might break.
getToken - this can be moved to separate query/function. It is not necessary to include in this Query. There is potential issue with this step because we want to get one token for all subseqent requests. There is a risk that you are generating a token for each query. The latency for each token request will add up!.
Text.Buffer would be helpful, but sadly doesn't exist.
You could try to return the Json.Document with the getToken function (i.e return the jsonResponse step instead of text).
In the main query, just add the following:
BufferToken = Binary.Buffer( getToken() ),
Token = Json.Document( BufferToken )[Value][token],
This should force Power Query to get token once.
add_handleID - this steps refers to [ID_External] in the remove_cols, is this correct? This only the ID column. Or does the project_id step do something?
type_change0 - this step is unnecessary, but this is a good time for the second Table.Buffer so you know have the SQL table and the HandleID.
getHandleID - this could be moved to separate query/funciton. I am not sure about the complexity of the error handling. This approach may be chatty. I.e. to couple the steps it needs to execute the web call more than once.
#"Expanded properties" - add the 3rd buffer step after this step, so all the previous steps are completed before the Pivot.
Note the #"Remove Column1" can be avoided by excluding the Unit column.
- Element115
Hey thanks for the pointers! Good stuff. Couple of questions before I try all this out because I am not too familiar with M's execution model, so I am guessing a lot at what actually goes on under the hood.
1__remove_cols - insert a Table.Buffer after this step. Please check this column is folding as the previous change of data type might break. The code currently looks like this: stop_folding = Table.StopFolding(#"Renamed Columns"), remove_cols = Table.Buffer( Table.RemoveColumns( stop_folding, {"ID", ...} ) ), ... a bunch of func defs, token = getToken(), add_handleID = Table.Buffer( Table.AddColumn(remove_cols, "handleID", each getHandleID([ID_External], token), type text), [BufferMode = BufferMode.Eager] ), Are you saying I should add Table.Buffer as below? stop_folding = Table.StopFolding(#"Renamed Columns"), remove_cols = Table.RemoveColumns( <-- Table.Buffer() removed stop_folding, {"ID", ...} ), ... a bunch of func defs, token = getToken(), <-- remove getToken() fund def from this query add_handleID = Table.Buffer( Table.AddColumn(remove_cols, "handleID", each getHandleID([ID_External], token), type text), [BufferMode = BufferMode.Eager] ), - Element115
2__getToken - this can be moved to separate query/function. It is not necessary to include in this Query. There is potential issue with this step because we want to get one token for all subseqent requests. There is a risk that you are generating a token for each query. The latency for each token request will add up!. Given the variable assignement: token = getToken(), are you saying that because of lazy eval, getToken() will be called only when the variable token is referenced insided the getHandleID() function every single time Table.AddColumn() iterates over a row? - Element115
3__add_handleID - this steps refers to [ID_External] in the remove_cols, is this correct? This only the ID column. Or does the project_id step do something? Not correct. [ID_External] is one of the columns that remains after the remove_cols step. It is absolutely essential because getHandleID() needs to pass this ID to the API call in order to get back what the API provider calls a handleID. The handleID is necessary to retrieve the properties associated with it. (It is a UNIX timestamp that is generated according to some schedule and changes many time over the course of a day; basically a marker to track property changes over time.) The project_id variable simply stores a string that is returned by fnProjectID() and needs to be part of the URI when calling the REST API. It identifies the customer of the API provider. - Element115
4__type_change0 - this step is unnecessary, but this is a good time for the second Table.Buffer so you know have the SQL table and the HandleID. Before your comment, my code was like this (type_change0 was renamed type_change1): add_handleID = Table.Buffer( Table.AddColumn(remove_cols, "handleID", each getHandleID([ID_External], token), type text), [BufferMode = BufferMode.Eager] ), type_change1 = Table.TransformColumnTypes( add_handleID, { {"handleID", type text} } ), Are you saying the Table.Buffer() above is where it should be? (The code I originally posted in my the first post here has been updated to what you see in this post.) As regards to type_change1 step not being necessary: I added it because PQ was giving me some weird behavior when referencing the handleID. When the column handleID is created I could get an error message from the API instead of a handleID, therefore the column data type needs to be text. What happened was that PQ would treat the error messages as text, but the handleID would be treated as numbers! So PQ was mixing 2 different types in the same column. Therefore when calling the next REST endpoint with function getRTData() and passing [handleID] as a number, and converting it to text in order to append it to the URI, that would fail complaining it was not a number, if you can believe it. And if I changed the function param type to text to treat it as text, it would also fail. The only thing that fixed this was to perform an explicit cast to text on the whole column. No idea why this behavior, but there you have it. - Element115
5__getHandleID - this could be moved to separate query/funciton. I am not sure about the complexity of the error handling. This approach may be chatty. I.e. to couple the steps it needs to execute the web call more than once. Unfortunately yes, because the API server is very funky, going down at random without warning, or returning 403 or 500 errors and so on. The thing is totally unpredictable, so I need to make sure I catch anything and stored in the table so I know what happened. This makes sure that the scheduled refresh in the Service does not fail because of the API server and the other tables depending on the on-prem DB get their updated data and the user has access to at least the most important data in the report. - Element115
6__#"Expanded properties" - add the 3rd buffer step after this step, so all the previous steps are completed before the Pivot. Note the #"Remove Column1" can be avoided by excluding the Unit column. Is adding the buffer to Table.Pivot not necessary, as follows? #"Pivoted Column" = Table.Buffer( Table.Pivot(type_change2, List.Distinct(type_change2[Key]), "Key", "Value"), [BufferMode = BufferMode.Eager] ), Didn't know we could omit fields from records when expanding them to columns. Good to know!Hi Element115 - it is better to add before the pivot operation. After the operation is performed, there are couple of light transformation. As AlexisOlson explains, it is only help to buffer when you need to loop through and reference the previous step.
- Element115
I 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.