My Query Takes 10-15mins to Run!

[TechnoWay]

Hello, I've built this query on Access, there's about 13 tables linked. The total size of the db is only 2.3mb. The largest table has 14k rows and 70 columns (but not all of them are being used). So it's not huge and mostly runs pretty quickly when i run the query, but then when i add 3 more tables and run the query the whole thing freezes for 15min!! It doesnt matter what 3 tables i add, they're pretty small too.

I tried to be as memory efficient as possible and tried different things such as cutting the largest table's rows in half, but i still waited for 15min... Is there any recommendations to speed this up?

Thank you.

 

[petertenthije]

Bit hard to tell something if we know nothing of the query / formula / macro you are running.

Is the data written in the database, or based on external links? To excel / access / sap / etc?

Does your query return unique values only?

 

[TechnoWay]

Bit hard to tell something if we know nothing of the query / formula / macro you are running.

Is the data written in the database, or based on external links? To excel / access / sap / etc?

Does your query return unique values only?

Hey there! Let me try to give more info:

No macros, there are few IIF formulas but the freezing doesnt happen until i add the last 3 tables.
The data exist in Excel spreadsheets that are all linked to Access.
Im not sure about the last question. The query mostly returns unique values but there are some values that are same. But there's no duplicate or repeating IDs if that's what you meant. It's simply product data for merchandising basically.

 

[Joe4]

That is an awful lot of tables to join. Maybe you have some inefficient joins.
Make sure that all your tables have primary keys.
And you may want to try adding some indexes too. See: Create and use an index to improve performance.

EDIT:

The data exist in Excel spreadsheets that are all linked to Access.

I missed the part about using linked tables from Excel. That could be problematic, as you cannot add primary keys and other table properties to linked tables from Excel.
You might be better off importing your data to physically reside in Access (or some other database, like SQL).

 

[TechnoWay]

That is an awful lot of tables to join. Maybe you have some inefficient joins.
Make sure that all your tables have primary keys.
And you may want to try adding some indexes too. See: Create and use an index to improve performance.

EDIT:

I missed the part about using linked tables from Excel. That could be problematic, as you cannot add primary keys and other table properties to linked tables from Excel.
You might be better off importing your data to physically reside in Access (or some other database, like SQL).

Does adding primary key and index actually speed things up?

 

[Joe4]

Does adding primary key and index actually speed things up?

Yes, it absolutely can (or else I would not have suggested it).
See here: Microsoft Access Performance Increase the Speed of Your Access Databases

 

[TechnoWay]

Yes, it absolutely can (or else I would not have suggested it).
See here: Microsoft Access Performance Increase the Speed of Your Access Databases

I see. And is there a way to refresh the imported sheets automatically? The reason i went with linking was because we do frequent updates on the spreadsheets. How do you update the imported databases if not one at a time?

 

[Joe4]

I see. And is there a way to refresh the imported sheets automatically? The reason i went with linking was because we do frequent updates on the spreadsheets. How do you update the imported databases if not one at a time?

Unfortunately, this will probably require some maintenance. The easiest thing to do would probably be to delete the data out the table, and then import the new file.
That isn't hard to do and can even be automated to run with a single click.
However, whenever deleting data, you will want to do periodic "Compact and Repairs" on the database to keep it running lean (deleting the data doesn't really purge it from the database until it is compacted).

 

[Micron]

I would run either an update query and an append query on db table(s) using the linked sheet(s) as the source table(s) OR run an upsert query and do it in one go. The advantage to putting linked sheet data into tables is that you can ensure the data types are correct, plus the data is local while still being current - as long as you run the query/queries on some regular basis (e.g. first user in in the morning).

EDIT -forgot to say that some fields would have to be indexed no dupes to prevent duplicate appends.

 

[TechnoWay]

Unfortunately, this will probably require some maintenance. The easiest thing to do would probably be to delete the data out the table, and then import the new file.
That isn't hard to do and can even be automated to run with a single click.
However, whenever deleting data, you will want to do periodic "Compact and Repairs" on the database to keep it running lean (deleting the data doesn't really purge it from the database until it is compacted).

Okay so I converted all the linked tables into imported ones and added Primary key to each of them, ran the query and boom it took only 3 seconds! I couldnt believe it lol
But now how can I keep the linked tables, convert them to imported ones and add primary key to select columns automatically? Can I record a macro and let it do all the work with a click of a button? Or do i need to create bunch of Make Table queries?