Performance issue using Select single statment inside loop

[Former Member]

Hi Experts,

            I am working on PMS module of HR. I have a requirement that for each pernr, i have to fetch KRA status and sub status from tables HRHAP_APPEE and HRHAP, for this I am using inner join statement. This seems to be the reason for decreasing performance of my program but I dont see other way to work around with this. Following are few lines of code I have written.

   CALL FUNCTION 'GET_CURRENT_YEAR'
      EXPORTING
        bukrs = wa_0001-bukrs
        date  = sy-datum
      IMPORTING
        curry = lv_fsyear.

*-- Assigning Start Date
    CONCATENATE lv_fsyear
                         '01'
                         '04' INTO lv_sdate.
    CONDENSE: lv_sdate.


*-- Assigning Start Date
*          lv_fsyear_t = lv_fsyear + 1.

    CONCATENATE lv_fsyear
                '09'
                '30' INTO lv_edate.
    CONDENSE: lv_edate.

    SELECT SINGLE  a~plan_version
                   a~appraisal_id
                   a~type
                   a~id
                   a~combined_key
                   b~ap_start_date
                   b~ap_end_date
                   b~ap_status
                   b~ap_status_sub
                   b~change_date
                   b~change_time
               INTO wa_hrhap_appee
              FROM hrhap_appee AS a
              INNER JOIN hrhap AS b
              ON a~plan_version = b~plan_version
              AND a~appraisal_id = b~appraisal_id
              "CLIENT SPECIFIED
              WHERE a~id = wa_emp_app-emp_no
*           AND b~ap_status = '5'
              AND b~ap_start_date >= lv_sdate
              AND b~ap_start_date <= lv_edate.

I also tried replacing above select statement with below lines, but it doesn't seem to work and I am not getting appropriate result.

V_APPRAISEE_ID = wa_emp_app-emp_no
SELECT SINGLE * FROM HRHAP_APPEE CLIENT SPECIFIED INTO wa_HRHAP_APPEE01
WHERE MANDT = SY-MANDT
AND ID = V_APPRAISEE_ID.
SELECT SINGLE * FROM HRHAP CLIENT SPECIFIED INTO wa_hrhap
FOR ALL ENTRIES IN IT_HRHAP_APPEE01
WHERE MANDT = SY-MANDT
AND PLAN_VERSION = wa_HRHAP_APPEE01-PLAN_VERSION
AND APPRAISAL_ID = wa_HRHAP_APPEE01-APPRAISAL_ID
AND AP_STATUS = '5'
AND AP_START_DATE ge LV_SDATE
AND AP_START_DATE le LV_EDATE.

.
Please suggest me a way to optimize this program's performance.

Earlier responses would be much appreciated.

Thanks

Uday

<<Moderator Message : Added Code tags>>

Message was edited by: Kesavadas Thekkillath

[Puneet_Gupta]

Hi Uday,

Use function modules HRHAP_RFC_DOCUMENT_GET_LIST and then HRHAP_RFC_DOCUMENT_GET_DETAIL to get the data at one go instead of reading each record in a loop.

- Puneet

[Former Member]

Hi Puneet,

          Sorry but these function modules doesn't seem have increased my program's performance. Any other suggestion please.

Thanks

Uday

[Puneet_Gupta]

Hi Uday,

Are you calling these functions in a loop or are you getting the data for all employees at one go ?

-Puneet

[Former Member]

yes puneet,

       i am calling these function modules inside loop because it has lot of dependent fields within the loop.

[JJosh]

DEFINE 2 STRUCTURES FOR THE INTERNAL TABLES

LT_HRHAP_APPEE AND LT_HRHAP

Structure should only have the fields to be selected and in the Select Query, fetch the fields in order as mentioned in the tables and pass the key fields to the WHERE conditions.

SELECT SINGLE plan_version

                          appraisal_id

                          type

                          id

                          combined_key

INTO lt_hrhap_appee

FROM hrhap_appee

WHERE MANDT = SY-MANDT 

     AND ID = V_APPRAISEE_ID. 

IF lt_hrhap_appee[] IS NOT INITIAL.

SORT lt_hrhap_appee BY <<<KEY FIELDS>>>

SELECT SINGLE ap_start_date 

                          ap_end_date 

                          ap_status 

                          ap_status_sub 

                          change_date 

                          change_time 

INTO LT_HRHAP

FROM HRHAP

FOR ALL ENTRIES IN lt_hrhap_appee

WHERE  PLAN_VERSION = LT_HRHAP_APPEE-PLAN_VERSION 

     AND APPRAISAL_ID = LT_HRHAP_APPEE-APPRAISAL_ID 

    AND AP_STATUS = '5'  

    AND AP_START_DATE ge LV_SDATE 

    AND AP_START_DATE le LV_EDATE. 

ENDIF.

IF LT_HRHAP[] IS NOT INITIAL.

   << PROCEED WITH YOUR LOGIC >>

ENDIF.