Reader Question: Is it worth running an Access front-end from a RAM disk?

Is it worth the complication to run an Access front-end file from within a RAM disk? The answer may surprise you. Or it may not. (Am I doing clickbait wrong?)

Reader Question: Is it worth running an Access front-end from a RAM disk?

Reader Xevi Batlle wrote in with the following question (shared with permission):

Is it worth [it] to execute the FE in a RAM disk drive?
As far [as] I'm concerned, perhaps it might be a good option in old computers with slow disks.

What is a RAM Disk?

A RAM disk is just what it sounds like: a "logical" (as opposed to "physical") disk drive that exists in a computer's random access memory (RAM).  

From Wikipedia:

A RAM drive (also called a RAM disk) is a block of random-access memory (primary storage or volatile memory) that a computer's software is treating as if the memory were a disk drive (secondary storage). RAM drives provide high-performance temporary storage for demanding tasks and protect non-volatile storage devices from wearing down, since RAM is not prone to wear from writing, unlike non-volatile flash memory. They are in a sense the reverse of virtual memory: RAM drive uses a volatile fast memory as if it's a nonvolatile slow memory. Virtual memory is the opposite.

The idea is to take advantage of the exceptionally fast speed of hardware RAM to juice as much performance as possible out of an application.  

Volatile vs. Non-Volatile Storage

RAM is "volatile." Its contents are lost as soon as it loses power, such as when the computer is powered down.  This makes it a poor choice for anything you want to permanently store.

Physical disk drives, including traditional spinning platter drives, flash drives, and solid state drives (SSDs), are forms of non-volatile storage.  When data is written to these devices it stays there until it is overwritten, even if the device loses power.

For this reason, you would never want to store a back-end data file on a RAM disk.  All it would take is a sudden loss of power and all of your data would be gone.

Front-End Access Files are Essentially Temporary

Front-end Access files are prone to occasional corruption.  

In most cases, I believe this stems from some combination of the following factors:

  • Source code and "compiled" intermediate code (also known as byte code or P-code) can get out of sync
  • The Edit and Continue feature allows writing new source code that gets "compiled" and incorporated into already-compiled (and running!) code
  • Multiple users editing form and report objects in the same file at the same time (e.g., if the front-end file is run [and edited] from a network location)
  • Deleting an object then creating an object of the same type with the same name without an intervening repair and compact
  • Gremlins

This is one of many reasons why it's important to split an Access database into separate front-end and back-end files.  With a split front-end approach, you distribute a front-end file to every user.  If an individual user's front-end file gets corrupted or starts acting funny, you simply provide them with an updated front-end and the problem is solved.

In fact, some front-end launcher utilities (such as Kevin Bell's appLauncher for Access) automatically create and run a copy of the front-end rather than the original front-end file itself.

My Thoughts

Since Access front-end files are essentially temporary in nature (or at least should be treated that way), they do seem to at least be a candidate for a RAM disk.  

Personally, I still don't think it makes sense.  Here was my response to Xevi:

Interesting idea.  My thoughts are that it wouldn't be worth the added complication of creating and maintaining a RAM disk.  

You might see slight improvements in runtime performance, but by far the best way to improve the performance of database applications is with more efficient database interactions (e.g., querying, updating, and deleting data). For example, adding an appropriate index could easily net you a 1,000% performance improvement on a slow-running query, whereas moving the front-end to a RAM disk might net you an across-the-board 50% improvement in responsiveness.  The problem is that will likely look like a form opening in a quarter of a second versus half a second.

Context is everything when it comes to performance.

Or, to put it another way: "just say to yourself, 'Gloves.'"

Xevi's Follow-up Response

To put a bow on this one, here was Xevi's follow-up to my response:

I totally agree with you that it is much more important to improve the application rather than using RAM disks. To tell you the truth I don't use RAM disks in my applications but I thought that perhaps somebody would be interested in doing it.

As I said in my response, it is an interesting idea.  

What I like about these sorts of unusual questions is that the process of answering them often elicits deeper insights.  

For example, we talked here about how front-end Access files are essentially temporary files, which is not how I've consciously thought about them in the past, but is how I've subconsciously treated them.  Another insight the question surfaced is that almost all major Access performance problems stem from inefficient database operations.  In contrast, switching to lightweight forms, VBA hyper-optimization, or upgrading your computer's hardware are equivalent to rearranging deck chairs on the Titanic when it comes to improving your Access application's performance.

Thanks for the thought-provoking question, Xevi!  Keep 'em coming.

Cover image created with Microsoft Designer

All original code samples by Mike Wolfe are licensed under CC BY 4.0