topic Re: performance issue - processing over a million records in Application Development and Automation Discussions

performance issue - processing over a million records

Former Member — Wed, 10 Nov 2010 13:19:08 GMT

Hi SDN

there's a file of over million records sitting in the application server.

i'm reading the file into an internal table and passing batches of 100,000 records at a time to the standard BAPI BAPI_ACC_GL_POSTING_POST.

after entire file is processed successfully, and only if no errors exist, i'm doing the COMMIT WORK.

but due to this the roll back area occupies lot of memory and the processing time took is 6hrs. sometime it ends in memory shortage & the job gets cancelled.

Basis team asks me to do the COMMIT WORK frequently to avoid more memory consumption of rollback area which inturn increase the performance.

but the problem is in case of an error in the file and some records are already posted by then with COMMIT WORK. how to roll back those documents.

will COMMIT WORK AND WAIT serves the purpose.

awaiting your suggestions.

Thanks,

Suman.

Re: performance issue - processing over a million records

HermannGahm — Wed, 10 Nov 2010 13:25:27 GMT

Hi,

> will COMMIT WORK AND WAIT serves the purpose.

No.

You have done everything fine. In general a commit should be done when a business transaction is completed.

That is when all your records are processed, exactly like you did it. The system administrators have to try their best

to provide a setup that can handle frequently used business transactions (is your case a frequently used one?)

Once we decide to commit in between a business transaction we have to implement a restart logic... that would

then be your task to create a logging for your batches... which ones are commited which ones are not... and in case

of problems... restart and process only those ones that have not been processed so far.

Kind regards,

Hermann

Re: performance issue - processing over a million records

ThomasZloch — Wed, 10 Nov 2010 14:22:42 GMT

I would go for Hermann's second suggestion, implement a restart functionality.

This logic works fine for me in a similar setting:

say 100,000 items to be posted:

- store all in a Z-table,

- commit work

loop:

- bundle 100 into a document and post with BAPI

- update these 100 items in Z table with status and or document number

- commit work (or rollback)

If anything bombs along the way, you can restart for those items in the Z-table that don't have correct status or missing document number. The Z-table can be reorganized regularly.

Thomas

Addition: the nice thing about the loop part above is that these blocks can be started in parallel using aRFC technique

Re: performance issue - processing over a million records

Former Member — Mon, 15 Nov 2010 14:27:16 GMT

Thanks Thomas.

i've applied frequent commit works for every 990 records.

now i'm trying to apply parallel processing on it.

it's creating few documents. thereafter it's falling over with "memory no more paging" dumps.

in the logic i applied the condition to wait until the work processes are free & also the received results >= send tasks.

but still it's falling over.

Pls check the below code.

suggest me for any corrections. once all the work processes are fully occupied, i need to wait for the work to be done, then pass the next batch of records for posting.

*-- Posting
    if lv_free_workproc = 0.
      do.
        CALL FUNCTION 'SPBT_GET_CURR_RESOURCE_INFO'
          IMPORTING
            free_pbt_wps                = lv_free_workproc
          EXCEPTIONS
            internal_error              = 1
            pbt_env_not_initialized_yet = 2
            OTHERS                      = 3.
        IF lv_free_workproc > 0.
          EXIT.
        ELSE.
          lv_seconds = '1'.
***          WAIT UNTIL lv_free_workproc > 0 UP TO lv_seconds SECONDS.
              DO.
                lv_seconds = '1'.
                WAIT UNTIL rcv_jobs >= snd_jobs UP TO lv_seconds SECONDS.
                IF rcv_jobs >= snd_jobs OR sy-index = 60.
                  EXIT.
                ENDIF.
              ENDDO.
*          MESSAGE ID sy-msgid TYPE sy-msgty NUMBER sy-msgno
*                  WITH sy-msgv1 sy-msgv2 sy-msgv3 sy-msgv4.
        ENDIF.
        ENDDO.
      endif.

        CALL FUNCTION 'ZGL_ACCOUNT_POSTING' STARTING NEW TASK lv_taskname
          DESTINATION IN GROUP lv_server_grp
          PERFORMING return_pproc_result ON END OF TASK
          EXPORTING
            is_documentheader     = ls_doc_hdr
          TABLES
            it_accountgl          = lt_gl_account
            it_currencyamount     = lt_currency
            it_extension1         = lt_ext1
          EXCEPTIONS
            system_failure        = 1
            communication_failure = 2
            resource_failure      = 3.
        CASE sy-subrc.
          WHEN 0.
            snd_jobs = snd_jobs + 1.
            lv_free_workproc = lv_free_workproc - 1.
          WHEN 1 OR 2.
            MESSAGE 'Error' TYPE 'I'.
          WHEN 3.
            IF snd_jobs >= 1 AND
               exc_flag = 0.
              exc_flag = 1.
              DO.
                lv_seconds = '1'.
                WAIT UNTIL rcv_jobs >= snd_jobs UP TO lv_seconds SECONDS.
                IF rcv_jobs >= snd_jobs OR sy-index = 60.
                  EXIT.
                ENDIF.
              ENDDO.
              ENDIF.
              IF sy-subrc = 0.
                exc_flag = 0.
              ELSE.
                MESSAGE 'Resource failure' TYPE 'I'.
              ENDIF.
            WHEN OTHERS.
              MESSAGE 'Other error' TYPE 'I'.
          ENDCASE.

Kind Regards,

Suman.

Edited by: Thomas Zloch on Nov 15, 2010 3:40 PM - code tags added

Re: performance issue - processing over a million records

ThomasZloch — Mon, 15 Nov 2010 15:18:45 GMT

As for the memory problem, make sure that after each call you are clearing all internal tables that you use to collect document data for one posting.

As for your code snippet, hard to judge from the distance, let me briefly outline my flow logic instead:

call function SPBT_INITIALIZE once to determine proc_max
proc_use = proc_max - n   (n = number of work processes to remain available)
loop at items
  bundle x items into one document, once document complete:
  do
    wait until proc_active < proc_use up to 1 seconds
    call Z-function starting new task ... performing end_of_arfc at end of task.
    if sy-subrc = 0
      add 1 to proc_active
      exit
    elseif sy-subrc = 3
      stay in do-loop
    else
      call z-function the "normal way" (no aRFC)
      exit
    endif
  enddo
  clear document data!
endloop

form end_of_arfc
  receive results ...
  subtract 1 from proc_active
endform

Thomas