Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.misc -> Re: inserting many records with ADO is very slow

Re: inserting many records with ADO is very slow

From: Jim Kennedy <kennedy-downwithspammersfamily_at_attbi.net>
Date: Thu, 23 Sep 2004 15:04:41 GMT
Message-ID: <dCB4d.34497$wV.30775@attbi_s54>

"Stefan Felkel" <stefan.felkel_at_cim-team.de> wrote in message news:d705870.0409230328.57a0b84e_at_posting.google.com...
> "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

If you put the begin trans end trans inside the loop then you might as well use auto commit, since all auto commit does is commit after each statement. (since Oracle doesn't have any auto commit mode) You should always use prepared statements no matter what back end. Jim Received on Thu Sep 23 2004 - 10:04:41 CDT

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US