Forum Discussion
Slow initial load
Hello!
I've created a PowerBI report that I previously had some performance issues with. I've now corrected all of those performance issues and have < 7 seconds of waiting time on each report page. The problem however still exist when you enter the report for the first time in a while (a couple of hours is most of the time enough). The report is using DirectQuery for most of the pages.
I have another report that uses 100% import mode and that report is fast, even when I visit it for the first time in a while.
Is there anything I can do with my report to make it faster? It has a fast loading time in PowerBI Desktop and in PowerBI service in general, it's just that first initial load in PowerBI service that's slow.
17 Replies
Hi,
I have the same issue over here, since PBI Desktop august 2020 release.It has always worked fine since 201706 untill 20208.
My reports use a Direct Query connection to an Azure SQL database.
Since the latest PBI Desktop update of June 2021 things are getting somewhat better however the problem is in the Service.
I clear the cache of my browser and then open my report.
Every single initial page load (page or drillthrough) has some lag.
After I've opened all the pages, I close the browser en reopen it, clear the cache and do the same navigation.
Than all works fine, I tested it with other filters so that the queries executed on the Azure SQL db are different.
It's a strange behaviour.
In januari I had a call with Simon Didier and he mentioned the following:
"After a data model is upgraded to the new metadata format,
AS will have the M Engine request the native/folded query for legacy DQ sources at runtime
instead of the native query being cached in the data model from PBI Desktop.
Requesting the native query at runtime is slightly different than how PBI Desktop does it,
which can result in a successful conversion in PBI Desktop and a failure in the Service.
The reason for this is due to a technically incorrect configuration in PBI Desktop -
we should be setting the ThrowOnFoldingFailure flag in order to match the Service.
Setting the flag will have queries fail to fold in Desktop as well."Like I said the behaviour is better since june 2021 update but not 100%.
Thanks in advance
Frank
Hello,
I'm not using a gateway because my data lives in the cloud (Azure SQL database).
I think it has something to do with cold and hot model. When the model is loaded then initially it's execution time on the database is slow because the queries aren't being stored in cache.
When the model is loaded in memory and the queries are executed everything works fine until the model is offloaded.The thing is: it doesn't work the way it used to work.
Frank
Hi, I had the same problem with my Direct Query Reports.
You can solve this with the Value.NativeQuery:
let
Source = Sql.Database(Server, Database),
Data = Value.NativeQuery(
Source,
"
select column...
from tabel
",
null,
[EnableFolding = true]
)
in
DataI hope this helps.
Frank
Did you ever solve the issue? I have the same problem. Slow inital load on the Service (15+ seconds) (not on Desktop), then it works super fast. No DAX, very small dataset - I'm connected to Dataverse, and I'm pulling in 3 tables with <50 rows...
HI gustavjMC ,
You could open the Query Analyzer and see what is doing (or not doing). If you don't find anything obvious there, you could do a network trace (like with fiddler) to confirm that everything is communicating as expected.
I would appreciate Kudos if my response was helpful. I would also appreciate it if you would Mark this As a Solution if it solved the problem. Thanks!
Hi collinq ,
By Query Analyzer, do you mean the performance tool in Power BI that u can use to track the querys? Because I've already done that and analysed my querys in DaxStudio, everything looks ok there.
I can refresh the data very fast in power bi service so I can't really see in what way my querys could be the problem here. It's only that first initial buffer in the service that is slow.
I have exactly the same problem: a report with several pages, everything in direct query mode beside of 2 dimensions that rarely change (date and a static list). Opening the report the first time takes roughly 45 seconds, once it's loaded each click never takes more than 5 seconds to query the corresponding data and update it's visuals.
I also tried Power BI Query Cache to speed up initial page load, but: if someone loads a report the first time and closes the Browser window before the report has been loaded completely, he will see next time he opens up that page a messed up version of that report with lots of error messages. This needs to be fixed than by hitting manually the refresh button, which is a pretty bad user experience from my point of view.
Using Performance Analyzer in Power BI Desktop is also no option, since the report must be already fully loaded to get that feature working.
Edit: I noticed that the same report published on Power BI Reportserver (connected to an On-Prem SQL Server) takes much shorter to load than the same report published on Power BI Service. For sure a Power BI Data Gateway creates more delay that a direct On-Prem SQL connection, but I wouldn't expect such a massive difference.
Hi gustavjMC ,
To optimize report loading, you can try the following operations:
1. Check the refresh summary and the metric usage app to view the refresh time, allocate reasonably, and try to choose the idle time to refresh. The refresh time with centralized refresh time is divided equally, so as to avoid too many people occupying resources and causing queuing
2. Select the correct capacity. For those datasets whose data refresh time reaches 2 hours, place them in the premium capacity, and the maximum refresh time is 5 hours
3. Remember the refresh limit. When the source data source is frequently refreshed, choose the DQ / LC mode as much as possible, because the import mode will constantly refresh the data set of the import mode and occupy resources. However, DQ / LC will limit the number of rows of returned data to 100W, and the corresponding time limit for running query is 225 seconds, which is more effective for refreshing
4. Optimize the tables and columns used by datasets and reports or instruments, avoid using complex DAX functions and delete useless fields
5. Limit the number of visual objects on the dashboard rather than using RLS
6. Use a reliable on premises data gateway to prevent the report from being unavailable or overloaded and failing to refresh the report. It also ensures that the physical machine configured with the gateway is not far away from the physical machine using the gateway, so as to avoid the geographical location from affecting the speed
7. Go to capacity setting and select a capacity node. Look at the workload settings. Adjust the resource allocation of the dataset to a small extent and the resource allocation of the dataset to a higher level
This is the related content and video about the problem, hope to help you:
https://docs.microsoft.com/en-us/power-bi/guidance/power-bi-optimization
https://docs.microsoft.com/en-us/power-bi/guidance/report-performance-troubleshoot
https://www.youtube.com/watch?v=vbS7WuH6PO8
Best Regards,
Liu Yang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Thank you for the tips and tricks!
Unfortunately none of them works for me. The problem with my report isn't that it's generally slow because generally, it's quite fast. The problem for me only exists when I open the report for the first time of the day in Power BI service. It's fast in Power BI Desktop and fast in Power BI service when it's not the first time I visit it for the day (or atleast for at couple of hours).
It's really hard to troubleshoot this problem because it does as I said only occur when visiting the report for the first time in a while or directly after i published a new version of the report from Power BI Desktop.
Is there any visuals or something that is only downloaded when visiting the report for the first time in a while or where is this problem coming from?
HI gustavjMC ,
Yes, when you first open the report, it loads into memory and runs on your machine to get the data ready to use. This is standard. If it is slow during this part that means that you have either an incredibly massive dataset or you have inefficient queries or you have lots and lots of Power Query edits that it runs through, or you have a very slow and underpowered machine, or you have a slow network or any of the other things that myself or Anonymous suggested.
Once it is loaded, it will run fine and quickly.
If it really takes a couple hours to get ready to use then you need to re-evaluate all of these options to get this to be a more effective report. Or, you can just live with the fact that this is how long it takes.
I would appreciate Kudos if my response was helpful. I would also appreciate it if you would Mark this As a Solution if it solved the problem. Thanks!
Hi collinq
Thank you for your answer!
Just to be clear, it doesn't take a couple of hours for the report to get ready to use. When i say slow, I mean like 20 seconds.
EXAMPLE:
- I visit the report at 07:00 in the morning, the report takes about 20 seconds to load and then I can browse the report's all pages without significant loading time.
- I visit the report an hour later, 08:00 in the morning. The report is now fast even though it is updated with new values since last time i visited the report.
- I visit the report two hours later at 10:00 and the report is still loading very fast.
- I wait 7 hours and opens the report at 17:00, the behaviour of the report is now like it was in the morning. It's taking about 20 seconds to do an initial load and then I can browse and update the values very fast.
I hope this example makes it more clear what my problem really is 🙂
