topic Re: Performance issue with Insert ACCEPTING DUPLICATE KEYS. in Application Development and Automation Discussions

Performance issue with Insert ACCEPTING DUPLICATE KEYS.

Former Member — Tue, 27 Jun 2006 14:12:41 GMT

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

Re: Performance issue with Insert ACCEPTING DUPLICATE KEYS.

Former Member — Tue, 27 Jun 2006 14:17:01 GMT

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

Re: Performance issue with Insert ACCEPTING DUPLICATE KEYS.

Former Member — Tue, 27 Jun 2006 14:28:06 GMT

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 .

Re: Performance issue with Insert ACCEPTING DUPLICATE KEYS.

sridhar_k1 — Tue, 27 Jun 2006 14:42:38 GMT

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

Re: Performance issue with Insert ACCEPTING DUPLICATE KEYS.

Former Member — Tue, 27 Jun 2006 15:06:26 GMT

Is it_links sorted by the primary key fields of ztable?

Rob

Re: Performance issue with Insert ACCEPTING DUPLICATE KEYS.

Former Member — Tue, 27 Jun 2006 15:56:06 GMT

Rob,

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

Re: Performance issue with Insert ACCEPTING DUPLICATE KEYS.

Former Member — Tue, 27 Jun 2006 15:57:04 GMT

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

Re: Performance issue with Insert ACCEPTING DUPLICATE KEYS.

Former Member — Tue, 27 Jun 2006 16:21:06 GMT

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

Re: Performance issue with Insert ACCEPTING DUPLICATE KEYS.

Former Member — Tue, 27 Jun 2006 17:17:59 GMT

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

Re: Performance issue with Insert ACCEPTING DUPLICATE KEYS.

i807822 — Wed, 29 Aug 2007 20:01:01 GMT

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.

Re: Performance issue with Insert ACCEPTING DUPLICATE KEYS.

former_member792891 — Mon, 27 Sep 2021 12:55:57 GMT

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