Desktop Refresh taking very long - on prem SQL DB

[niel_orvyn1]

Hi there, 

Been googling but struggling to find the correct answer, probably my inexperience. 

• Connection: From my home laptop, connecting to a SQL DB on a physical server via VPN
• SQL queries are writting in power query via the sql statement option in source

In power query hitting the refresh preview (all), it takes about 10min do update all the queries. 
then
When hitting the refresh on the destkop screen (visuals screen), it takes about 2.5 hours to update. Most queries/tables go fairly quick, one howerver, with 6M rows takes the longest. 

Below screen shot trying to depict the above. 

I've used dax studio to try an see if the delay might be in measures, they all seem to be fairly ok, max of 45ms to refresh with a cleared cache. 

Anyone out there the could give me an answer or at least try and point me to the correct documentation? 

Thanks in advance. 

Best Regards,

Niel

[HotChilli · Accepted solution]

I think we have to assume that it is the Power Query part of the refresh that is taking a long time.  Are there any complex manipulations on the 6M row table?  Is Query Folding working?

Check in Task Manager->Powerbi to see if the Mashup engine is spiking and using up the PC memory and processor time.

You could try with Performance Analyzer to see if there is a particular visual that is causing the problem (I doubt it).  You could also copy the pbix and remove all visuals and run a refresh to test this.

Also (with the copy) Disable loading of the 6M row query and test refresh.

Do you suspect the slowdown is caused by the VPN connection?  Is it possible to use a remote viewer to connect to powerbi running on a physical machine in the office as a test? 

[HotChilli · Accepted solution]

Also,  incremental refresh is now available in Pro licensing version of powerbi, so you could look into that too.

[v-lili6-msft · Accepted solution]

hi  @niel_orvyn1 

Try to open Power BI --->File--->Options and Settings--->Options--->Data Load---> Uncheck autodetect new relationships after data is loaded

and i think vpn may be the problem for this case too, you may try to install power bi desktop with the datasource in the same PC and try it again.

Regards,

Lin

[HotChilli · Accepted solution]

Also,  incremental refresh is now available in Pro licensing version of powerbi, so you could look into that too.

[HotChilli · Accepted solution]

I think we have to assume that it is the Power Query part of the refresh that is taking a long time.  Are there any complex manipulations on the 6M row table?  Is Query Folding working?

Check in Task Manager->Powerbi to see if the Mashup engine is spiking and using up the PC memory and processor time.

You could try with Performance Analyzer to see if there is a particular visual that is causing the problem (I doubt it).  You could also copy the pbix and remove all visuals and run a refresh to test this.

Also (with the copy) Disable loading of the 6M row query and test refresh.

Do you suspect the slowdown is caused by the VPN connection?  Is it possible to use a remote viewer to connect to powerbi running on a physical machine in the office as a test? 

[niel_orvyn1]

Hi @HotChilli,

Thanks for your msg. I was a combination of the list you gave below. I've made added all your point as a trouble shooting checklist in future. 🙌. 

I have also added the incremental refresh and it is working very well, especially due to the VPN and latency issues in my part of this world. 

Many thanks!