5

The company I work for produces a lot of test data in raw text files from lots of different devices. Each device belongs to a 'process' of which there are only a few dozen. Currently we have a program which takes this data and produces/adds to an Access database (currently .mdb, however we are hoping to change to .accdb because it's newer and better?) of the appropriate process.

We then use Excel to view the data from the databases in a pivot table. The database files themselves rarely get close to 1 GB (maybe a few a year) at which point we archive the database and create a new one as this is the size limit. We never need to access the data of multiple processes at the same time, so we have never had an issue with them being in separate databases.

Someone in our IT department has suggested that we switch to an SQL database, where all of the data for all processes will be stored, and the data will just have an extra column labelling the process it belongs to.

My worry is that in accessing this data through Excel we would only ever be querying one process at a time, but the database would have to filter through all of the other processes, which would surely take longer, since it is having to query dozens of times more data?

Is there any disadvantage to sticking with our current system of multiple databases, one for each process which we only access one at a time, instead of collecting them all into a large database? And conversely are there advantages of having a single large database system like this? Am I wrong and it would be faster to use a large SQL database than multiple small Access databases?

3
  • 3
    While JD makes good points, carefully consider your situation and costs vs. benefits. Nothing is free - software must be acquired, expertise developed, business processes updated and relearned, code migrated, etc. Query performance is likely to be of little concern in a properly designed database using well-written queries.
    – SMor
    Jul 14, 2022 at 12:37
  • 3
    @SMor That's fair from a business perspective. Though I usually find there's a good amount of unrealized costs to using obsolete practices and / or software in a business too, that people typically aren't aware of until they take a step back and analyze how they can improve.
    – J.D.
    Jul 14, 2022 at 12:41
  • You may want to import the data to a test SQL database, and actually try how long it takes to query it from Excel.
    – pts
    Jul 16, 2022 at 17:44

3 Answers 3

12

In my opinion, Microsoft Access is an obsolete way to manage data, especially within an organization. There's a lot of limitations and lack of features compared to a modern database system, besides the data being stored in a localized file (as opposed to being centralized on a server). But people still use it to do so, which I suppose is why Microsoft still supports it as an application.

My worry is that in accessing this data through excel we would only ever be querying one process at a time, but the database would have to filter through all of the other processes, which would surely take longer, since it is having to query dozens of times more data?

When architected and indexed properly, this would not be something to be concerned about. A B-Tree index (the standard type of index in Microsoft SQL Server) has a search time of O(Log2(n)). This means if your table had 1 billion rows, it would only need to search through roughly 30 nodes of the B-Tree (at most) to find any particular subset of data (Log2(1 billion) = 30).

So as the person in your IT department mentioned, if you indexed your table on the Process column (since it sounds like you only look at one process of data at a time), ideally it would only take milliseconds to locate any subset of process data, no matter how big the table was. Then loading that data off disk would take roughly the same time as it currently does in Microsoft Access, since it would be the same amount of data.

To hammer the point home, anecdotally, I used to work with databases that had tables that were terabytes big, with 10s of billions of rows in them, on a server that was no more provisioned than a modern laptop, and querying for any subset of data took less than a second for SQL Server to locate.

5
  • 1
    "But people still use [MS Access] [as a database backend], which I suppose is why Microsoft still supports it as an application." Note that MS Access is both (a) a database back-end as well as (b) a Windows GUI font-end builder, and that you can use both of these components without the other one (for example, many line-of-business MS Access front-end applications use an SQL Server back-end). Thus, even if people stopped using MS Access as a back-end database (for which there are lots of good reasons), it would still be a popular rapid application development/end-user data analysis tool.
    – Heinzi
    Jul 15, 2022 at 17:00
  • @Heinzi That's absolutely true actually, good point. I personally believe even as a front end / rapid application development tool, it's obsolete in favor of other Microsoft solutions (including ones that don't require coding).
    – J.D.
    Jul 15, 2022 at 18:39
  • Well, I've seen Microsoft continually release (and, a few years later, abandon) "MS Access front-end replacement" technologies for the last two decades (anyone remember "Visual Studio LightSwitch"?). Sure, it's entirely possible that one of them will eventually succeed (I'd love to eventually migrate our huge, legacy Access-based font-ends to something completely .NET based), but, so far, I remain unconvinced.
    – Heinzi
    Jul 15, 2022 at 20:27
  • Indexing on a column with such a low selectivity (only a few dozen distinct values) may not help very much. Maybe partitioning on this column would help more. Jul 16, 2022 at 6:11
  • @RazvanSocol I don't think we can assume the selectivity without knowing how many rows there actually are / will be over time in the table vs how many a particular Process would return. OP's concern is data growth, which with his use case will introduce a new unique Process each time the data grows. To me that sounds rather potentially selective. I would even argue a few dozen distinct values is good selectivity. If we put a number to that, for example 30, and the table has 1,000,000 evenly distributed rows, an index reduces the search space to around 33,333 rows for a particular Process
    – J.D.
    Jul 16, 2022 at 12:37
5

Education: full-featured databases (which Access is not) have a thing called Indexes*. When configured, these allow you to pull data by indexed values without visiting the rest of the table. You can easily get the rows you want from a table with millions or billions of rows in milleseconds to seconds. So that is not a reason to avoid changing.

Instead, let's lay out the disadvantages and advantages of your current approach:

Advantages of Your Current Setup

  • You're used to it. You have knowledge, processes and techniques for operating your current stack that you and your coworkers are used to.
  • Your equipment (server and network) are adequate for your current approach and do not need to be augmented.
  • You don't need to buy any new software.

Disadvantages

  • Reliability: one of the chronic issues with MDB and AccDB, historically, has been its tendency towards file bloating and eventual data corruption. This requires some complex backup procedures not supported by the app to recover from. This may be a very small or very large problem for you, depending.
  • Network access: MSAccess is not designed to be accessed over the network by multiple staff at the same time. Apparently that's not currently a requirement, but if it became one, you couldn't support it.
  • Won't support new analytics tools: should your office decide that they want to be able to use better analytical tools, like Tableau or SciPy, these will not be supported by your current setup.
  • No cross-process queries: right now you don't do analysis across different processes. Should you ever decide to, though, your current setup will not support it.

So, as you can see, most of the reasons to change are around enhanced capabilities. And only you and your coworkers can decide if these enhanced capabilities are worth the cost of changing systems.

(* MSAccess actually supports indexes, but given the structure of MDB they are not nearly as effective as indexes on SQL Server)

2
  • 2
    "MSAccess is not designed to be accessed over the network by multiple staff at the same time." Actually, it is, that's what those ".ldb" files are for. It's not necessarily a good idea, though, since, in my experience, those data corruption issues you mentioned get worse when accessing Access DBs over a network.
    – Heinzi
    Jul 15, 2022 at 17:09
  • 1
    Exactly, I didn't say "not possible" I said "not designed". I was a beta tester for the original MSAccess release. It was designed to be a user-friendly GUI client for external SQL databases, and originally wasn't supposed to have its own data storage. Jul 15, 2022 at 19:52
3

You question does already have two excellently answers. What I'd like add, is that your IT department could start by downloading a free Developer version of an SQL Database, like for example MS SQL Server. They could then "play" with it. Import MS Access files is easy. They could then use SQL queries. Help on the web is plenty. Then you could decide whether to upgrade to a more advanced Relational Database.

7
  • Both SQL Server and Access use SQL so one is as relational as the other. Jul 15, 2022 at 19:21
  • @ypercubeᵀᴹ: In theory, you're right. In practice, this is a touchy subject, and I've seen more then one flame war sparked by whether one may call MS Access a relational database or not.
    – Heinzi
    Jul 15, 2022 at 20:11
  • 1
    Really? Based on what grounds? It has primary key, unique and foreign key constraints as far as I know. Surely not all the capabilities of SQL server but I don't see any argument why it can't be called relational. Jul 15, 2022 at 22:47
  • You are right. Actually there are dozens of them: en.wikipedia.org/wiki/… So why use Oracle, IBM DB2, Teradata or MS SQL Server? Pointless. MS Access is just as good! Actually I started my career with dBase. That is good too!
    – user255698
    Jul 16, 2022 at 3:46
  • @ypercubeᵀᴹ: See, for example, the comment threads at stackoverflow.com/q/15499145/87698. Personally, I agree with you.
    – Heinzi
    Jul 16, 2022 at 7:23

Your Answer

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

Not the answer you're looking for? Browse other questions tagged or ask your own question.