A really useful new Power Query performance enhancement was added to Power BI Desktop in an update to the May release via the Microsoft Store a week or so ago (if you’re not installing Power BI Desktop through the Microsoft Store you’ll have to wait for the June release I’m afraid). You can read the documentation here:
https://docs.microsoft.com/en-us/power-bi/create-reports/desktop-evaluation-configuration
However if you have just read the docs you may be wondering what these two new registry key settings actually do. In this post I’m only going to talk about one, MaxEvaluationWorkingSetInMB; I’ll leave ForegroundEvaluationContainerCount for a future post.
At various times in the past I have blogged about how, when you run a Power Query query, the query itself is executed inside a separate process called an evaluation (or mashup) container and how this process has a limit on the amount of memory it can use. Some transformations such as sorting a table, doing a group by, pivoting and unpivoting require an entire table of data to be held in memory and if these operations require more memory than the evaluation container is able to use then it starts paging and query performance gets a lot worse. This post provides more details:
Two things have now changed though. First of all, the default of amount of memory available to an evaluation container in Power BI Desktop has been increased from 256MB to 432MB. This on its own will make many Power Query queries run a lot faster. Secondly, it is now possible to define how much memory an evaluation container can use yourself via the new MaxEvaluationWorkingSetInMB registry setting described in the documentation.
Here’s an example that shows how much of an impact this can have. In Power BI Desktop I created a Power Query query that reads data from a csv file with around one million rows in it and then sorts the resulting table by the values in one column:
let
Source = Csv.Document(
File.Contents("C:\demo.csv"),
[
Delimiter = ",",
Columns = 16,
Encoding = 1252,
QuoteStyle = QuoteStyle.None
]
),
#"Sorted Rows" = Table.Sort(
Source,
{{"Column2", Order.Ascending}}
)
in
#"Sorted Rows"
Using SQL Server Profiler in the way described here, I found that the Power Query query took almost 87 seconds to start returning data and a further 19 seconds to return all the data:
What’s more, in Task Manager I could see that the evaluation container doing the work was limited to using around 423MB of RAM:
I then used Regedit to set MaxEvaluationWorkingSetInMB to 4096, giving each evaluation container a maximum of 4GB of RAM to use:
After restarting Desktop I reran the same query. This time Task Manager showed the evaluation container doing the work using around 1.2GB of RAM:
…and Profiler showed that the query started returning data after only 14 seconds and returned all the data in a further 12 seconds:
As you can see, that’s a massive performance improvement. Before you get too excited about this, though, a few things need to be made clear.
First, this setting only affects the performance of Power Query queries in Power BI Desktop. It does not affect the performance of queries in the Power BI Service, although there is another setting that (I think) will have the same effect for queries that go through an on-premises data gateway – but that’s yet another for a future post. So while this will make development much quicker and easier it won’t make dataset refreshes in the Power BI Service quicker.
Second, you need to be very careful when changing this setting. There’s no safety net here – you can set MaxEvaluationWorkingSetInMB to whatever value you want – and so some care is needed. When a dataset is refreshed then multiple evaluation containers may be used to handle the Power Query transformations, each of which can use the amount of memory specified by MaxEvaluationWorkingSetInMB. Since there’s a finite amount of memory on your development PC it’s important you don’t set MaxEvaluationWorkingSetInMB too high because if you do there’s a risk that Power BI will try to use more memory than you have available and bring your PC to a grinding halt. What’s more there’s no way of knowing how much memory any given query will need without some experimentation, so my advice is that if you do change MaxEvaluationWorkingSetInMB you should only increase it by a small amount and then increase it only if you are sure you need it.
I’d love to hear how much changing this setting improves the performance of your queries. If it does prove to be useful to a large number of people I hope we can get it added to the Options dialog in Power BI Desktop (which is much more convenient than changing a registry key); I also think it would be very useful in Excel Power Query. Please leave a comment with your findings!
We desperately need to speed up the gateway processing. Assuming we have ample memory available in all gateway cluster VMs (32GB) what is the registry setting for the gateway? Pretty please?
Let me double-check a few things and then I’ll write a post.
+1 on Lutz request here – we also desperate to get gateway processing up. Power Query import method take significantly longer to process than native SQL query (i.e. writing sql queries to obtain data) using gateway (don’t see the same difference using desktop).
Lutz: Try changing the StreamBeforeRequestCompletes to ‘True’ in gateway config file. That cut our processing time for large models in half, at least.
Hey Chris,
Did you get a chance to look at how we can adjust the Memory on Power BI Service?
Chris, any plans for that gateway performance tuning blog post?
Not yet, sorry…
Is this possible to do in Excel Power Query?
That is very good. I have a SQL / ETL / Qlikview background and could never understand how a product so slow doing basic data transformations ( like sort or merge ) could ever be successful. I almos thought it was on purpose ( make power query slow, so people have to buy powerful SQL servers ). Hopefully this change will make PBI more “independent”.
Thanks, Lucas
Performance has a lot of factors involved. A few big ones to check are
[1] Is the query folding?
[2] How many queries are you using? Using ‘reference query’ in PPBIDesktop does not cache queries. (Dataflows can).
[3] Are you following Power Query best practices?
[4] For excel sources, make sure you set your table’s primary key column. If you use “table.distinct” it will add the[m]
[5] Here’s a list of SQL queries that are easier to fold
[6] Some actions can unexpectedly create extra work
I wonder how is it possible that I do not find these registry keys in my Registry Editor.
You need to add them if they aren’t there already
is there some place that shows how to add them?
I can’t find them either, maybe because I’m using the PBI Desktop App from Microsoft Store? Will a manually added Registry Key affect the query behaviour, or is there different way to control it for the Store App?
If they don’t exist you will need to add the keys manually. I can confirm that manually-added keys do affect query behaviour in the Store version of Desktop, although as I said you may need to make sure that you’re using the latest version.
Add the key manually, then run another dataset refresh and check in Task Manager if the mashup container task now consumes more memory than the default limit mentioned in the article.
You may also need to restart Desktop after doing this, although I’m not sure if this is necessary
I can confirm that it works for the Store App with a manually created key. I set the size to 3 GB on my 4 Core 16 GB Machine and the result is awesome! A local full outer join on two tables with 250k rows used to run several minutes, it finishes now in less than 20 seconds. Thanks a lot!
Great news, thanks for letting me know!
Chris, this is wonderful information. Can the same regedit be done to Excel and get the increased benefits?
No, sorry
I’ve just submitted something to address this via the excel feedback portal. As the audience for this is probably pretty niche, it would be great if people could head over and vote.
https://feedbackportal.microsoft.com/feedback/idea/feb39923-9e65-ed11-a81b-000d3a045ff7
In the mean time I am copying and pasting between Excel and Power BI query editor so that I can take advantage of the increased performance during development as this reduces my iteration time. Once it is working I generally care (slightly) less about how long it is taking to execute in excel as it is generally much faster than whatever manual / formula based processes were being used before.
Great new and very useful. I hope it will come Excel Power Query soon
@Chris – All that is need to improve the performance of PQ in PBI Desktop during Development is a setting called Calculation mode = Manual – where by PQ does not go back to the data source every time you modify a step and just works of the cached 1000 rows in the Preview.
Cheers
Sam
@Chris, thanks, that is exactly what I need with my complex dataset.
At first, it didn’t work after creating these registry settings. Then I noticed, that I found that registry path “Power Bi Desktop” at “Computer\HKEY_CURRENT_USER\SOFTWARE\Microsoft\Microsoft Power BI Desktop” and put those registry settings there… with no effect. Maybe this path stems from an earlier non-Store installation.
Beneath “Computer\HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\” on my machine, no “Microsoft Power BI Desktop” key did exist. After creating it and creating those new settings there, everything works as described!!
One further question: how do these settings related to the setting “Enable parallel loading of tables” in the Options-Dialog of Power BI?
Best
Torsten
I don’t think it is related. By the way, those settings (parallel load) are pretty useless as they are ignored on the service – the service always processes the refreshes in parallel even if you don’t want it to.
Hi Chris, thanks for such an interesting topic. I run a few tests with this starting settings:
– the options “Allow data previes to download in the background” and “Enable parallel loading of tables” were both unchecked, as usual
– my machine has an i7 CPU and 16 GB of RAM
– the PBIX archive is 23 MB big, the biggest table is just 4 M rows, there are about 20 queries, with some joins. All cached (not Direct Query)
– normally the refresh takes long time, but this time I couldn’t measure before starting the test
– other processes were running in the while (heavily Chrome)
Duration of refresh for each value of MaxEvaluationWorkingSetInMB:
1) 432 MB –> 43′
2) 4.096 MB -> more than 1 hour
3) 8.192 MB –> 40′
I guess I should repeat more consistent tests, probably with a very tiny data model.
Thanks
Thanks for this – I wonder if reducing the value of ForegroundEvaluationContainerCount, which should allow you to increase MaxEvaluationWorkingSetInMB beyond 8MB, will increase overall performance?
Hi Chris,
You wrote “8MB” in your reply. Just for sake of clarity, in the results above I meant
1) 432 MB
2) 4 GB (not MB)
3) 8 GB (not MB)
May be I misuse the “.” instead of “,”, due to the different use in my country. I apologize for it.
The number of MashUp instances spanned from 4 to 7.
I’m going to set the ForegroundEvaluationContainerCount to different values and test again.
Thanks a lot
Ah yes, sorry, I should have realised. If you have 20 containers and 16GB of RAM, remember the docs say that the maximum amount of memory that can be used is 16GB/20 – although I suspect there are other factors in play here too.
in Power BI settings it limits my computer to up to 2GB, but I have 32GB of RAM. I’ve tried configuring it in Regedit but I’m still limited up to 2GB. I don’t understand why I can’t use more than 2GB, where do I unlock this limit?
The only thing that worked was changing the number of containers ForegroundEvaluationContainerCount
Hello!
What about CPU? is there a way to increase the % of use per container ? I have the impression that there is a limitation of 10% per container. Thanks!
Can you make the same thing in Excel Power Query? I am not able to find the setting.
No, you can’t do the same thing in Excel yet