0

My company has been chained to this MS Access '97 .MDB format we've had since the dawn of time. Our schema is hierarchical, with multiple one-to-many relationships. We are at a point where we are looking for alternatives, as the slowness and overall clunkiness of Access are beginning to take their toll on our productivity.

Our "modern" methods of accessing the database involve DAO.Net and heavy amounts of hash-based caching. The .NET System.Collections.Generic.Dictionary type has been a god-send here, because without it, I don't know how we would get our work done in a timely manner. We have multiple projects that each have a database file associated with it (sometimes multiple), and we tend to interact them one of two ways: either the DB is created by hand (using our in-house editor), or generated using a program which takes data we receive from another company, in some other format, and converts it to our format.

In both cases, our common .NET library loads the entire database into hash tables via Dictionary and resolves the object relationships with code by looking up values in the hash table by ID. When auto-generating the database, we use another set of hash tables to determine whether an object exists already in the cache before adding it. Once we are finished parsing the source data, we start a multi-threaded bulk insertion operation. We do all this because any other method of accessing the database is very slow.

I hope I've given enough context to my question: Is there a DB engine out there whose query speed can rival that of hash tables like what I am using? Memory and disk usage are no concern, these DB only exist on developer machines, we convert them to a different format for use with our software. I just want to get rid of my hash tables, but I don't want to sacrifice speed to do it.

Improve this question

1 Answer 1

Reset to default
0

"Is there a DB engine out there whose query speed can rival that of hash tables like what I am using?" - Yes, almost any modern relational database management system (RDBMS), because they've been carefully constructed to utilize the appropriate data structures and data merging algorithms given the context and statistics of the data. Always using Hash Tables, Dictionaries, and multiple singleton ID lookups is not always the most efficient way to store, read, and combine data.

My personal opinion is MS Access sucks as a database system, even more so that you're using the '97 version. So almost any modern RDBMS should be an upgrade. You'll likely just need to shift paradigms to storing the databases on a dedicated server as opposed to locally on the client machines, as that's standard practice for modern RDBMS.

There are a multitude of options to choose from out there, but I recommend picking a mainstream option so that there's plenty of support and documentation on how to utilize it. Since you're already using their stack, Microsoft offers SQL Server as solution, which is highly functional and well designed, but is an enterprise software so may cost money to use. SQL Server's Express Edition is free with a couple of limitations, the most apparent one being a limitation of 10 GB of size per database. Other cheap and free options include PostgreSQL and MySQL as well.

Another notable benefit by switching to a modern RDBMS will likely include improved concurrency.

Improve this answer
7
  • From what I understand though, I'm not sure a server-based SQL is a possible switch for us. Currently these are version-controlled files, they're synced with the project that they are a part of - If we need to fetch an old version of the project, we need the database that matched with that old version. Unless there's some features of RDBMS I don't know about. Commented Aug 24, 2022 at 4:31
  • @KateM There's Liquibase and Flyway which can version dbs. Commented Aug 24, 2022 at 10:38
  • @KateM When you say "files", are you referring to the MS Access apps?...or the MS Access database files? MS Access can interface with SQL Server, so it's definitely possible to swap out the database and hook your existing apps into an RDBMS. No matter which DB you switch to, this is what will end up happening. That doesn't mean you need to get rid of your source control history, and you can source control a RDBMS moving forward, but it will be a separate set of files (if not separate project) to maintain in source control, from the application files. Commented Aug 24, 2022 at 12:20
  • @J.D. we could switch to sqlite for a simple single-file database alternative though, right? Is sqlite the only other DB engine that runs locally? I think I understand what you're saying in terms of version control. But in order to keep a database version-controlled in this way, it means someone would need to keep logging in to the machine every time a build is made (sometimes 3 times a week on an active project) in order to commit the DB? I'm just trying to understand the work flow. edit: when I say files I am speaking of the MS Access DB files, the .mdb Commented Aug 24, 2022 at 15:29
  • @KateM Most database systems can be ran locally (it's just not usual to do so). SQL Server can be ran locally too. SQLite is just more lightweight of a database system to make portability easier. But even if you chose SQLite you still wouldn't be able to source control it exactly the same way as you're currently doing with the MS Access database. Instead, you'd likely source control the scripts you apply to your SQLite database (or any database you choose). Modern database systems are managed / changed / updated with SQL scripts (including SQLite). Commented Aug 24, 2022 at 16:07

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.