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!

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

Very slow process adding new records?

Status
Not open for further replies.

BJZeak

Programmer
May 3, 2008
230
CA
Running a T7200 Dual core processor at 2GHz under XP Pro SP3 and Access 2K3 SP3 ... one table with 200K records where the primary key is a compond of the first 3 fields

Each of these fields is indexed and can be duplicated as long as the combination of the 3 fields is unique ... 2 of these fields have many to 1 relations to 6 other tables (5 tables to 1 field and an information table to the other field)

I went to add 15K of potentially new records to this table ... in the process I used an ADO findfirst statement just to ensure the record didn't exist. This process took over 2 hours to complete ... The findfirst statement uses the first 3 fields in top down order

I then ran the process again and it only took 15 minutes to complete (this time I assume because all the records existed)

Am I missing some basic concept? What is slowing down this process? Index? Related tables? Other?

Both times, 15 minutes to find and 2+ hrs to add 15K of records seems out of line especially since these are indexed?

 
ADO findfirst statement" might be the issue. If you have an index that can be used, I would consider changing to DAO with SEEK. This can be much, much faster. If the tables are linked, you must create a database object to the MDB containing the linked tables.

Duane
Hook'D on Access
MS Access MVP
 
All tables are in a backend mdb file and the update process was written and run from that file in single user mode on the machine it resides (no links). The Relationships aid with Multi Subform Screens where related data is for the most part automatically refreshed.

This process is part of an "Expert" Parts System where the data doesn't change much. The users will update existing data if required but normally the bulk of the data is fixed.

The normal front end mdb file links to this backend file across a network but typically, (99.999%) it's only used to lookup part information/diagrams ... on the very rare instance a correction/addition may be required to the attributes of a part and or override its base part details (part details are fixed right or wrong so they cannot be deleted or changed only overriden).

All of the FrontEnd Screens use ADO FindFirst lookups and have been to this point fast enough for the users ... if a seek is faster perhaps this will be an added bonus. I will do some investigations.

Thanks for the feedback.
 
Seek works great however the Help screen states it cannot be used on linked tables so perhaps won't be usable for the frontend processes :{
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top