Re: Inserting IS Very Very Slow - Why?

If this is just a routine that is a one shot deal, or done infrequently, then try disabling the Primary Key and Foreign Key constraints on the table. Each time a record is inserted, the index on the Primary Key is re-created and the Foreign Key relationships are checked. This can cause considerable overhead. When you are done. Enable the constraints. If the data you entered is faulty (i.e. duplicate Primary Key), Oracle will not let you enable the constraint.

Just an idea...hope that helps,

Kyriakos Lambros
Programmer
Las Vegas Valley Water District

Ken Sproule wrote:

> Thanks to all who help, even to those who merely try.
>
> Now please understand that I am assuming I have done something wrong
> or haven't done something I should.
>
> I have a C++ program using MS's ADO. It constructs a string that is
> used in an ADO command object and is as follows (table and column
> names are spurious, but data types are correct ).
>
> "INSERT INTO MyTable(
> NUM_1 , -- NUMBER(4,0)
> CHAR_1 , -- CHAR(10)
> STRING_1 , -- VARCHAR(40)
> DATE_1 , -- DATE
> NUM_2 , -- NUMBER(6,0)
> NUM_3 , -- NUMBER(6,0)
> CHAR_2 , -- CHAR(4)
> CHAR_3 , -- CHAR(4)
> NUM_4 , -- NUMBER(6,0)
> NUM_5 , -- NUMBER(6,0)
> CHAR_4 , -- CHAR(3)
> NUM_6, -- NUMBER(3,0)
> STRING_2, -- VARCHAR2(15)
> STRING_3 ) -- VARCHAR2(40)
> values( ........ );
>
> The constraints on the table are as follows:
>
> CONSTRAINT PK_ MyTable
> PRIMARY KEY (CHAR_1 , DATE_1 ),
> CONSTRAINT FK_ MyTable _ NUM_4
> FOREIGN KEY (NUM_4 )
> REFERENCES MyTable_2),
> CONSTRAINT FK_ MyTable _ CHAR_4
> FOREIGN KEY (CHAR_4)
> REFERENCES MyTable_3 CHAR_4 ),
> CONSTRAINT FK_ MyTable _ CHAR_3
> FOREIGN KEY (CHAR_3)
> REFERENCES MyTable_4(CHAR_3)
>
> The inserts set off a trigger that stuffs some variables into another
> Table, but that table is rather small ( i.e. 50 rows and 80 columns).
>
> AnyWay this routines has been running about 3 ½ hours and has only
> inserted about 7,300 records.
>
> Any ideas?
>
> Thanks again for your help!
>
> Sincerely,
>
> Ken Sproule
> kenmn_at_tds.net
>
> Ken Sproule
> kenmn_at_tds.net
Received on Tue Mar 14 2000 - 17:35:33 CST