Slow Performance; Slow to Open/Save

Copper Contributor

Hello,

 

I have created a data model within a workbook comprised of 15+ Excel data sources and 1-2 from SharePoint. The Excel data source files range from 10k-100k rows, which are located on our network drive. I use Get & Transform to pull in each of those files to clean the data. Some queries have very little applied steps, and some have 10+ steps. Some steps are minor, such as removing columns; however, some are more complex, such as conditional calculations. Ultimately, the workbook uses the data model to populate the front-end of the report consisting of 20+ pivot tables/charts. A few slicers control the tables/charts on the front-end. There is also VBA within the workbook, but only to format and print. 

 

There are two issues I wrestle with every time I introduce a new data source via Get & Transform:

  1. The "Refresh All" takes about 2-5 minutes to perform. Is this normal?
  2. The file size is only 8.5 mb; however saving the file takes over 3 minutes and opening the file takes a long time as well. (When opening the prompt freezes at 0%) What is causing this lock up?
2 Replies

Hi @Michael Hang,

 

The refresh time is not unusual in my experience,  are you using 32 Bit Excel?

 

I'd encourage you to vote for this idea in the Excel User Voice forum to speed up query refresh

https://excel.uservoice.com/forums/304921-excel-for-windows-desktop-application/suggestions/16852819...

 

 

In terms of the file opening and saving that does sound unusual.  Do you have any formulas in your file?  

Also how many slicers do you have and how many Pivot Tables are they connected to?

 

 

 

 

Did you find a solution for this?

I exactly have the same problem.

Over the last week I couldn't find a single person reporting exactly the same issue until I found this post.

 

please help ...