VBA, File System Object, speed/advantages/disadvantages

Question

This turned into a rather long post, and there's not really an "answer" per say. I'm more looking for an explanation as opposed to some silver bullet to fix the problem. As such, any aspect you'd like to answer would be quite appreciated. Thanks in advance!

---

I'm running into what may be a "problem" with the file system object, and that's lead to a question about the functionality etc. of how the File System Object in VBA works vs. "something else" (I don't know if there's an alternative to use in Excel for what I'm doing) in .net etc. I don't know of a better place to ask, and I'm not sure what to look into to research it for myself. So here I am!

So! To the problem. The short explanation is that I iterate through folders, gathering file information (name, extension, full path, etc.) and place it into a spreadsheet. I eventually use this information to copy the files to a new location. However, on a large scale (1,000+ files) this seems to work just fine locally, but it is considerably slower on a network location (at work). It will chew through like 1,500 files, wait a while, do 1,500 more etc. Either while listing or copying the files. Again, this is not the case when done locally, it will just run through without issue, so I can probably assume it's probably nothing to do with my code. It's almost as if the network is opening and closing a gate intermittently.

Alternatively, using other programs from an end user perspective (I tried it against the same files I was using with my program, on our work network) it is MUCH faster without any of the aforementioned delays. I'm assuming the alternative program is using some version of .net, if it matters. Long story short, I don't think I can inherently blame our network for the speed issues I'm running into.

So my question/curiosity/issue comes down to a few key points:

-What's the difference between the FSO in VBA and the default libraries in .Net, and could the difference between the cause of the issue I'm running into? Clearly it's possible to read this sort of data much more quickly than it is being done.

-Is the FSO not intended to be used this way (over a network, with large amounts of remote data, or... ?)? Is it just dated/outmoded? And is there an alternative that can be used through VBA?

-I only nebulously understand that our network functions in a different way than a local drive. It stores many terabytes of data, etc. and I'm not sure what the difference is at a very deep level between accessing a local drive and a network location. I know I'm not giving details on the network that would probably be very beneficial in diagnosis, I just don't the information unfortunately. I guess I'd just ask if it "potentially" an explanation that using the FSO in such a way with some/all sorts of networks is just not the way it's meant to be used. Is it possible that the network is set up in such a way to limit the sort of way I'm trying to interact with it?

-Even though I haven't run into any issues doing this locally, is it possible that something in my code is much more taxing to a network location vs. a local drive?

Thanks for any insight you can provide.

Answer 1

Finch042 acknowledges that he is only "nebulous" about the specifics of what is different when accessing a network server's file system vs. a local file system, and that his question is really about the relative speed different between those two circumstances. All of the other posts here assume that the issue is with his design choices and/or coding techniques but I think the underlying question has gone unanswered: why is it that network file operations can be so much slower?

The short answer is that a networked file system is on a different computer's disc at the end of LAN cable (or, worse, a Wifi signal), and such intermediary technology is much more limited in its data-transfer bandwidth than the electronics between a computer's processor and its local disc. It is true that modern LAN capacities are, relative to the stone-age, blindingly fast, but they are still way, way slower than the disc-interface electronics on a PC's motherboard. So you will always experience some level of performance degradation when accessing remote files.

Furthermore, many modern server farm systems may include mirroring (i.e. storage redundancy) for data-integrity maintenance and may also include automatic version-backup capabilities, both of which can add access time to some server operations, especially when writing new files or updating existing ones.

As for the fluctuations in the data transfer rates to/from the server, which Finch042 describes as an apparent "gating" of the data flow: whenever you are using a common-access technology, such as LAN systems and shared servers, you are usually competing with others who are trying to do similar stuff. For example, LAN technologies such as traditional Ethernet actually allow the various users to stomp all over each other’s transmission attempts and, when that does result in a failed attempt, it retrys until it succeeds. It is a design that trades simplicity and, thereby, ultimate overall reliability, for a (usually) minor loss in throughput speed. But when the demand on the network is high, it can result in a dramatic degradation in throughput for all users.

Similarly, a file server has a limited capacity to service file-system access requests and it, too, can become overloaded at times of high demand.

I suspect that Finch042's experience is likely related to those kinds of issues, especially if his organization's network and server system grew incrementally, and therefore in a non-optimized way, over a long time, and/or is at or near its capacity limit. And his experience of inconsistent data-transfer rates is likely just the ebb and flow of demand on the common, shared network/server systems.

Also, be aware that virus protection systems can interfere with file access speeds, especially for network server files.