Forum Discussion
Buffer a table to improve performance
Hi,
I read a few articles about how to buffer a table, but i am not sure of what i am doing, neither if it really helps. Here is what i have done so far, including the buffer query. Could you let me know if the buffers are doing anything good or wrong ? If wrong, how should i use the Buffer query to improve the performance of my query ? It takes 20min to refresh given it sucks all the "Iwi" lists (all the same) from a lots of sharepoint sites from a Project Online app.
Thanks
JL
let
Source = OData.Feed("https://xxxxxxx.sharepoint.com/sites/pwasandbox/_api/ProjectData"),
Projects_table = Source{[Name="Projects",Signature="table"]}[Data],
#"Selected Columns" = Table.SelectColumns(Projects_table,{"ProjectId", "ProjectName", "ProjectWorkspaceInternalUrl"}),
#"TableBuffer1" = Table.Buffer(#"Selected Columns"),
#"AllListData"=Table.AddColumn(#"TableBuffer1","Iwi",each GetList([ProjectWorkspaceInternalUrl],"Iwi")),
#"RemovedErrors"=Table.RemoveRowsWithErrors(#"AllListData"),
#"TableBuffer2"=Table.Buffer(#"RemovedErrors"),
#"Expanded Iwi" = Table.ExpandTableColumn(#"TableBuffer2", "Iwi", {"IwiGroupName", "InterestValue", "DateOfAnswer", "Comments"}, {"Iwi.IwiGroupName", "Iwi.InterestValue", "Iwi.DateOfAnswer", "Iwi.Comments"})
in
#"Expanded Iwi"
You aren't using it wrong, but it only help performance in some cases. It doesn't buffer the table in memory for the next step. It techinally isolates it from external changes. So, a great use for this is to use it after sorting data. It is possible subsequent actions could cause the sort to change. It can be a huge performance booster for lists. I use List.Buffer() all of the time but lists are pretty small and are used differently.
If you aren't getting good performance gains, it means it isn't really helping in your scenario, and you certianly don't need to use it twice in a query - unless it is for a specific purpose, like protecting a sort.
2 Replies
You aren't using it wrong, but it only help performance in some cases. It doesn't buffer the table in memory for the next step. It techinally isolates it from external changes. So, a great use for this is to use it after sorting data. It is possible subsequent actions could cause the sort to change. It can be a huge performance booster for lists. I use List.Buffer() all of the time but lists are pretty small and are used differently.
If you aren't getting good performance gains, it means it isn't really helping in your scenario, and you certianly don't need to use it twice in a query - unless it is for a specific purpose, like protecting a sort.