cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
GuySev
Advocate I
Advocate I

Data 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.

1 ACCEPTED SOLUTION
Anonymous
Not applicable

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.

View solution in original post

5 REPLIES 5
v-lili6-msft
Community Support
Community 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

 

Community Support Team _ Lin
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Hi @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?

Anonymous
Not applicable

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.

Hi @Anonymous ,

 

Thank you for this clarification, eventually I kind of figured out this is the case as well.

I changed my data model in order to avoid some of the appends and indeed performance increased substantially.

 

Thanks again!

@GuySevHi

I ran in a similar problem, my power queries running up to 4 hours. I'm using Access to do my joins, the same joins take me 3 seconds now. I found Access to be an excellent tool for extracting from multiple sources, doing your data modeling and specific views, then pushing it out to power Bi. Access also enforces accuracy in your data and it is not difficult to learn. It follows the same data modeling rules as Data modeling in Power BI. Just more strict as it demands parents to child ect aka data referral integrity.

 

It also acts as a centralization point for your data to maintain and build dimensions etc.

 

Regards

 

Wynand

Helpful resources

Announcements
Microsoft Build 768x460.png

Microsoft Build is May 24-26. Have you registered yet?

Come together to explore latest innovations in code and application development—and gain insights from experts from around the world.

charticulator_carousel_with_text (1).png

Charticulator Design Challenge

Put your data visualization and design skills to the test! This exciting challenge is happening now through May 31st!

May UG Leader Call Carousel 768x460.png

What difference can a User Group make for you?

At the monthly call, connect with other leaders and find out how community makes your experience even better.

Power BI Dev Camp Session 22 768x460.jpg

Check it out!

Mark your calendars and join us on Thursday, May 26 at 11a PDT for a great session with Ted Pattison!