Best Practices for Optimizing Large Datasets in MS Access

[sandy_52]

Hi everyone, I’m working on a Microsoft Access project with a large database (100,000+ records) and facing performance issues with queries. What are your top strategies for optimizing table structures and queries to improve speed?

I’ve tried indexing and normalizing tables, but I’m looking for advanced tips like query optimization, VBA automation, or database splitting. Below are two images: one showing my current table structure and another of a slow query design. Any advice on tools, techniques, or common pitfalls? Thanks for your insights!

 

[theDBguy]

Hi. Welcome to AWF!

Splitting the database is a recommended best-practice approach, so you should do it anyway. If your database is already split, another recommended approach is to open and maintain a persistent connection to the data. Can you show us the design view of the slow query and post its SQL statement?

 

[mike60smart]

Hi everyone, I’m working on a Microsoft Access project with a large database (100,000+ records) and facing performance issues with queries. What are your top strategies for optimizing table structures and queries to improve speed?

I’ve tried indexing and normalizing tables, but I’m looking for advanced tips like query optimization, VBA automation, or database splitting. Below are two images: one showing my current table structure and another of a slow query design. Any advice on tools, techniques, or common pitfalls? Thanks for your insights!

View attachment 120190
View attachment 120191

Can you upload a screenshot of your Relationship Window?

 

[MajP]

May want to look at

Use the Performance Analyzer to optimize an Access database - Microsoft Support

You can use the Performance Analyzer to optimize the performance of a Microsoft Access desktop database.

support.microsoft.com

Microsoft Access tips: Optimizing queries

How to get JET (the database engine in Microsoft Access) to execute queries quickly

allenbrowne.com

If you really want to get into the details

JetShowPlan: A Primer

You may be familiar with JetShowPlan, but I guarantee you've never read an article about it quite like this one.

nolongerset.com

 

[The_Doc_Man]

Your exhibit is mixed. We can see the navigation pane to the left in each case and those two displayed images don't come from the same database. How do they both relate to the problem you were describing, or are you asking a general question? In which case why two different DBs?

 

[gemma-the-husky]

100k records isn't that many. What issues are you having, Mike?

 

[gemma-the-husky]

In your city table, I would not store strings for the country. I would store a lookup ID to a country table.

Ah. I can see it's a query on top, and you have that structure. I'm not used to tabbed displays.

 

[Pat Hartman]

I agree 100,000 rows is an insignificant row count. It doesn't help us to see the results of a query, we need to see the schema and the SQL for the query.

 

[isladogs]

Another link that may be helpful.
In this article, I tested the various optimisation techniques suggested by Allen Browne. Example database included

Speed Comparison Tests 8

This article covers various methods of optimising queries and the use of the JET ShowPlan feature. It is the eighth in a series of articles discussing various tests done to compare the efficiency of different approaches to coding. Example databases are provided so the tests can be done on your...

www.isladogs.co.uk

There is also a related video: