Results 1 to 3 of 3
  1. #1
    Join Date
    Jul 2011
    Posts
    6

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

    Or maybe you have different suggestions.

  2. #2
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    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):
    Code:
    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).
    Have a nice day!

  3. #3
    Join Date
    Aug 2008
    Posts
    147
    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
    ---------------------------------
    www.sqlserver-dba.com
    www.dba-db2.com

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •