Performance problem on import

I have created a database containing one master table, that has links to two child tables.
I am attempting to do a mass import of data to this database using a C++ program and the
ODBC API. I use the SQL "Insert" command to create a record in the master, and then
mutiple insert commands on both child tables (from 1 to 10 entries on one child, usually 0-2 on the second).

This works great, until I get to about 50 or so entries in the master table. Then everything
slows to a crawl. I see delays of 2 or 3 seconds on every insert command. Not workable
(I expect to import hundreds or thousands of records per database). Any ideas about what
is going wrong? I suspect that the JET database engine is just not very efficient, taking a long time to update key chains, etc. But I don't really understand why it works fine for the first few dozen records. Maybe some kind of memory caching?

By the way this same program works fine importing thousands of entries into SQL Server.

.

Relevant Pages

  • Re: Performance problem on import
    ... I am attempting to do a mass import of data to this database using a C++ program and the ... mutiple insert commands on both child tables ... until I get to about 50 or so entries in the master table. ... I see delays of 2 or 3 seconds on every insert command. ...
    (microsoft.public.access.externaldata)
  • Re: please solve the problem
    ... that deleted record automatically deleted from child table. ... This is known as a Cascading Delete and is handled by the ... relationships between your database tables. ... you only need to delete from the Master Table; ...
    (microsoft.public.dotnet.languages.vb)
  • Re: import into new relational database - data entry into related table impossible
    ... I can enter data into the master table but clicking on ... the fields from the child table has no effect. ...
    (comp.databases.filemaker)
  • Re: Setting Up An Access Database On Terminal Server Or Citrix
    ... the user with the database and their IT people set it up. ... a central location from which to start the other 3 "Child" databases. ... up on Terminal Server and this will be left to their IT people, ... "Master" database, given that each user will have their own frontend ...
    (comp.databases.ms-access)
  • Database archiving?
    ... For each day database must be empty, ... table, and child ... table is in relation with master table over KeyTest. ...
    (microsoft.public.access.gettingstarted)
Loading