Forum Discussion
Advocate IIIPower Query Performance
Super UserDAX 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.
Community ChampionAlexisOlson 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().
- smpa01
lbendlin thanks for this insight and I am trying to relate it to my case.
90% of my source data comes from SQL server. they are well indexed and I bring them using custom SQL queries. So in those cases do I understand, neither query folding has any relevance nor should I care about it?
I also did not understand what you meant by So if your source system is good at spooling but weak at custom queries - when can this happen in case of a SQL server,- lack of indexing, poor DB designing?
- lbendlin
Yes. Just because you are a good and diligent SQL server admin doesn't mean everybody else is. Oftentimes you have to access someone else's SQL server tables and views, and you have no visibility to their setup. In such cases spooling (and throwing stuff away later) may be your only viable choice.
