Power Query - cache shared nodes
Update Power Query in Excel to take advantage of caching in cases where a parent node refers to a child node that has already been refreshed (as exists in Power BI desktop today).
This issue creates significant performance problems with refresh times when creating highly interdependent financial and operational models. This is a show stopper from a usability and customer acceptance standpoint.
Josh Blackman
shared this idea
Hi all,
I wanted to share with you that the new query caching mechanism in Excel has been deployed to Office Insiders starting from Excel version 1801 (build 9001.*). At this stage, we would like to allow some “baking” time as we monitor the feature health metrics.
In this scope, we need your help to ensure a quality release! I encourage you to try the following scenarios and share your feedback:
1. Run Refresh All on a complex workbook with multiple query dependencies. Does it work faster?
2. Run Refresh All on a complex workbook with multiple query dependencies. Do you see any issues with your data?
3. Refresh a single query several times. Do you see any issues with your data?
- The Excel Team
281 comments
-
Ed Hansberry
commented
This was rolled out early 2018 for office 365 and I believe was included in office 2019 when it shipped
-
Anonymous
commented
Hi, just checking if this is complete and rolled out. Is it?
-
Andrey Minakov
commented
Hi All! I watched trace log of the version, and have to say that a number of exceptions can be found in the logs.
All of them a happens when there are operations with files of different kind. So far I see exceptions while reading excel files and in case of using cash of web-requests. I think exceptions a thrown during using the cash, because, when I refresh a multi-step query in PQ UI, the exception happens only after the second launch of a function, which makes a web request. Again - all the exceptions can be seen only in trace logs, while you get correct data in result of request. BUT - these exceptions defenitely slow down the work - there are a number of them, and PQ ignores Table.Buffer when it see such exceptions (I can see that in the logs as well).
The error message is:
Exception:
ExceptionType: System.IO.EndOfStreamException, mscorlib, Version=4.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089
Message: Чтение после конца потока невозможно.
StackTrace:
в System.IO.__Error.EndOfFile()
в System.IO.BinaryReader.FillBuffer(Int32 numBytes)
в System.IO.BinaryReader.ReadInt64()
в Microsoft.Mashup.EngineHost.Services.DiskPersistentCache.PersistentDictionary.<>c__DisplayClass2c.<ReadBucket>b__2b()
в Microsoft.Mashup.EngineHost.Services.FileSystemAccessHelper.<>c__DisplayClass4.<IgnoringAccessExceptions>b__3()
в Microsoft.Mashup.EngineHost.Services.FileSystemAccessHelper.TryIgnoringAccessExceptions[T](Func`1 func, IEvaluationConstants evaluationConstants, T& result, Exception& accessException) -
Adam Bender
commented
I have noticed significant speed improvements since the changes recently. Using Win 10 64 bit, Office 365 64 bit, connecting to a MS SQL server.
Previewing or making changes to a table with 33M rows and around 50 columns was nearly impossible before, or it would take 5 minutes per step. Now it takes around 20-30 seconds to get the preview and only a few seconds per step.
Huge improvement.
-
James
commented
Just wanted to follow up on this to see if there has been any progress with this update? The last update from the Excel Team was early in July. I also see that there has been no updates to PQ for Excel 2013 since July; whereas PowerBI has received numerous updates. Will these recent updates be flowed through to Power Query?
-
Andrey Minakov
commented
But it would be definitely interesting to understand, why do you make 2 equal request, and not just 1? Of course is much better in comparison with the current standard release, where you make from 4 to 6 equal requests, but why you decided to make 2 instead of 1?
-
Andrey Minakov
commented
Guys, you definitely have improved web requests, thanks for that! At least from that point of view Excel now works better than PBI Desktop ;-). So now, if I make any number of references to a web query in Excel (loaded to a model, of course), there is NO any additional requests to a web source while refreshing the model. And in PBIDesctop I still have additional requests per reference. Just cool for Excel users!
-
Anonymous
commented
I think this is obvious to anyone using PQ along with PowerPivot, but let's just make this distinction really clear:
1- If you are not using PowerPivot, always make sure your table is not loading to the data model ("load to" window)
2- If you are using PowerPivot, make sure you ONLY load to the data model the queries that you absolutely need.In my case, with a large, complex data set, I made sure to create data model specific final queries where the last step was to "Remove other columns". That serves as a gatekeeper to stop any unnecessary columns from taking up space in my data model.
Another tip for PowerPivot users: download the PowerPivot Utilities add-in to measure the weight of each column, usage of your columns and metrics, etc. It is a fantastic tool for optimizations.
-
Anonymous
commented
To everyone that's unchecking the load to data model box; the reason it speeds up the refresh is because it's not importing the data to Power Pivot which of course has it's own downsides if you are managing millions of rows and want to generate relationships between the tables.
-
Neil Good
commented
I agree with Darrell Ripkowski below - having the Load to Data Model unticked does make it lad a lot faster. No idea why of course! As Laura says, Power Query is very good but I get the feeling MS do not want to go down this road.....
-
Laura
commented
Love the capability of Power Query, but I am a prisoner to the s . l . o . w . . . update time. Yes I have 5M rows, appending from 25 underlying tables, but it takes an unbelievable amount of time to update. I would abandon it all together, if it had not revolutionized the reporting capability to the Vice Presidents of the company I am contracted at. I am a consultant, so I must do what they ask, and not offer opinions about the time wasted. To them the result is phenomenal, no matter how long it takes. : (
-
Carlos Cortinas
commented
Hi Darrel Ripkowski, can you tell me please where a I found that Store button or checkbox, please?
thanks
-
Daniel Schmidt
commented
Nor have I; Office 2019 64-bit also appears to refresh referenced queries repeatedly.
-
MasterV
commented
I have not observed any significant improvement (Office 2019 64 bit)
-
Darrell Ripkowski commented
Well I was wondering why power query was slow myself and so I tried a couple things.
I finally found out the main cause of slowness what checking the box Store in data model while creating the table.
If I just loaded to table and unchecked the load to data model the refresh was drastically faster. I am talking from taking around 5 minutes and more and making excel almost unusable to less than a minute to load the new data.
The data I am refreshing are from 12 different files on a share point online server so it has a lot to gather.
-
Wally Wilinsky
commented
Just so the Microsoft team is aware... I ran an Excel Power Query test and Excel is connecting to that database and retrieving data much more than is needed.
Setup:
Excel 2016 64 bit
Workbook with multiple power queries to a data model
All data sources are from the same SQL database
Wireshark installed to monitor network traffic between my PC and the SQL database.Result:
For every query refreshed (either manually or through refresh all) Power did the following:
1. Queried the database and returned a result set of every table and view I had access to
2. Queried the database and returned a result set of every stored procedure and user defined function I had access to
3. Queried the database and returned the calling parameter for the user defined functions I was calling
4. Queried the database and returned the table structure of the result set my user defined function was going to return.
5. Queried the database and returned the primary key index names of a seemingly random list of database tables.
6. Queried the database and returned the version of SQL
7. And FINALLY executed the query it was designed to execute.My questions is WHY SO MUCH OVERHEAD??????? I already designed the queries. I understand getting some of this information when I open the query builder but this was a right click refresh on the query list in Excel. Is this the root of all the Power Query slowness in Excel?
If the query is already designed, why can't it just execute the query? If I don't have permission get me that error.
Microsoft PLEASE HELP!!!!!!! This is killing my user base.
-
Anonymous
commented
Sam - it could be your data model design.
-
Sam
commented
There is no significant improvement is speeds after the update
(Ver 1809 Build 10820.20006) -
Ed Hansberry
commented
@Adam - anyone with 1801. The only people that won't ultimately get it are those that purchased a perpetual Office 2016 license.
It will be in Office 2019 this fall too.
-
Adam Bender
commented
Hello,
In an earlier response you indicated that this was available for users with version 1801 and beyond.
Does that only apply to Office Insiders, or anyone with that version? I am on the semi-annual channel.
Thanks,
Adam