Optimize Mass Insert to database

[Former Member]

Hi Experts,

Is there a way to optimize mass inserts to the database?

I did INSERT tabname from TABLE internaltab. this takes a long time.

Any thoughts?

Thanks in advance.

Best Regards,

Rose

[ThomasZloch]

I don't know a better way than the array insert you are mentioning.

If I remember correctly, having the internal table sorted by the primary key of the database table might speed things up somewhat.

It might also be a good idea to mass insert in chunks of e.g. 50K records (depending on row size), with COMMIT WORKs inbetween.

Greetings

Thomas

[Former Member]

That statement can not be improved, but you should better take care that there are not to many lines in the internal table until you execute a Commit.

The database keeps rollback areas in case if something goes wrong. If they become too large you will see negative performance impact. Therefor you should

split the table into smaller ones of about 10000 lines.

Check

append lines of from index1 to index2.

Insert 10.000 lines and commit and insert again.

This should work fine.

Siegfried