By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
434,917 Members | 1,335 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 434,917 IT Pros & Developers. It's quick & easy.

long running queries

P: 3
I am new to access. I can work in it but don't necessarily know the correct way to do things. I am importing large tables for comparison, from 60,000 to 160,000 rows typically. I have a query where I import two files and join them by 3 fields and then link to a table and join it by one of the fields. I am left joining by one of the tables to the other. I am multiplying the price from the linked teradata table times several columns in both of the other tables. This query runs fine until I add the multiplication of the columns, then it takes forever. Also, when I get the results and try to copy them to put into excel, it never finishes or if I try to hit the buttom to see how many rows at the end of the results, it never finishes. I end up having to cancel. Can you tell me what I can do to make this run faster. When I import, I am not adding any keys because depending on what I do, sometimes it's unique by 2 columns and sometimes by 3. Also, is it better to import the data into access or link to the file? Thank you.
May 30 '07 #1
Share this Question
Share on Google+
5 Replies


Rabbit
Expert Mod 10K+
P: 12,366
I am new to access. I can work in it but don't necessarily know the correct way to do things. I am importing large tables for comparison, from 60,000 to 160,000 rows typically. I have a query where I import two files and join them by 3 fields and then link to a table and join it by one of the fields. I am left joining by one of the tables to the other. I am multiplying the price from the linked teradata table times several columns in both of the other tables. This query runs fine until I add the multiplication of the columns, then it takes forever. Also, when I get the results and try to copy them to put into excel, it never finishes or if I try to hit the buttom to see how many rows at the end of the results, it never finishes. I end up having to cancel. Can you tell me what I can do to make this run faster. When I import, I am not adding any keys because depending on what I do, sometimes it's unique by 2 columns and sometimes by 3. Also, is it better to import the data into access or link to the file? Thank you.
Create indexes on all join fields. Create relationships between the tables.

Problem is when you do anything more than a select or equijoin, performance is going to suffer because it can't use an index to speed it up. So that multiplication forces it to process each record. Try a join and then a seperate query to calculate the value, I don't know if this will help but give it a shot.

When importing to excel you are most likely overloading excel. If you just let it run it would probably give you an error eventually. An excel sheet can only have about 66,000 rows of records.
May 30 '07 #2

P: 3
cje
Create indexes on all join fields. Create relationships between the tables.

Problem is when you do anything more than a select or equijoin, performance is going to suffer because it can't use an index to speed it up. So that multiplication forces it to process each record. Try a join and then a seperate query to calculate the value, I don't know if this will help but give it a shot.

When importing to excel you are most likely overloading excel. If you just let it run it would probably give you an error eventually. An excel sheet can only have about 66,000 rows of records.

Thank you for your suggestions. I had previously broken out the queries and had the multiplication separate but it didn't help. However, I did not add indexes so I will do that. Can you tell me if I run the query to create a new database to build the next one with or do I run the next one, doing the multiplication, using the query that I saved that pulled off the appropriate data. Also, the results are less than 66,000 rows so I guess it would just take a long time to come back from what you are saying. I appreciate your help.
May 30 '07 #3

Rabbit
Expert Mod 10K+
P: 12,366
Can you tell me if I run the query to create a new database to build the next one with or do I run the next one, doing the multiplication, using the query that I saved that pulled off the appropriate data.
I'm not sure what you mean by this.
May 30 '07 #4

P: 3
cje
I'm not sure what you mean by this.
OK, what I mean is that when I save a query that pulls in a dataset that I have imported as a table, does the query save the results as another database or each time I run it does it create the data again. So, when I build another query from this query, is it executing the first query and creating the data again or does it have the database from the first query already created? Is this more understandable? But I don't know how to create a database from the run of the first query because all I get is a results set and I can't find an option to create a table from it. Thank you.
May 30 '07 #5

Rabbit
Expert Mod 10K+
P: 12,366
OK, what I mean is that when I save a query that pulls in a dataset that I have imported as a table, does the query save the results as another database or each time I run it does it create the data again. So, when I build another query from this query, is it executing the first query and creating the data again or does it have the database from the first query already created? Is this more understandable? But I don't know how to create a database from the run of the first query because all I get is a results set and I can't find an option to create a table from it. Thank you.
Yes, it does rerun the query each time. So if you base a query on a query. it will run the first query, then the second query. To save the results, change the query to a make table query and then base the second query on the table it makes.
May 31 '07 #6

Post your reply

Sign in to post your reply or Sign up for a free account.