MS Access performance

Hi,
I'm developing in an MSAccess 2013 database (yes, I know, no one does anymore), three linked databases with a total size of 800MB. My VBA code takes about 1 minute to run through many calculations on about 256K records. I thought I could gain some performance improvement by putting everything on a RAM disk (4GB out of 16GB physical), tried ExFat and NTFS both), but alas, there's not much improvement (65 seconds vs 70 seconds).

Any idea what might be going on that doesn't? The CPU never goes above 15% for either set.

thanks for any insights
John

The fact that you gained some improvement in performance after moving to a RAM Disk indicates that you set it up correctly. But the fact that the improvement was only a small one indicates that in this particular case the read/write speeds might not be the main slowing factor.

It is possible that other approaches, such as indexing, query optimization, or database compact & repair, may make a much bigger difference.

Hi, thanks for your input. I've been optimizing this database as much as possible (its a 5 year project), so i'm pretty sure all the indexing is as best as can be. same with compacting. query optimization, sure, there's always room for improvement. I guess i was assuming that if you are reading and writing to a database regularly in your code (theres probably about 100 places in the code where there are extensive read/writes), if it was just going to RAM, you would see a significant improvement.

As an example (before trying the ram disk) the main area of code was originally looping through records to process them (there's no other way to do it) and it was always getting bogged down especially on the full set of data . But i was able to upload the desired data into an array first, run through the loop, and then export back to the tables in about 1/5 the time it took when it was accessing the tables directly (500% improvement there). I was hoping i could find a similar degree of improvement if all the rest of the code was only accessing ram the whole time (vs the 10% or so i experienced)

In general use case scenario, assuming everything is as optimized as possible, would using the RAM disk as a database and application location be expected to bring a larger improvement than what i experienced? Maybe is there something with MSAccess that is the bottleneck (like maybe its still using disk in the background)?

The issue might stem from how the VBA script processes data. Notably, when data was pre-loaded into an array for processing, performance improvements were observed, suggesting the bottleneck could be tied to inefficient data handling by the script. Therefore, optimizing the script's data management and processing methods could mitigate some performance issues, rather than focusing solely on enhancing disk I/O speed.

It's difficult to pinpoint without reviewing your code, but generally, VBA's performance is limited because it operates as an interpreter, parsing and interpreting each line within a loop anew. Furthermore, the method of data access significantly affects performance. For example, employing SQL queries for data manipulation, instead of iterating through each record in the dataset, can offer substantial improvements.

thanks for your input. I agree that its hard to know where the bottleneck is without looking at the code, and VBA has its limitations. I have worked on optimizing it quite a bit, and have found many places where it has helped a lot. I guess what I'm getting at is even with all the code limitations and so forth, i would think that having everything in RAM should have registered some significant improvement, especially in the realm of populating temp tables and updating existing tables. I mean, in principle, if I have an insert query (just by itself) that takes 10 seconds to insert 300K records using disk access, shouldn't I expect a decent performance increase of more than 5% if its running in RAM?
thanks again

This simply indicates that the bottleneck is not related to disk I/O. For instance, if you wish to compare the pure disk performance, you could utilise Crystal Disk Mark to assess both your SSD and your RAM disk. It is likely that you will observe a significant performance difference between the two.

However, when it comes to inserting 300K records, the bottleneck likely lies elsewhere. Possible factors include SQL query parsing (especially if it occurs with each iteration), implicit transaction begin/commit operations (if not using explicit transactions), or other internal mechanisms of MS Access.

Additionally, it's noteworthy that MS Access was developed well before the era of multicore CPUs. Consequently, unlike contemporary software, it is not designed to leverage multicore processing, which may be a contributing factor to the performance limitations observed.