Forum Discussion
Advocate IIIPower Query Performance
Super Usersmpa01 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().
Community Championlbendlin 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.
- smpa01
lbendlin many thanks the picture is getting clearer. I can see the other perspective now.
So if I am querying a large tbl (let's say a 1M+ db tbl) exists in an inefficient SQL server , are you saying PQ will rather do the transformation faster (due to query folding aspect) than through native SQL query (poor db design) ? I am just scared that given PQ's poor performance aspect, I doubt whether it will do it faster (with query folding) than the non-indexd SQL server (inefficient server). I can take that and experiment against a non-indexd db large db table.
Even if that is true, must not one try to detrmine first how efficient/ineffcient the server is before deciding on native SQL or PQ (to Query folding to take place).
- lbendlin
That's not exactly what I am saying. I am saying that spooling can be a better option in such a scenario. Query folding would only make it worse, so you would need to suppress that, with Table.Buffer() for example.
- AlexisOlson
Most of my data come from SQL server too. I never use custom SQL queries within the Power BI queries but rather connect to tables or views that are already shaped like I want them. The idea is to keep SQL code stored and managed on the server and keep transformations in M fairly minimal for those tables. There's more M involved for sources like Excel or SharePoint where I can't really push the processing upstream.
I often do exploration and rapid prototyping with the Query Editor since it has such a nice GUI / coding balance and allows mashing together different sources so easily. But when moving to production, it's more of just a connector to data sources rather than a transformational layer.
Note: I'm not making any value claims as to best practice here. Just sharing my personal experience.
