Forum Discussion
Variable "Connecting to Datasource" Load times
Hello,
I am running into a strange issue that I am not sure how to solve. I am using Power Query to link to external Excel data files/Sheets and then I process the data within Power Query and also have some Macros that run after the data is refreshed.
I am seeing a drastic difference in the amount of time it takes separate users to open/refresh this file (the users have the same internet connection, use the same link to the file on a Shared Drive on our servers, same Excel settings, and have the same hardware). Some users can load and run all Macros in < 15 seconds, while the others take > 10 minutes.
I have exhausted all possible causes/reasons that I can think of and am looking for some guidance. Any and all suggestions are appreciated!
Thanks,
Skywalker_
2 Replies
Could I (as someone who used to teach technical problem solving--AKA finding of cause) ask a few more defining questions that might help you or others zero in on (or eliminate) some of the possible causes.
First, your description of the problem for which cause needs to be found is this: "Some users can load and run all Macros in < 15 seconds, while the others take > 10 minutes."
That's a good start, seriously. But there still are questions, the answers to which should make it even clearer.
- Is it always the same users who load and run in <15 seconds?
- Is it always the same users who load and run in >10 minutes?
- (Or, just be sure we're clear, are there some people who have both experiences, just on different occasions?)
- Are there no (zero) experiences in the sizable time gap between those two extremes?
- If there are other experiences, let's include them in the list of symptoms seeking a cause.
- Are these different users all in the same geographic location (you don't mention location in your description of all the "Sames" that they do share in common.
- When are these different extremes experienced (time of day, time of week)?
And so as to eliminate people coming up with possible causes you've already eliminated, what have been your hypotheses?
Yes, thank you Mathetes!
1/2. Yes (to a degree). I have a fairly small sample size when it comes to users that I know have experienced one or the other. I know of two individuals who always have it run <15 seconds, and four that are always > 10 minutes.
3. I am not aware of anyone that has experienced both scenarios. It's either one or the other.
4. There are rare occasions where they either both take longer or both run faster, but I have not heard of/experienced any significant deviations from one or the other.
5. Good point, I seem to have forgotten to add this! Yes, we are all located within the same building. I have made sure to test different internet types (WiFi, Ethernet) as well as locations in the building. These tests were unhelpful as the faster PC/User was still fast, while the slow PC/User (both are laptops in this case) was still slow.
6. They are consistent throughout the Day and Week. It does not matter at which time the Users attempt to open/run the file.
As for our test cases, we have attempted (using two specific laptops, one from each "bucket"):- swapping the User Account signed into Excel on either machine
- using both Ethernet and WiFi on either machine
- editing Excel's Macro/data connection settings
- downloading the files locally
- optimizing the Power Query connections
- using a "fresh" Windows account (i.e., I logged in using my Windows account onto one of the slow machines)
- reinstalling Microsoft Office 365 applications
I hope this helps clarify some of the underlying details I failed to mention!
Thanks again Mathetes for the robust questions!
