1

I have an Access database for which I am the only user. It's the first database I've built. It has 16 related tables, around 40 select queries, and a dozen or so update/delete queries. It is already 512MB and will at least double in size as additional data is added to tables and more queries & reports are created over the next 12 months. The largest table (which is accessed by most of the queries) is around 800k rows by 11 fields. This table will most likely grow to over 2M lines over the useful life of the database (c. 12 months).

Queries that ran in under 30 sec a month ago are starting to run slower as the tables have grown, some queries which include calculations now taking 10 min or so to complete (and yes, I am using stacked queries as much as possible).

Does anyone have solid advice one way or the other as to the performance boost I could expect from splitting?

Thanks

Glenn Langford
  • 27
  • 8

1 Answers1

1

No you wouldn't, only query optimizing and careful indexing can speed up the query time.

That said, you should split it anyway (create a backup and run the wizard) if for nothing else to ease backup of your data and to make that independent of your ongoing development of the frontend.

Gustav
  • 53,498
  • 7
  • 29
  • 55
  • Thanks Gustav, I'll go ahead and split for the reasons you've mentioned but also because the db may approach 2GB within its useful life. – Glenn Langford Apr 06 '16 at 07:52
  • Then you could just as well start now to use a database engine that can hold that many data, like _SQL Server_ or _MySQL_. – Gustav Apr 06 '16 at 08:47
  • Thanks Gustav, I'll consider that. I'm using Access to transform XML files on the way in and also to update/delete etc. My SQL knowledge is minimal, so I'd probably still have to use Access for the front end. – Glenn Langford Apr 06 '16 at 09:00
  • @GlennLangford: Frontend Access and Backend SQL Server is a well-proven combination. The free SQL Server Express can work with databases up to 10 GB. – Andre Apr 06 '16 at 09:22
  • Do note: Access by default uses a database engine, Jet/ACE which carries the SQL dialect. Gustav is recommending server level RDMS's as Access like open-source SQLite (btw - max size at 140 terabytes) are file level RDMS's. Also, @GlennLangford, you can connect to multiple Access backend files not just one. – Parfait Apr 06 '16 at 13:09