Data Refresh Staggering Performances...

[Anonymous]

Hi guys,

I have a connection to a sql table in my power bi desktop.

When i refresh in the desktop version (about 4000 rows) -> it takes less than 20-30 seconds

When i publish it on app.powerbi and refresh the datasource via the gateway ->it takes 10 minutes

Anyone know what could be happening here?

Really need some help!

[v-diye-msft · Accepted solution]

HI @Anonymous 

 1. The data for these rows loads into memory and is uncompressed at every refresh. This processing creates huge demands for memory. The solution: use the "Top N" filter to reduce the max number of items that the table displays. You can set the max item to larger than what users would need, for example, 2000. The result is the end-user experience doesn't change, but memory use drops greatly. And most importantly, performance improves.

2. You should probably optimize your capacity, see Optimizing Premium capacities.

3. To understand gateway workloads and sizing recommendations, see On-premises data gateway sizing.

[v-diye-msft · Accepted solution]

HI @Anonymous 

 1. The data for these rows loads into memory and is uncompressed at every refresh. This processing creates huge demands for memory. The solution: use the "Top N" filter to reduce the max number of items that the table displays. You can set the max item to larger than what users would need, for example, 2000. The result is the end-user experience doesn't change, but memory use drops greatly. And most importantly, performance improves.

2. You should probably optimize your capacity, see Optimizing Premium capacities.

3. To understand gateway workloads and sizing recommendations, see On-premises data gateway sizing.

[Anonymous]

for 1), you mean in the matrix tables that I have in my dashboard, i should filter them to show only a subset of the data? 

[GilbertQ · Accepted solution]

Hi there

here is a great video explaining how to complete the incremental refreshing, which should assist you in getting it working.

https://www.youtube.com/watch?v=BQahH53ayKU

[GilbertQ]

Hi there

It could be quite a few things that could cause it to run slowly.

A few that spring to mind are:
- Resources on the Gateway Server (CPU & Memory)
- How fast is the connect from Gateway to SQL Server
- How fast is your internet connection from the Gateway Server to the Power BI Service
- How busy is the Power BI Service

That and a few more things could be causing it to take time to run slower.

Unless you need the dataset to be refreshed every few minutes I would find that to be more than acceptable.

You could also look at Incremental refreshing your dataset if it is supported?

[Anonymous]

Thanks that's helpful. How would incremental refresh be done?