Forum Discussion
Slow Report Refresh
Hi Anonymous,
Is there any advanced operation in your query tables?(e.g combine, append, reference other query, calculate with external query table, custom function)
If this is a case, it will cause additional cost on calculating with these reference queries.(each row will loop calculation with reference table, it will increase memory usage and calculation time)
Maybe you can try to use List.Buffer or table.Buffer to cache these reference query to memory to reduce additional resource spend.
Reference link:
How to Improve Query Reference performance for large tables
Use of Table.Buffer in references
Regards,
Xiaoxin Sheng
Thanks @Xiaoxin,
I do have a number of merges and appends as well as a number of queries that reference each base query (25 total queries against 9 data sources). I thought about Table.Buffer, but a lot of the reading I've done on that seems to indicate that it's kind of hit or miss.
Is there a good way to determine empirically whether Table.Buffer would help? FWIW, I'm just an intermediate knowledge user at this point (but looking to learn!); relatively simple methods that aren't too time-consuming (reporting is a secondary function for my role) would be a big help.
HI Anonymous,
>>FWIW, I'm just an intermediate knowledge user at this point (but looking to learn!); relatively simple methods that aren't too time-consuming (reporting is a secondary function for my role) would be a big help.
Actually, it not such complex to understanding, you only need to use buffer function to package your advance calculation steps into buffer functions.
Please take a look at following blog, it show how to use buffer functions:
Buffer() M Function in Query Editor (Power BI)
Regards,
Xiaoxin Sheng
Thank you for the link Xiaoxin...
I'll review it further later on for future reference, but for at least this report, I've generally solved my issue by using DAX calculations to replace the majority of queries. I've still got a few items remaining, but I reduced the most problematic query to about 2/3 of the steps previously used, particularly removing a couple of merges to speed things up. I've reduced the desktop time from 5-15 minutes to 35secs to 1 min (I haven't published this iteration yet to see the impact at the service level). My guess is that even with the remaining items (which are the most difficult items to work with), I'll probably end up with <10 minute maximums at the service (trying to be really conservative).