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.

 

[Joe4]

Okay looks like adding just autonumber to every table and making it the primary key did not solve my problem, it still takes 10+mins to run. I have to go with a unique primary key for each table it seems...

Adding an autonumber and making it the primary key is the "fall-back" option when a better one doesn't already exist. If you already have a field in the query which is already unique and always populated, it is always better to use that as your primary key.

You should also make sure that every field used in joins to other fields is indexed (I believe that is mentioned in the link I provided).

If you have multiple tables, you will want to do this to each one.
Note however, that this should just be a one-time thing. Once you have it set-up, if you just re-use those tables like I described (by just deleting the data and then adding new data to the empty table shell), you will not need to do this again, as those properties are on the table and saved.

You mentioned I could run a delete query and then update query right? Can I do all that at once? Also update query tells me I cant run it on the asterisk? How can I update every column at once?

You cannot perform both Delete and Update query activities in the same query. They have to be two separate queries.
However, you can then run them both in succession in a Macro or from VBA code in order to run both with a single click.