Let's say, i have one huge table "A" that I like to use as a base table/query for 10 other queries that add their own transformations.
My understandig ist that "Reference" helps the developer to avoid having to update multiple instances of the same script. But when refreshing the dataset the engine builds one script for every query that is loaded into the model. It seems not cache the results of the queries that are referenced. That's very bad in terms of performance!
Any help is greatly appreciated!
Solved! Go to Solution.
If you use query duplicate against your database, the server has to run that query 10 times. However, if you use query reference, the server only run it once, and the query would be loaded into Power BI further transformations. If you think your database server can handle 10 large queries, it would be the best option to do so since Power BI transformations can be slow with large datasets
@NickNg278: You seem to have the same expection like I had. But, no, it's true: PQ will NOT load the base query ONCE, but as many times as there are queries that reference it +1 (itself). You can verify that fact by setting up a base query and a query that refernces it using an SQL Server database and then watch the Activity Monitor during dataset refresh: You will see TWO identical queries being run. Additionally the loading speed will plummet. It's horrible...
@v-jiascu-msft: Thanks for the confirmation that this is "as designed". I had hoped that there will be a M command that perhaps allows caching a query that I don't know. However there seems to be none and I have to look for a way around - Direct Query will certainly be a way to consider.
I found a very detailed explanation here, that helped a lot.
There IS a command Table.Buffer to cache results and that could help to solve the problem at least in some cases.