Re: Oracle is painfully slow when doing massive INSERTs

From: Mark Usrey <msu_at_sumsoft.com>
Date: 1998/03/11
Message-ID: <6e6t7j$98d_at_bgtnsc02.worldnet.att.net>#1/1


Morten Myrvold wrote:

> Hi all...
>
> Hope someone can help me with this one (kinda newbie question):
>
> I've written a utility that essentially transfer tables from one database to
> another (different RDBMSes). To transfer a table consisting of
> 30000-something rows takes 18+++ mins to complete (number of columns doesn't
> seem to affect this). I'm using an ODBC cursor for selecting rows from the
> source DB and a dynaset (OO4O21) for inserting into Oracle.
>
> What I'd like to know is: Is there a way for me to speed up this process.
> I've tried changing the COMMIT frequency (also tried not using transactions
> at all), but these adjustments did very little to the overall transfer
> time... If it means I'll have no way of rolling back transactions, so be
> it... I don't really care - I just want the destination database to be an
> exact replication of the source database (and the transfer time as low as
> possible, of course).
>
> Timing the transfer process revealed that as much as 90%++ of the time is
> spent on INSERTing into Oracle.
>
> I've tried running my databases on (almost) every possible configuration (on
> the same computer, on two different HDs/controllers on one computer and on
> two separate computers) - all with the same result. I've also made sure that
> the network traffic is as low as possible, so that's not it either.
>
> It seems to me that it all boils down to one thing: Oracle's just plain
> painfully slow on massive INSERT's....
>
> Any ideas, anyone?
>

[Quoted] [Quoted] You did mention any indices that are kept on you destination table. Large amounts
of time can be spent updating the various indices on the destination table if [Quoted] they are complex. Also, if the rows being inserted are already ordered, updates

[Quoted] to indices and be particularly slow.

[Quoted] [Quoted] If you are keeping indices, try dropping them before doing the insert, and then recreating them afterwards.

Mark Received on Wed Mar 11 1998 - 00:00:00 CET

Original text of this message