Forum Discussion
Advocate IData Refresh takes a very long time
Hello all,
My apologies if this subject has been already on this board, I searched for a solution for a while and couldn't find the right answer.
I've built a rather complex model that combines several data sources: SQL server, MySQL, Google big-query and one excel file. In order to create relationship between the tables, I've created several merges and appends. unfortunately these couldn't be avoided.
In general, my model contains a reasonable amount of rows, approx. 15 million. I've gone through this article and applied every step that I could within the limits of my model to increase performance.
During refresh, the row count on the refresh window reaches the maximum rows on each table pretty fast, but then gets stuck on that display for a very long time, about one-two hours.
I also went through all the steps applied on the query view to try and figure out what causes this, with no luck.
Does any one have a clue what can cause this and how I can increase performance?
Any help would be much appreciated,
Guy.
When you do joins/appends in Power Query it has to load the entire table into virtual memory. This causes a lot of paging and is very slow.
Does your dataset have to be refreshed on an approximatly hourly basis or is a daily cadence ok?
As this will greatly affect what options are open to you to increase performance.
7 Replies
v-lili6-msftCommunity Support
hi, GuySev
For your dataset is so big, the performance will involve many aspects, you could refer to below documents and blogs that may help you:
https://docs.microsoft.com/en-us/power-bi/power-bi-reports-performance
http://blog.pragmaticworks.com/power-bi-performance-tips-and-techniques
https://www.sqlbi.com/topics/optimization/
Best Regards,
Lin
GuySevHi v-lili6-msft ,
Thank you for your reply!
As I stated, I already went through these performance articles and implemented quit a few techniques.
Quit frankly, this model is not that big at all. I have sum models that are twice and even three times bigger, but preform much better.
I'm trying to understand if a step I made on query view is causing this, or rather a calculated measure maybe.
Is there any way to measure what causes this slow down?
When you do joins/appends in Power Query it has to load the entire table into virtual memory. This causes a lot of paging and is very slow.
Does your dataset have to be refreshed on an approximatly hourly basis or is a daily cadence ok?
As this will greatly affect what options are open to you to increase performance.
Old thread but hope this helps someone else.
When importing data from SQL, in Data Source settings, SQL Server Database connection info, you need to specify the port and not just the SQL server name. Not doing this will slow down the data import by quite a bit
ex: SQLSERVER1, 12345
Hi. I have a similar issue. We consumed data from SAP HANA database where in we created dataflows in Power BI Service. The dataflows we created are refreshing in very lesser time. We consumed the same dataflows in to power BI Desktop and when we move this dataset to service, it is consuming almost 3.5 hours in service. We also enabled incremental refresh in Service on dataflow. So not sure why our dataset is consuming a lot fo time for refresh. Any help could be greatly appeciated.
