Skip to Content
0

Optimization of mass deletion on HDB

Jan 26, 2017 at 04:22 PM

69

avatar image

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

10 |10000 characters needed characters left characters exceeded
* Please Login or Register to Answer, Follow or Comment.

1 Answer

Spencer Liang
Jan 27, 2017 at 12:33 AM
0

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

Show 1 Share
10 |10000 characters needed characters left characters exceeded

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

0