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, 16 Sep 2004 14:00:16 GMT
Message-ID: <Q%g2d.444242$%_6.367740@attbi_s01>

"Stefan Felkel" <stefan.felkel_at_cim-team.de> wrote in message news:d705870.0409160329.2133674_at_posting.google.com...
> "Jim Kennedy" <kennedy-downwithspammersfamily_at_attbi.net> wrote in message
news:<vH92d.442183$%_6.436342_at_attbi_s01>...
> >
> > My guess is that you are committing after each record. Ouch. Also you
are
>
> Definitely not - except ADO does it automatically. The Application does
the
> following:
>
> - Open a connection
>
> - BeginTrans
>
> - Add some records
> - Change some other records
> - Delete some other records
>
> - CommitTrans
>
> - Close the connection
>
> > not using an array interface which anyone would use to get good
> > performance.
>
> Not ADO, i guess.

I didn't see a begin trans , end trans statement in your script so it is in auto commit mode (as far as I remember) and the ADO is issuing a commit for every update. (bad design) I don't know if ADO supports an array interface or not, but if it doesn't you are losing a major performance feature. Also continually opening and closing a connection is very expensive. You should keep the cursor open and just change the bind variables. I don't use ADO for Oracle I use Oracle's OLE Objects driver and it has a nice array interface and you can easily use bind variables. I was able to insert 32,000 records in under 2 seconds.
Jim Received on Thu Sep 16 2004 - 09:00:16 CDT

Original text of this message

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