Re: Performance problem on import

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.


"Jeff Boyce" <nonsense@xxxxxxxxxxxx> wrote in message news:OLBg6p2ZIHA.5900@xxxxxxxxxxxxxxxxxxxxxxx
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

"Jeff McKay" <comaxis@xxxxxxxxx> wrote in message news:13qeovlnhghfba9@xxxxxxxxxxxxxxxxxxxxx
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

  • Performance problem on import
    ... I have created a database containing one master table, that has links to two child tables. ... until I get to about 50 or so entries in the master table. ...
    (microsoft.public.access.externaldata)
  • Typed datasets using oleDB
    ... database in VB.Net. ... If I add a parent record to the dataset and then a child ... updated using the dataAdapter.Updatecommand. ...
    (microsoft.public.dotnet.framework.adonet)
  • Re: Object scope issue...
    ... So, the "update" method on the Parent class, inserts or updates parent data ... child class and calls it's "update" method which then either ... What I was doing in my "Save" command button code was setting the parent ... I only do a database update. ...
    (microsoft.public.dotnet.languages.vb)
  • RE: Event ID 447
    ... Please made a backup for Exchange database first. ... Please run the eseutil /mh command to see if the mailbox store is clean ... Microsoft CSS Online Newsgroup Support ...
    (microsoft.public.windows.server.sbs)
  • Re: Sharepoint Database falling over event 1000
    ... Microsoft Small Business Server Support ... > I Have Just run the SQL command from the command Line, ... > affected)1> exitDoes this say the database is Ok?If so why since we loaded ... > I/O operation has been aborted because ofeither a thread exit or an ...
    (microsoft.public.windows.server.sbs)