topic Re: Modify DB in chunk in Application Development and Automation Discussions

Modify DB in chunk

Former Member — Mon, 04 Jan 2010 09:05:48 GMT

HI All ,

I have large internal table (like 1000000 records ) and i want to modify DB table from it ,

what i want is to do the modify in chunk i.e. to do commit after 1000 recorded each time .

what is the best way to do that ?

Regards

Chris

Re: Modify DB in chunk

Former Member — Mon, 04 Jan 2010 09:15:12 GMT

It depends on how your report is arranged in order to improve the performance, anyway if I need to considere the internal table only, probably it should create a package with 1000 hits:

DESCRIBE TABLE ITAB_MAIN LINES TOT_RECORDS.

FROM_INDEX = 1.
IF TOT_RECORDS > 1000.
  TO_INDEX      = 1000.
ELSE.
  TO_INDEX      = TOT_RECORDS.
ENDIF.

DO.
  APPEND LINES OF ITAB_MAIN TO ITAB FROM FROM_INDEX TO TO_INDEX.
  UPDATE <TABLE> FROM ITAB.
  COMMIT WORK.
  FROM_INDEX = TO_INDEX + 1.
  IF FROM_INDEX > TOT_RECORDS.
    EXIT.
  ENDIF.
  TO_INDEX = TO_INDEX + 1000.
   IF FROM_INDEX > TOT_RECORDS.
      FROM_INDEX = TOT_RECORDS.
   ENDIF.
   REFRESH ITAB.
ENDDO.

Max

Re: Modify DB in chunk

Former Member — Mon, 04 Jan 2010 09:20:58 GMT

Hi ,

You can move 1000 records from your internal table to temporary internal table.

Now you can move records from this temporary internal table to database table.

Hope this helps you.

Re: Modify DB in chunk

Former Member — Mon, 04 Jan 2010 09:32:07 GMT

HI Max,

Thanks,

I check it and let you know .

I have some additional questions ,

1. to do the modify in chunk it is good approach ?

2. i use modify from table in my program do i need to do read before update and just to modify the changes records

3. the DO statements is not risky (like go to infinite loop ).

Thanks a lot and best regards

Chris

Re: Modify DB in chunk

awin_prabhu — Mon, 04 Jan 2010 09:32:31 GMT

Hi Chris,

You can try like below also,


DATA: itab TYPE TABLE OF mara,
           wa TYPE mara,
           cnt TYPE i VALUE 1000.

SELECT * FROM mara INTO TABLE itab WHERE vpsta = 'K'.

LOOP AT itab INTO wa.
  wa-vpsta = 'KL'.
  MODIFY mara FROM wa.
  IF sy-tabix = cnt.
    CALL FUNCTION 'DB_COMMIT'.
    cnt = cnt + 1000.
  ENDIF.
ENDLOOP.

Thanks,

Re: Modify DB in chunk

Former Member — Mon, 04 Jan 2010 09:58:08 GMT

HI ,

One more thing when i try to use append lines i get this error

APPEND LINES OF it_fil_us_data TO lt_chunk_table

FROM lv_from_index TO lv_to_index .

Regards

Chris

".", "ASSIGNING <fs>", "REFERENCE INTO data-reference", or "ASSIGNING

<fs> CASTING" expected after "LT_CHUNK_TABLE".

Re: Modify DB in chunk

Former Member — Mon, 04 Jan 2010 10:07:49 GMT

HI Max,

Ignore my last post

the solution is

APPEND LINES OF it__fil_usrs_data

FROM lv_from_index TO lv_to_index TO lt_chunk_table.

do you have some answers to my previous questions ?

Thanks and Regards

Chris

Re: Modify DB in chunk

Former Member — Mon, 04 Jan 2010 10:45:19 GMT

I have some additional questions ,

1. to do the modify in chunk it is good approach ?

MODIFY is INSERT+UPDATE, that means if a record in internal table doesn't exist it'll be inserted: that can be or can't be a risk: I don't know your goal.

I prefer to use UPDATE if I needs to update existing record only, so if perpahs a wrong record is loaded in the internal table it won't be inserted, and so I can avoid to do a select to check if a record exists or doesn't exists.

But if you doesn't need to care about it and so u need to insert and update a record: MODIFY is better.

2. i use modify from table in my program do i need to do read before update and just to modify the changes records

This is the concept below: if you need to change the existing records only, u make sure the record uploaded in the internal tables are in dictionary table too: how to do it depends on your program, i.e. how u've uploaded them, else u should check it before using MODIFY

3. the DO statements is not risky (like go to infinite loop ).

Every time a do cycle is used it needs to use a condition exit: in my sample I've used a counter... else a infinite loop is sure.

Max

Re: Modify DB in chunk

Sm1tje — Mon, 04 Jan 2010 11:01:42 GMT

Well, maybe it's just me, but I for one would like to know first if this is a standard SAP table or a custom table. Or to put it more general, does this DB table have any direct relationship (header and item table for example) with other DB tables. So when updating (modifying) one table, do you 'mess up' the referential integrity?

Re: Modify DB in chunk

Former Member — Mon, 04 Jan 2010 12:32:12 GMT

The table is stand alone ץ

Regards

Chris