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]

Typically, I do the following:

In order to update my existing queries to use the "new" imported tables, I find it is often easier to update the existing queries by changing them to SQL View, copying the SQL code out to Microsoft Word, finding & replacing all instances of the old table name with the new table name, and copy/paste that code back over the orignal SQL code of the query.

Regarding maintaining the data, I would do the following:
1. Have your "Imported" table, set up with all primary keys and indexes, and continue using this in all your queries.
2. To update the data in this query, I often do the following in a Macro:
i. Delete all the data from the "imported" table through a SQL Statement, i.e. "Delete TableName.* From TableName" (I often turn off the warnings with a "SetWarnings=False" step first).
ii. Run an Append Query that you have set up to copy all the data from your Linked Table to your Imported Table.
Note that depending on how your data changes, you may not need to delete and re-import the data each time - if you have a Unique Primary key field you could instead run an "Update Query" to update any changes to existing records (Matched Query to identify all existing records), and then an "Insert Query" to populate all new recorders (Unmatched Query to find all new records).

If you are deleting a lot of records, then just be sure to periodically run a Compact/Repair regularly to purge all deleted records to keep things running lean.

 

[TechnoWay]

Typically, I do the following:

In order to update my existing queries to use the "new" imported tables, I find it is often easier to update the existing queries by changing them to SQL View, copying the SQL code out to Microsoft Word, finding & replacing all instances of the old table name with the new table name, and copy/paste that code back over the orignal SQL code of the query.

Regarding maintaining the data, I would do the following:
1. Have your "Imported" table, set up with all primary keys and indexes, and continue using this in all your queries.
2. To update the data in this query, I often do the following in a Macro:
i. Delete all the data from the "imported" table through a SQL Statement, i.e. "Delete TableName.* From TableName" (I often turn off the warnings with a "SetWarnings=False" step first).
ii. Run an Append Query that you have set up to copy all the data from your Linked Table to your Imported Table.
Note that depending on how your data changes, you may not need to delete and re-import the data each time - if you have a Unique Primary key field you could instead run an "Update Query" to update any changes to existing records (Matched Query to identify all existing records), and then an "Insert Query" to populate all new recorders (Unmatched Query to find all new records).

If you are deleting a lot of records, then just be sure to periodically run a Compact/Repair regularly to purge all deleted records to keep things running lean.

Thank you for the suggestions! Unfortunately since the updates on the spreadsheets comprise of adding/deleting rows as well as editing data Update or Append query gets too convoluted for me. I'd rather go with Make Query and simply replace the tables everytime. But now my issue is losing the primary key in this process.

Is there a way to automatically add auto number and primary key with Macros?

 

[Joe4]

I'd rather go with Make Query and simply replace the tables everytime. But now my issue is losing the primary key in this process.

Yes, that is PRECISELY why you don't want to do it that way. The good new is, you don't need to!

Use the first method I recommended.
Just create the Import Table once (i.e. import the data, set up the Primary keys, indexes, etc).
And then just keep re-using this table shell by deleting all the data each time, and importing all the data from your linked table into it each time.
That way you don't need to rebuild your table each time, nor do you have to update all your queries.

I have used this method many times with much success.
The only thing you need to do is remember to do a Compact/Repair regularly.
That is something that can be scheduled (I have done it in a Start-Up script in the past, put occasional errors sometimes caused database corruption, so it is probably not the best way to do that).

 

[TechnoWay]

Yes, that is PRECISELY why you don't want to do it that way. The good new is, you don't need to!

Use the first method I recommended.
Just create the Import Table once (i.e. import the data, set up the Primary keys, indexes, etc).
And then just keep re-using this table shell by deleting all the data each time, and importing all the data from your linked table into it each time.
That way you don't need to rebuild your table each time, nor do you have to update all your queries.

I have used this method many times with much success.
The only thing you need to do is remember to do a Compact/Repair regularly.
That is something that can be scheduled (I have done it in a Start-Up script in the past, put occasional errors sometimes caused database corruption, so it is probably not the best way to do that).

So i found this method to add an auto number and primary key when i run the Make Table everytime, but i can't seem to successfully place this in the SQL design. This is the default SQL for the query:

SELECT [Generic Product Titles].* INTO [Product Titles_imp]
FROM [Generic Product Titles];

and this is the line I want to add:

ALTER TABLE [Product Titles_imp] ADD CONSTRAINT [ID] PRIMARY KEY ([ID])

No matter where i place it i get an error, mostly "Syntax error in FROM clause."

Could you help me integrate this line in the code please?

 

[Joe4]

So i found this method to add an auto number and primary key when i run the Make Table everytime, but i can't seem to successfully place this in the SQL design. This is the default SQL for the query:

SELECT [Generic Product Titles].* INTO [Product Titles_imp]
FROM [Generic Product Titles];

and this is the line I want to add:

ALTER TABLE [Product Titles_imp] ADD CONSTRAINT [ID] PRIMARY KEY ([ID])

No matter where i place it i get an error, mostly "Syntax error in FROM clause."

Could you help me integrate this line in the code please?

You shouldn't have to to do that. You should just be able to set up the table once, and not have to mess with it after that.
What does the data in your table look like?
Are there any unique fields that already exist (i.e. like some sort of ID field)?
If not, all you have to add is add an Autonumber fileld to the Imported Table.
It doesn't matter that the linked table does not have it in there. When you add data from the Linked Table to the Imported Table, it will automatically populate the Autonumber field.
You don't need to do anything special.

 

[TechnoWay]

You shouldn't have to to do that. You should just be able to set up the table once, and not have to mess with it after that.
What does the data in your table look like?
Are there any unique fields that already exist (i.e. like some sort of ID field)?
If not, all you have to add is add an Autonumber fileld to the Imported Table.
It doesn't matter that the linked table does not have it in there. When you add data from the Linked Table to the Imported Table, it will automatically populate the Autonumber field.
You don't need to do anything special.

Unfortunately I dont get a primary key (index) by default because I'm running Make Table queries and this is the SQL code:

SELECT [Generic Product Titles].* INTO [Product Titles_imp]
FROM [Generic Product Titles];

I need to add something like
ADD CONSTRAINT [ID] PRIMARY KEY ([ID])
or
ADD COLUMN ID COUNTER

But no matter where i place this in the code i get a syntax error...

 

[Joe4]

Unfortunately I dont get a primary key (index) by default because I'm running Make Table queries and this is the SQL code:

Why are you running Make Table queries? As I said, there is no reason to do this!

You are creating your tables ONCE, and only ONCE. Once created, you set up all the primary keys and indexes you need on those tables.
Then, you are never deleting these tables, or adding new tables in their place.
You are simply deleting the data out of these tables, and adding new data into these tables.
So their structure (primary keys, indexes, etc) never go away, and you never have to mess with them again after the initial table set-up.
You are creating the "shell" of the table, and them simply re-using that table over-and-over (which is good because then you never need to change the table names used in your queries after the initial table creation).

 

[TechnoWay]

Yeah i decided to go with Make Table queries, way easier for my situation, thanks for the suggestion tho!
Now I need to figure out how to add primary key for each table with SQL.

ALTER TABLE [Product Titles_imp] ADD PRIMARY KEY (ID)

This works but only one time, i cant add the rest of the table names this way. So the worst case scenario i create additional 15 queries like this, but I'd rather avoid.

 

[Joe4]

Yeah i decided to go with Make Table queries, way easier for my situation, thanks for the suggestion tho!
Now I need to figure out how to add primary key for each table with SQL.

ALTER TABLE [Product Titles_imp] ADD PRIMARY KEY (ID)

This works but only one time, i cant add the rest of the table names this way. So the worst case scenario i create additional 15 queries like this, but I'd rather avoid.

That is exactly why I do not advise doing it that way, it is much more difficult to do and involves having to run code every time (Make Table Queries are a big pain to deal with because you have to recreate those things EVERY time!).

Why do you think this way is easier than my suggestion?
I have been using that method for years, and set it up in a Macro so that once everything is set up, all I have to do is click a single button to get a fresh copy of my data into my data tables.

 

[TechnoWay]

That is exactly why I do not advise doing it that way, it is much more difficult to do and involves having to run code every time (Make Table Queries are a big pain to deal with because you have to recreate those things EVERY time!).

Why do you think this way is easier than my suggestion?
I have been using that method for years, and set it up in a Macro so that once everything is set up, all I have to do is click a single button to get a fresh copy of my data into my data tables.

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...

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?