topic Re: Performance issue using Select single statment inside loop in Application Development and Automation Discussions

Performance issue using Select single statment inside loop

Former Member — Thu, 28 Jun 2012 09:07:31 GMT

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

Re: Performance issue using Select single statment inside loop

Puneet_Gupta — Tue, 03 Jul 2012 21:37:42 GMT

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

Re: Performance issue using Select single statment inside loop

Former Member — Thu, 12 Jul 2012 08:00:07 GMT

Hi Puneet,

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

Thanks

Uday

Re: Performance issue using Select single statment inside loop

Puneet_Gupta — Thu, 12 Jul 2012 16:47:13 GMT

Hi Uday,

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

-Puneet

Re: Performance issue using Select single statment inside loop

Former Member — Fri, 13 Jul 2012 05:32:07 GMT

yes puneet,

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

Re: Performance issue using Select single statment inside loop

JJosh — Wed, 18 Jul 2012 04:11:46 GMT

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

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.