Power BI Service is really slow compare with Desktop

[LuckyMu]

Hi guys,

I have a specific dashboard on PBI services and it's refreshed manually by the users, sometime it takes an hour to finish refreshing. 

So at the beginning I thought the source query (connect to SQL Server and a spreadsheet on sharepoint) is slow but when I refresh the same report at the same time on PBI Desktop, it takes only few seconds, maximum 30 seconds.

For example, I refreshed on Desktop at 13:10 and it took 10 second, but as you can see on the screenshot above, it takes more than half hour( from 12:47 to 13:21) to complete.

This issue happens all the time, but very random, 10 times a week or sth like that.

Gateway is the latest version, can anyone explain what's the issue or what can I do to find the root cause?

Thank you

[Anonymous · Accepted solution]

Hi  @LuckyMu ,

After the refresh request is sent from power Bi services, the formed query request is scheduled by azure service bus, and azure service bus will also operate some other queries, so some queries will be broken until azure service bus opens permissions for them, which may take a lot of time

This is the link of related content. I hope it can help you：

https://docs.microsoft.com/en-us/azure/service-bus-messaging/service-bus-messaging-overview

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.

[LuckyMu]

Hi Liu Yang,

Thank you for your reply.

So you are saying that even manually refresh would go through Service Bus as well? I thought only the scheduled would.

If this is the issue, any suggestions to fix it? please note this is the only dataset(out of 30) is having this issue occasionally. 

Thank you

[Anonymous]

Hi  @LuckyMu  ,

Manual refresh will also be done through services bus 

Suggestions:

1. Use a reliable gateway to prevent report refresh failure due to unavailability or overload, and ensure that the physical machine that configures the gateway is not far away from the physical machine that uses the gateway, so as to avoid the impact of geographical location on the speed

2. View the refresh summary and metric usage app to check the refresh time, make reasonable allocation, try to choose the idle time to refresh, and divide the refresh time which is centralized with the refresh time equally, so as to avoid the resource occupation caused by too many people and queuing 

This is a related document,hope it can help you

https://blog.crossjoin.co.uk/2019/12/02/testing-performance-of-power-bi-reports-in-the-browser-part-...

https://docs.microsoft.com/en-us/power-bi/connect-data/service-gateway-deployment-guidance

https://docs.microsoft.com/en-us/power-bi/guidance/power-bi-optimization

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.

[Anonymous]

HI @LuckyMu ,

Please check similar thread- https://community.powerbi.com/t5/Service/Power-BI-Service-Incredibly-Slow/m-p/1037581

[LuckyMu]

Hi Nikhil,

Thank you for you reply but that thread was talking about a totally different issue.