Forum Discussion
Advocate IIIPower Query Performance
Advocate IIIAs 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.
- AlexisOlson
Super User
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.
- lbendlin
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
- Ian_Stuart_Rupe
Hi lbendlin I read (or at least skip read) all the articles. Some were new to me and very helpful. I did not see the artucle that addressed counting rows though?
smpa01Community Champion
AlexisOlson this seems like a good opportunity since you brought it up and this has been on my mind for a very long time.
If I am bringing all my tables from a SQL server and I am doing all the server side transformation through native SQL queries, is there a reason why I should care about query folding?
Please correct me if I am wrong, but the way I undertand query folding is if you are bringing tables from a server (take TSQL for example) and you don't write native SQL queries and rather transform through PQ syntax, as long as those syntaxs are comparable to a SQL equivalent transformation, the query folding will happen (e.g. Table.SelectColumns= SELECT a,b,c).
But if my whole query is a native SQL why query folding is important to me (query folding will not happen in the first place if you write a native SQL query altogether)?
- lbendlin
smpa01 If you hand craft your queries then Query folding is automatically disabled.
But there is another aspect to it - the aspect of cost. Why should I encourage query folding when my source system may be weak and unprepared (no indexes for example) while the PC or service has ample of memory and oomph to do the processing? So if your source system is good at spooling but weak at custom queries then you should not encourage query folding, and rather emphasise the use of Table.Buffer().
