Skip to Content

Record deletion from Standard SAP table takes too long

Hi,

In our Retail system we have to SAP tables:

- FRE_ST_POINTER (Changepointer Timeseries Data Stock)

- FRE_CONS_POINTER (Changepointer Timeseries Data Consumption)

which have way too much entries in them. (about 700.000.000 each).

Even when retrieving the number of entries in SE16, we get a time-out dump.

We like to clean this table making use of the standard SAP Program: FRE_TS_POINTER_REORG

There is a Note for this program (2085247 - Performance improvement for report FRE_TS_POINTER_REORG) which we already have implemented, but still it takes too long to get rid of all the entries.

(We only have a small maintenance-window in which we can perform this action).

It looks like the amount of data is growing faster than the deletion of the records.

My question: why does it take so long to simple delete some (millions) records from a database-table.

In the standard SAP reorg-program, there is a line stating:

DELETE FROM fre_st_pointer WHERE timestamp < pi_timestamp_del


So, why does a simple delete take so much time?

And, Is there another solution to delete (most) of the records, we only want to keep lets say 3 weeks of changepointers and not from ears back.

Add a comment
10|10000 characters needed characters exceeded

Assigned Tags

Related questions

6 Answers

  • Posted on Jul 22, 2015 at 11:44 AM

    Bob,


    Did you try getting the entries deleted from database level (not through program) with the help of Basis team.As a last remedy you can try this option.

    K.Kiran.

    Add a comment
    10|10000 characters needed characters exceeded

  • author's profile photo Former Member
    Former Member
    Posted on Jul 22, 2015 at 01:28 PM

    Bob,

    Did you also notice that there is a select on FRE_ST_POINTER before the deletion happens. The \program needs to select certain records from 7 billion records before the deletion process begins. May be this is the reason why its slow.

    I also noticed that on the select there is a ORDER BY clause. Not sure if I am correct, but I learnt that instead of ordering within the select statement it was better for performance if we select the data and then sort our internal table. May be this is also adding time to it.

    I hope someone will correct me if I am wrong about my understanding about 'order by'.

    V.

    Add a comment
    10|10000 characters needed characters exceeded

    • I also noticed that on the select there is a ORDER BY clause.

      That was in the source before we applied the note.

      After the note, there is only one simple line in the program:

      DELETE FROM fre_st_pointer WHERE timestamp < pi_timestamp_del


      and another option to delete in blocks:


      DO.

      SELECT * FROM fre_st_pointer UP TO pi_block ROWS

      INTO TABLE lt_fre_st_pointer

      WHERE timestamp < pi_timestamp_del.


      DELETE fre_st_pointer FROM TABLE lt_fre_st_pointer.

      ENDDO.


      Basically this is the whole program.


      Both options last forever.

  • Posted on Jul 23, 2015 at 01:53 PM

    Hello Bob,

    I hope you are running the program FRE_TS_POINTER_REORG in background to delete the entries.

    Are you running the job during day time or night?

    I guess it is taking huge time because of Work processes issue.

    Find below documents where Work process is explained in detail.

    Check with your basis team, whether increasing the memory of Non dialog work processes will make an difference to your issue.

    abap/heap_area_total: Total Quota for Heap Memory - SAP Memory Management (BC-CST-MM) - SAP Library

    Memory allocation sequence to work processes

    Background Work Processes Explained - Background Processing - SAP Library

    Regards,

    TP

    Add a comment
    10|10000 characters needed characters exceeded

  • author's profile photo Former Member
    Former Member
    Posted on Jul 23, 2015 at 02:33 PM

    Hi Bob,

    when I encountered a similar problem, I selected and copied the relevant entries (in your case those that are <= 3 weeks old) into a Z table, dropped and recreated the whole standard table (via SE14) and copied them back. Obviously not a clean approach and depending on the customer this may not even be an option. In any case, you should probably get in touch with the basis/dba team responsible to work out a solution.

    Add a comment
    10|10000 characters needed characters exceeded

  • author's profile photo Former Member
    Former Member
    Posted on Aug 21, 2015 at 10:00 AM

    Hi, we have a similar issue. The option to do a drop-table was suggested by someone from SAP (not via proper OSS channel) but we are reluctant to consider that.

    Instead we are looking at using FRE02 with selection on "change-pointer date from/to" to gradually reduce the size of FRE_ST_POINTER and FRE_CONS_POINTER tables to a manageable size.

    We hope this will allow to better manage the chuncks of unnessary stock/cons. change-pointer which we want to delete. Will update this post once Basis-team has tried this.

    Afterwards the reorg.program FRE_TS_POINTER_REORG should be scheduled after each delta-load (see OSS note 1386094): we didn't do this after we started to use FRE03 instead of FRE02 for transferring stock- and consumption-data which has lead to a similar amount of records as the 700 million you noticed. The latter should be the structural solution according to SAP based on note 1386094. Due to a more manageable table-size there should be no performance issues in that case.

    Add a comment
    10|10000 characters needed characters exceeded

  • author's profile photo Former Member
    Former Member
    Posted on Nov 20, 2015 at 08:31 AM

    Hi Bob and others,

    It took a while and some communication with SAP support, but the final answer is a follows four our similar situation:

    First some background: we had about a billion records in each FRE_ST_POINTER and FRE_CONS_POINTER.

    Now, see below the three steps we took in both ensuring that stock and consumption-interface was still doing what it should do, and getting rid of the change-pointer records.

    First we changed(based in instruction of SAP support) customizing so that no new change-pointers were created anymore (SAP Retail -> SPRO: Integration with other SAP Components -> Maintain Basic Settings for Data Transfer) => de-flag "Delta load for distribution centers" and "Delta load for stores" under the Time Series Interface settings.

    As a result no new change-pointers will be be written to tables FRE_ST_POINTER and FRE_CONS_POINTER.

    Secondly, implement OSS Note 2233149 - Consumption data not transfered via FRE03 (transf. general) as in our case trx. FRE03 would after the above mentioned customizing change only transfer stock-data, but no consumption data anymore.

    Third: a truncate was performed on database-level by our basis-team on tables FRE_ST_POINTER and FRE_CONS_POINTER to clear them completely in a quick way.

    (Note: Delete via FRE35 / FRE_TS_POINTER_REORG would have taken weeks if not months with our data volume; FRE35 is fine to use when scheduled directly when FRE03 is used for stock/consumption-data interfacing to F&R. It can then on daily basis clean up all unneccessary change-poinetr records in mentioned two tables. It is however not designed to get rid of months of uncleaned back-log in these tables. FRE35 uses delete SQL-statements which will result in creation of log-files in cases prior to database-commit a rollback has to be performed. This makes the program too sluggish for a billion records clean up)

    Add a comment
    10|10000 characters needed characters exceeded

Before answering

You should only submit an answer when you are proposing a solution to the poster's problem. If you want the poster to clarify the question or provide more information, please leave a comment instead, requesting additional details. When answering, please include specifics, such as step-by-step instructions, context for the solution, and links to useful resources. Also, please make sure that you answer complies with our Rules of Engagement.
You must be Logged in to submit an answer.

Up to 10 attachments (including images) can be used with a maximum of 1.0 MB each and 10.5 MB total.