Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

Register Log in
  • Congratulations wOOdy-Soft on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Improving my data/table structure

Status
Not open for further replies.
newguy86

newguy86

Technical User
May 19, 2008
226
US
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
 
Sort by date Sort by votes
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.
 
Upvote 0 Downvote
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.

 
Upvote 0 Downvote
Status
Not open for further replies.

Similar threads

raywood1
  • Locked
  • Question
Reducing a Massive Linked Table
Replies
4
Views
769
dhookom
dhookom
MrStohler
  • Locked
  • Question
Beginner Q: How to separate data from Excel into related tables and maintain relationship? 1
Replies
5
Views
706
SkipVought
S
Anthony343242
  • Locked
  • Question
MS ACCESS UNION ALL with Attachment data type 1
2
Replies
29
Views
7K
Anthony343242
Anthony343242
03Explorer
  • Locked
  • Question
Refreshing a linked table (housed on MS SQL Server) now breaks
Replies
3
Views
577
dhookom
dhookom
JayRo71
  • Locked
  • Question
Importing only unique records from excel tables 1
Replies
10
Views
939
SkipVought
S

Part and Inventory Search

Sponsor

Back
Top