Improving my data/table structure

[newguy86]

Hello all,
I'm having an issue where a query in my db takes a long time to run (approx. 30 seconds or so). The query is based off of two tables in my db that are joined first and then retreives the information that the user requested. And from what I can tell the slow down is in joining these two tables.

One of the tables is linked to a report (excel) we receive and stores the location information (address, phone #, geocode, etc.) for all locations in the company. The other table stores the various ID's that these locations use for all the programs and systems we work in.

What I want to know is if there's a better way to store this information so that when the query needs to run (which is frequently) it won't take so long?

Any assistance you guys can provide would be greatly appreciated!

Travis

http://www.enterprise.com

 

[lameid]

Generally linked tables from a separate file/database are going to be slower and especially if they are not a database (like text or Excel).

If you linked the spreadsheet you should be able to import it easy enough. Then you can make your updates in Access either in the table directly or using forms.

Lastly, your join should go faster if you index the fields you are joining on in each table. If one is a unique field for each row, make it a unique index and consider making it the primary key.

Noteworthy is that it is easy enough to export a table to Excel or have Excel use Access as a datasource to read information if you stil want to present data in Excel.

 

[BNPMike]

Also consider denormalising - Import both tables and create a new joined table. You'll need to update it on a regular basis but it will cut out the join processing at run time.