Skip to Content
avatar image
Former Member

Optimization of mass deletion on HDB

Hi guys,

in a periodical interval i want to reaoranize obsolete data within a table with approx. 3 billion records. Until now I fetch relevant data with a twofold inner join (and cursor) into an itab. Then I delete dbtab from itab. Expected amount of obsolete records per week: 50 million. But as you can imagine, it takes a very long time, approx. 1,5 days.

Now we run our CRM on a HANA database and I think HANA bring some new performance opportunities. I thought about code pushdown to SELECT and DELETE the mentioned records directly on the database.

Do you have any ideas to improve performance? Maybe with CDS views or AMPDs?

Regards,

Daniel

Add comment
10|10000 characters needed characters exceeded

  • Get RSS Feed

1 Answer

  • Jan 27, 2017 at 12:33 AM

    Hi Daniel,

    you may

    1, write a report and run it in paralell in batch job.

    2, create index to accelerate SELECT with inner join

    3, might it be possible to run SQL on HDB directly?

    Spencer

    Add comment
    10|10000 characters needed characters exceeded

    • Former Member

      Hi Spencer,

      thanks for your answer.

      1. In the past we had some performance issues e.g. high CPU usage on HDB because of parallelization. Because my problem is non business critical, I'd like to avoid parallelization on HDB.

      2. I think I can't justify new indices on the three very large tables due to memory (space on HDB) consumption, so this is also not an option.

      And that is the crux, because on Oracle, I would prefer option 1. and 2. :-)

      3. The code example (see below) for selection works. In this case I push my selection up to the application server and then DELETE dbtab from itab. Do you know if it is possible to delete directly within a CDS view? I think that cloud be helpful and increase performance.

      @AbapCatalog.sqlViewName: 'ZV_TEST'
      define view Zcdsv_Test as select from crmd_order_index as orderidx
          inner join crmd_orderadm_i as orderadmi 
              on orderidx.header = orderadmi.header
          inner join crm_jcds as jcds 
              on orderidx.header = jcds.objnr 
              and orderidx.item = jcds.objnr
              {
              jcds.mandt,
              jcds.objnr,
              jcds.stat,
              jcds.chgnr,
              jcds.usnam,
              jcds.udate,
              jcds.utime,
              jcds.cdtcode,
              jcds.inact,
              jcds.chind
              }
              where orderidx.posting_date_hd = '20151022'
                  and orderidx.object_type = 'BUS2000115'
                  and ( orderidx.process_type_ix = 'ABC' or orderidx.process_type_ix = 'XYZ' )
      <br>

      Regards,

      Daniel