Unanswered: Slow performance with joins, unions and multiple queries
I'm frequently running into slow performance problems (a simple query taking 5-10 minutes to complete).
Here's the overview:
My data resides in Excel files and I have to combine data from several tables in order to obtain the information I need. I also use multiple queries to get there. I know it's not an elegant design (I'm a novice), but it works relatively well except in those situations:
1. At the beginning I wanted to link to Excel files instead of importing them. That of course would save me the pain of having to re-import the Excel files every time I change them. It was terribly slow though.
2. In one instance, I have to use "Union All". Afterwards, queries that used to take 10 seconds take 5 minutes to complete.
Is there a way of turning a query into a table or stopping the recalculation process? I only need to update the data every few days. That means that I could generate some queries that build up to a table that contains all the information I need ("Inter-Table") and then "freeze" this result so all my subsequent queries would be based on "Inter-Table" and there would be no need to repeat all the steps that led to the "Inter-Table".
Instead of using a set of simple SELECT query joined by a UNION operation to retrieve the data, you could use a serie of SELECT...INTO queries that would each create a table and store the data from the SELECT data set into it. The complete syntax is (see Access help):
SELECT <Field1>,< Field2>, ..., <FieldN> INTO <NewTableName> FROM <Source>
You could then use a UNION ALL query on the imported tables. Don't expect too much, though: the process will still be slow (UNION queries are not very performant in Access).
One other posibility is to import the data into a platform with a few more options , such as SQL Server. Based on your expertise level , this may require some learning , but in the long run will give you more flexibility