Performance issue with Insert ACCEPTING DUPLICATE KEYS.

[Former Member]

Hi Data Base experts,

We are using INSERT with ACCEPTING DUPLICATE KEYS in one of our Z table. This is causing performance issue job got struck at this insert and sitting there for a hours . the z table contains nearly 8 millions of records. all these records are created by this insert statement . This insert is not in between loop / end loop. This is the code .

INSERT ztable FROM TABLE it_links ACCEPTING DUPLICATE KEYS.

My question.

Is ACCEPTING DUPLICATE KEYS addision take time. What we need to consider the performace wise .

Thanks in advance

[Former Member]

I don't think ACCEPTING DUPLICATE KEYS should add to the performance issue. 8 million records is huge.

You might want to consider breaking your internal table into smaller chunks and issue multiple insert statements followed by a commit.

Regards,

Ravi

Note : Please mark all the helpful answers

[Former Member]

Ravi,

Thanks for your reponse . I forgot to state that we are inserting in chunks only . Not in a single shot all the 8 millions .

[sridhar_k1]

DO you have COMMIT WORK after insering each chunk from itab?

If you have commit at the end of program or no commit work, the program tries to commit 8mil at the end, which is a performance issue.

Regards

Sridhar

[Former Member]

Sridhar ,

It is followed by a Commit work . I am able to see the chuncks getting updated in table . But it is very slow process

[Former Member]

Hi

You should try to break the inserting, I don't know how many rows you try to insert once, but if the number is very big you should do the insert for block with 1000 records (for example) and after inserting a block do a commit.

Max

[Former Member]

Is it_links sorted by the primary key fields of ztable?

Rob

[Former Member]

Rob,

No the it_links is not sorted . do i need to do it . In what way it hampers the performance . Pls advice me

[Former Member]

I'm really not sure how much it will help, but it should give some improvement. It can't hurt anything, so why not try it?

Rob

[i807822]

Hi, I would like to try to answer your question.

I see you are trying to insert in zTable. I would start by investigating the internal table called it_links.

Is it a standard table? If not, try first sorting this table as this would be an asset for performance. By sorting the table, we will 'group' all duplicates one after the other.

If no explicit sort key is entered using the BY addition, the internal table itab is sorted according to the table key.

  SORT it_links BY key1 key2. 
  DELETE ADJACENT DUPLICATES FROM it_links COMPARING key1 key2.

The sorting will be done in memory.

Then you can break up into small blocks and update to database.

This is what I would try first. Use trn. st05 to calculate processing time.

I hope this helps. Nick.

[former_member792891]

Hi;

I'm wondering something about your answer.


INSERT ztable FROM TABLE it_links ACCEPTING DUPLICATE KEYS.


is not  this code comparing ztable and it_links dublicate fields

but your code compares dublicate records only ın it_links table

 SORT it_links BY key1 key2. 
  DELETE ADJACENT DUPLICATES FROM it_links COMPARING key1 key2.

am I right ? or do I think wrongly