Forum Discussion
Power Query Performance
In general I find Power Query performance to be very poor when building a medium complexity script. For example it seems really inefficient on joins (merges) and if I have a fact table that I want to join to multiple dimensions (to bring back the surrogate keys) it can take a very long time to refresh the data view when moving from step to step. Also getting row counts is very slow and this is something that is good practice to do before and after merges. It is frustrating when one knows how fast this would be in SQL.
What do other people think? Is there a live idea on https://ideas.powerbi.com/ideas/ ? I couldn't see one. Has anyone suggested using SQL as the language rather than M? Could this be done?
Thanks,
Ian
20 Replies
Ian_Stuart_Rupe I can understand the feeling. PQ has performance issues and the performance is nowhere near to SQL's performance.
PQ is largely used for data transformation. If you are bringing the tables from a SQL server and if you have an option, do all the transformation server-side and only use PQ to transport those tables.
If you have tables from multiple sources (e.g. SQL and sharepoint) it is recommended that you build a data model and use DAX to genrate the output.
DAX is blazing fast (if not as fast as SQL) and it will not disappoint you.
There are also known tweaks described here that makes significant difference to PQ's performance. But for large dataset I did not find PQ's performance satisfactory.
As and when I can go server side I do but when bringing data from files and wanting to create a dimensional model that is not an option. I had not thought of using DAX to create the surrogate key columns on the fact table. That could well be an option, thank you. I will raise a new idea to improve performance/ change the language too unless someone else knows of one that has a;lready been raised.
DAX might be problematic for creating key columns as you might run into circular dependencies if you then use these to create relationships on.
Power Query can be a bit hit and miss with performance depending on a number of factors including things like how well (or if) it's folding queries back to native SQL. The Power Query experts around here like lbendlin are well aware of the performance limitations and have been pushing for improvements, so they may have specific ideas to add your voice to.
Thanks for the flowers AlexisOlson but I'm no Power Query expert by any stretch. I am currently re-reading the utterly fantastic primer on Power Query by Ben Gribaudo
Power Query M Primer (part 1): Introduction, Simple Expressions & let | Ben Gribaudo
Ian_Stuart_Rupe I highly recommend you read the articles as well - all of them. You will be able to appreciate how Power Query actually works, what it is good at (hint: Counting rows is definitely not on the list, quite the opposite) and what differentiates it from other languages like SQL. The primer has an extensive section on keys and indexes and their influence on joins.
Here's a great video on the topic: How Power Query Thinks: Taking the Mystery Out of Streaming and Query Folding (Video) | Ben Gribaudo
Hi Ian_Stuart_Rupe ,
Could you tell me if your problem has been solved?
If it is, kindly Accept it as the solution. More people will benefit from it.
Or you are still confused about it, please provide me with more details about your problem.
Best Regards,
Stephen TaoNot solved. It seems that performance is a known issue.
Thanks everyone for the posts, very helpful and I have wached the video lbendlin and I am reading the other articles to see if there is anything that I can do to improve performance. At the moment, as query folding is not an option with file sources, I think any gains will be minimal, but we shall see.
Hi all, I have voted for an existing idea at https://ideas.powerbi.com/ideas/idea/?ideaid=03e29a3d-c483-42a3-9860-1e31b028d715 If you agree could you vote and sppread the word? It needs votes.
