×
INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Contact US

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

Improving my data/table structure

Improving my data/table structure

Improving my data/table structure

(OP)
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
www.enterprise.com
 

RE: Improving my data/table structure

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.

RE: Improving my data/table structure

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.

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members! Already a Member? Login

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close