Doubt for optimize abap code

[PTecnico]

Dear experts

How can optimize the following code :

    LOOP AT is_ee_org_ass_repl_req-org_assignment INTO DATA(ls_org_assignment).
	lv_fecha_inicial = ls_org_assignment-start_date.
        lv_fecha_final = ls_org_assignment-end_date.
	lv_posicion	ls_org_assignment-position_id

      SELECT SINGLE sobid  FROM hrp1001 CLIENT SPECIFIED  
        INTO (@DATA(lv_sobid) )
        WHERE  mandt = @SY-mandt and plvar =  '01' and
        objid = @LV_posicion AND ( begda <= lv_fecha_final AND endda >= lv_fecha_final ) AND subty = 'A012'

    ENDLOOP.

In the t,code ST12 appear the following log:

Regards

[Sandra_Rossi]

It's normal that ST12 says that most of the time is database time. You may want to optimize the database access (although you'll still get around 90% database time) by doing only one SELECT instead of several SELECT. As you are probably learning and you're authorized to use old ABAP, try with FOR ALL ENTRIES.

[ArcherZhang · Accepted solution]

DATA: lt_sobid TYPE TABLE OF hrp1001-sobid, lt_posids TYPE TABLE OF hrp1001-objid. " Collect all position IDs first LOOP AT is_ee_org_ass_repl_req-org_assignment INTO DATA(ls_org_assignment). APPEND ls_org_assignment-position_id TO lt_posids. ENDLOOP. " Ensure the table is not empty to prevent runtime errors IF lt_posids IS NOT INITIAL. " Fetch all relevant sobids in a single SELECT statement SELECT sobid FROM hrp1001 CLIENT SPECIFIED INTO TABLE lt_sobid FOR ALL ENTRIES IN lt_posids WHERE mandt = @SY-mandt AND plvar = '01' AND objid = @LT_posids AND begda <= lv_fecha_final AND endda >= lv_fecha_final AND subty = 'A012'. ENDIF. " Processing the results as needed LOOP AT lt_sobid INTO DATA(ls_sobid). " Your logic here using ls_sobid ENDLOOP. Key changes: Use of FOR ALL ENTRIES: This minimizes the number of database calls by collecting all position IDs first. Batch processing: Fetch results using a single database operation. Defensive programming: Check if the table carrying data to fetch (lt_posids) is not initial. Make sure to adjust the final logic loop as per your requirement with the lt_sobid results. This approach reduces database load significantly and should improve performance.

[Sandra_Rossi · Accepted solution]

Please use the buttons "..." and "</>" to display your code nicely, to make it legible. See how to do it here: https://community.sap.com/t5/questions-about-sap-websites/how-to-post-code-in-sap-community-gt-2024/...

[Sandra_Rossi · Accepted solution]

You forgot to format your code, let me do it for you (+ missing arobase added):

DATA lt_sobid  TYPE TABLE OF hrp1001-sobid.
DATA lt_posids TYPE TABLE OF hrp1001-objid.

" Collect all position IDs first 
LOOP AT is_ee_org_ass_repl_req-org_assignment INTO DATA(ls_org_assignment).
  APPEND ls_org_assignment-position_id TO lt_posids.
ENDLOOP.

" Ensure the table is not empty to prevent runtime errors 
IF lt_posids IS NOT INITIAL.
  " Fetch all relevant sobids in a single SELECT statement 
  SELECT sobid
    FROM hrp1001 CLIENT SPECIFIED
    INTO TABLE @LT_sobid
    FOR ALL ENTRIES IN @LT_posids
    WHERE mandt  = @SY-mandt
      AND plvar  = '01'
      AND objid  = @LT_posids
      AND begda <= @LV_fecha_final
      AND endda >= @LV_fecha_final
      AND subty  = 'A012'.
ENDIF.

" Processing the results as needed 
LOOP AT lt_sobid INTO DATA(ls_sobid).
  " Your logic here using ls_sobid 
ENDLOOP.

[keremkoseoglu]

On a Hana system, you can inner join the ITAB with the database table directly; without FOR ALL ENTRIES.

select  
  from is_ee_org_ass_repl_req-org_assignment as _oa
       inner join hrp1001 
               on hrp1001~plvar = '01' and
                  hrp1001~subty = 'A012' and
                  hrp1001~objid = _oa~position_id and
                  hrp1001~begda <= _oa~start_date and
                  hrp1001~endda >= _oa~end_date
  fields _oa~position_id,hrp1001~sobid
  into table @DATA(results).

[PTecnico]

Hi

I have used the code

DATA: lt_sobid TYPE TABLE OF hrp1001-sobid, lt_posids TYPE TABLE OF hrp1001-objid.

" Collect all position IDs first

LOOP AT is_ee_org_ass_repl_req-org_assignment INTO DATA(ls_org_assignment2).
 APPEND ls_org_assignment2-position_id TO lt_posids.
ENDLOOP.
 " Ensure the table is not empty to prevent runtime errors

  IF lt_posids IS NOT INITIAL.
   " Fetch all relevant sobids in a single SELECT statement
   SELECT sobid FROM hrp1001 CLIENT SPECIFIED
      INTO TABLE lt_sobid FOR ALL ENTRIES IN lt_posids
       WHERE mandt = SY-mandt  AND
             plvar = '01'       AND
             objid = LT_posids AND
             begda <= lv_fecha_final AND
             endda >= lv_fecha_final AND
             subty = 'A012'.
   ENDIF.

   " Processing the results as needed
   LOOP AT lt_sobid INTO DATA(ls_sobid).
   " Your logic here using ls_sobid
   ENDLOOP.

and this generated error:

1 errors found. Activate anyway? Line 171: "LT_POSIDS" cannot be a table or a reference and cannot contain either of these objects or strings.

Regards

[Sandra_Rossi]

@PTecnico The line 171 in your system corresponds to the line 16 in the code you have posted here, "objid = LT_posids" is missing the component of LT_POSID that you want to compare. This internal table has lines without structure ("TYPE TABLE OF hrp1001-objid"), so you have to indicate the Pseudo-Component "TABLELINE" -> "objid = LT_posids-tableline".

[Sandra_Rossi]

@keremkoseoglu For information, your SELECT is missing the arobase in front of the internal table "is_ee_org_ass_repl_req-org_assignment".

[PTecnico]

Hi Sandra, I have checked you last response, excuse me , and I dont understand What is the change that I should apply,

[Sandra_Rossi]

@PTecnico Did you read the ABAP documentation to see what "Pseudo-Component" means? Let's cross the Ts. The concerned line must be:

             objid = LT_posids-table_line AND

[PTecnico]

This is code of ArcherZhang in the format recommed by Sandra_Rossi

DATA: lt_sobid TYPE TABLE OF hrp1001-sobid, lt_posids TYPE TABLE OF hrp1001-objid. 

" Collect all position IDs first 

LOOP AT is_ee_org_ass_repl_req-org_assignment INTO DATA(ls_org_assignment).
 APPEND ls_org_assignment-position_id TO lt_posids.
ENDLOOP.
 " Ensure the table is not empty to prevent runtime errors

  IF lt_posids IS NOT INITIAL.
   " Fetch all relevant sobids in a single SELECT statement 
   SELECT sobid FROM hrp1001 CLIENT SPECIFIED 
      INTO TABLE lt_sobid FOR ALL ENTRIES IN lt_posids
       WHERE mandt = @SY-mandt  AND 
             plvar = '01'       AND 
             objid = @LT_posids AND 
             begda <= lv_fecha_final AND
             endda >= lv_fecha_final AND
             subty = 'A012'.
   ENDIF. 

   " Processing the results as needed 
   LOOP AT lt_sobid INTO DATA(ls_sobid). 
   " Your logic here using ls_sobid 
   ENDLOOP.
    " Key changes: 
    " Use of FOR ALL ENTRIES: This minimizes the number of database calls by collecting all position IDs first.
    " Batch processing: Fetch results using a single database operation.
     "Defensive programming: Check if the table carrying data to fetch (lt_posids) is not initial.
    " Make sure to adjust the final logic loop as per your requirement with the lt_sobid results.
    " This approach reduces database load significantly and should improve performance.