Performance improvement in inserting record to database table

[Former Member]

I have internal table with nearly 1 million record to be updated to the database table.

Currently using :

INSERT dbtable FROM TABLE t_inttable.

Please suggest as insertion taking hours to insert the record.

[HermannGahm]

Hi,

is this a one-time action or do you have to do this regularly?

since you already use an array insert there is not much you

can do in the ABAP program.

You can check the execution on database level. If you have

many indexes that need to be maintained you can think about

droping them before the insert and rebuidling them afterwards.

Parallelization (running many processes in parallel inserting non

overlapping chunks of the data in parallel) might be an option as well.

Kind regards,

Hermann

[Former Member]

I have internal table with nearly 1 million record to be updated to the database table. Currently using:
INSERT dbtable FROM TABLE t_inttable.

Hhhmm, are you saying that you're inserting one million rows with one bulk insert statement? It might be good to check if you really need the all-or-nothing approach or if you could commit the data in smaller packages. That shouldn't make it any faster, but would put less strain on database resources...

As Hermann mentioned already, index management is often a big factor for table updates (thus resulting in the mentioned approach to drop the index, load the data and then recreate the index, which often is faster than maintaining the index during the updates). Hhhmm, in general it's also good to check for any foreign keys defined on the table. Though SAP is already fairly restrictive by only allowing references to key fields, you can still define generic keys and thus possibly have inefficient references/lookups.

Also, it might be worthwhile to check how the table is organized, e.g. how much free space is left and how additional space is added (i.e. if there's no space and new space is allocated in small chunks you also have some overhead there). It might also help to reorganize the table, if you have frequent DELETE's (otherwise your INSERT's are probably scattered among the free space from deleted rows).

I recommend to do some analysis and tests what possibly impacts the performance (e.g. it should be quick and easy to drop indexes and see if timings improve) and then figure out which measures you could actually use...

Cheers, harald

p.s.: I assume that this is not a one-time load, because then you could obviously use whatever efficient bulk-load tool your database provides...

p.p.s.: Should you consider parallel updates while maintaining indexes during the inserts, I would try to partition the data into chunks that ideally don't have to update overlapping index areas (to avoid index contention by different processes trying to update the same index pages).

[Rui_Dantas]

>

>in general it's also good to check for any foreign keys defined on the table. Though SAP is already fairly restrictive by only allowing references to key fields, you can still define generic keys and thus possibly have inefficient references/lookups.

I don't think that SAP's "foreign keys" are actually foreign keys (created and checked at db level); at least with Oracle they are not...

[Former Member]

I don't think that SAP's "foreign keys" are actually foreign keys (created and checked at db level); at least with Oracle they are not...

Oops, yeah, I got carried away with thinking in database terms. Now that you mention it, I remember that the foreign keys in SAP are merely used as automated input restrictions for screen fields, but have no effect at all on database level. I hope that at least this time I'm not babbling...

Thanks for the correction.

[ravi_lanjewar]

Hi,

You can do run program in background is better to execute otherwise it will give timeout runtime error and your execution turminated middle of execution. Insert the data in batch of 50-50 record.

other way to you check weather record exit or not if not exit insert record other wise update record

This logic will give better performance than modify or insert.

Rgds

Ravi Lanjewar

Edited by: Ravishankar Lanjewar on May 24, 2010 3:34 PM

[Former Member]

> other way to you check weather record exit or not if not exit insert record other wise update record

> This logic will give better performance than modify or insert.

no, this is exactly the MODIFY logic and not recommended for mass data