long running queries

[cje]

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.

[Rabbit]

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.

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

[Rabbit]

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.

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

[Rabbit]

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.