topic Optimize Mass Insert to database in Application Development and Automation Discussions

Optimize Mass Insert to database

Former Member — Wed, 30 Apr 2008 10:52:04 GMT

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

Re: Optimize Mass Insert to database

ThomasZloch — Wed, 30 Apr 2008 10:59:02 GMT

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

Re: Optimize Mass Insert to database

Former Member — Wed, 30 Apr 2008 13:29:52 GMT

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