Re: inserting many records with ADO is very slow

"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