Re: inserting many records with ADO is very slow

"Jim Kennedy" <kennedy-downwithspammersfamily_at_attbi.net> wrote in message news:<Q7D2d.316893$8_6.257799_at_attbi_s04>...
>
> I don't have the code in front of me (its at work), but I used the example
> in the documentation with creating an array of bind variables and then doing
> the insert. (they have an example in the docs) If you installed OO4O then
> you should have actual examples on your PC. To further debug this you are

Thanks for that tip. With OO4O i was able to insert 512,000 records in 6 seconds. Sadly, i have to support other databases as well.

> probably going to have to trace what it is doing. If you use sqlLoader it
> is very efficient and it uses the published API. I suspect ADO does some
> "helpful" things and that is why your performance is low. Can't you use
> bind variables in ADO? You might be doing a ton of parsing and that is
> going to kill you. Doesn't ADO allow a multiple insert option? It isn't

ADO supports prepared statements with parameters and that is much faster than the inserts with the Recordset. When executing multiple inserts at once (BEGIN INSERT...; INSERT...; INSERT...; END;) with the adequate number of parameters it gets even faster (128000 records in 25 seconds) - but its much slower than the inserts with OO40 (128000 records in 2 seconds).

> clear in your code where you put the commit (begin and end trans) You
> should put it outside the for loop.(inside would be like auto commit.) You

ASFAIK when using BeginTrans/CommitTrans no auto commit happens between these two calls (and they are outside the loop in my tests).

Stefan Received on Thu Sep 23 2004 - 06:28:45 CDT