Performance problem on import

  • Thread starter Thread starter Jeff McKay
  • Start date Start date
J

Jeff McKay

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.
 
Jeff

The first place I'd check, were this mine, is the indexing in JET/Access.
If the JET engine has to re-index the table after each insert, this could
end up taking a long time.

If that turns out to be the issue, you could risk removing the indexing,
loading it up, then re-indexing... if you are confident that the data is
well-behaved<g>.

When you check for indexes, remember that Access/JET assigns an index to
fields used in the relationship window -- and if you have parent-child
relationships you've made explicit, Access has assigned (hidden) indexes.

Good luck!

Regards

Jeff Boyce
Microsoft Office/Access MVP
 
I tried removing every relationship and the primary key from every table,
and made sure that no fields in any table were indexed. It should now be
totally flat. No change whatsoever in performance. It's almost as if there
is a speed governer in the software - as soon as 50 records are added -
boom,
no more fast inserts. Could it have something to do with how the table is
expanded, once the free space is gone? I don't see any options to control
table size or growth behavior.
 
I think I have this fixed now. I was doing an unnecessary SQLAllocHandle
and SQLFree inside a tight loop which when removed got things going again.
I do think it shows a bug
in the ODBC driver for Access, since I don't get the problem with SQL
Server, and
there wasn't really anything wrong with the original code.
 
Back
Top