Re: Inserting IS Very Very Slow - Why?

It helps to put indexes on foreign keys (MyTable_4.CHAR_3, MyTable_3.CHAR_4 ..). Try doing an EXPLAIN on the insert. It'll tell you about the tablescans taking place to validate the FK constraints. If your machine has enough memory, the table the trigger stuffs may be in memory & not cause too much pain.

Evan

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 Wed Mar 15 2000 - 00:00:00 CST