Skip to Content

Archived discussions are read-only. Learn more about SAP Q&A

performance of the SQL

Hi ABAP Experts,

good day all,

iam working as BI consultant as per user requirement some of the fields are not there i enhanced to R/3 by using cmod . i have written some code every thing fien .but its very bad performance almost its taking more than 24 hrs taking . here i have written many SQL statement . can any one suggest me how to improve the performance of code.

  • Tables declaration for EMPLOYEE_ATTR

TABLES: PA0185,HRP1001,PA0001,PA0588, PA0587, PA0021, PA0006, PA0009, PA0003.

TABLES: HRMS_BIW_IO_OCCUPANCY.

TABLES: BWCO_MD_CCTR.

TABLES: BIW_KNA1_S,KNB1.

  • Internal table - Types -- Declaration for COSTCENTER_ATTR

TYPES : BEGIN OF T_PERNR,

PERNR TYPE PERSNO,

END OF T_PERNR.

DATA : IT_PERNR TYPE STANDARD TABLE OF T_PERNR.

DATA : VL_COUNT TYPE I.

  • data : s_area like pa0001-mstbr.

  • Declarations for 0EMPLOYEE_ATTR

DATA : I_W_DATA LIKE HRMS_BIW_IO_OCCUPANCY,

I_W_DATA_KOSTL LIKE BWCO_MD_CCTR,

L_ICNUM TYPE PA0185-ICNUM,

L_RMBEGDA TYPE PA0185-BEGDA,

L_RMENDDA TYPE PA0185-ENDDA,

L_PERNR TYPE PA0001-PERNR,

L_PERNR1 TYPE PA0001-PERNR,

L_PLANS TYPE SOBID,

L_PLANS2 TYPE SOBID,

L_SUBTY TYPE PA0185-SUBTY,

L_NAME TYPE PA0001-ENAME,

L_NAME1 TYPE PA0001-ENAME,

L_AMOUNT TYPE BETRG,

L_AMOUNT_OFF TYPE BETRG,

L_ORGUNITSVP TYPE PA0001-ORGEH,

L_ICNUM_RM TYPE PA0185-ICNUM,

L_ICNUM_TIER2 TYPE PA0185-ICNUM,

L_MSTBR TYPE PA0001-MSTBR,

L_ICNUM_21 TYPE PA0185-ICNUM,

L_ICNUM_20 TYPE PA0185-ICNUM,

L_ICNUM_24 TYPE PA0185-ICNUM,

L_ICNUM_18 TYPE PA0185-ICNUM,

L_ISSPL TYPE PA0185-ISSPL,

L_ESINO LIKE PA0588-ESINO,

L_EEBAS LIKE PA0587-TSTID,

I_W_DATA1 LIKE BIW_KNA1_S,

L_TLFXS LIKE KNB1-TLFXS,

L_NAME4 LIKE KNA1-NAME4,

  • new fields added by pandu

L_GBDAT TYPE GBDAT,

L_FAVOR TYPE PAD_VORNA,

L_NAME2 TYPE PAD_CONAM,

L_STRAS TYPE PAD_STRAS,

L_PSTLZ TYPE PSTLZ_HR,

L_ORT01 TYPE PAD_ORT01,

L_USRID TYPE SYSID,

L_XMETXT TYPE REBDXRO,

L_BANKL TYPE BANKK,

L_BANKN TYPE BANKN,

L_CNAME TYPE ZCNAME,

L_USRID1 TYPE SYSID,

L_MGRID TYPE SOBID,

L_INCDAT TYPE BEGDA,

L_LIFNR TYPE LIFNR,

L_RDATE TYPE BEGDA,

L_LWDAY TYPE BEGDA,

L_REDAT TYPE BEGDA,

L_SCHKZ TYPE SCHKN,

L_PRDAT TYPE PRDAT,

L_ABRDT TYPE LABRD,

L_ABRSP TYPE ABRSP,

L_BANKA TYPE BANKA,

L_SMDAT TYPE BEGDA.

  • l_ctc_amt type betrg.

*endchange

DATA: BEGIN OF TP_RGDIR OCCURS 0.

INCLUDE STRUCTURE PC261.

DATA: END OF TP_RGDIR.

***************************************

*Decleration for monthly salary

DATA : L_PA0008 LIKE PA0008.

DATA : V_AMOUNT TYPE PAD_AMT7S,

FLDNAM(30),

NUM(2) VALUE '01'.

FIELD-SYMBOLS: <FS> TYPE ANY.

FIELD-SYMBOLS: <FS1> TYPE ANY.

*****************************************

CASE I_DATASOURCE.

*Getting Customer branch in 0customer_attr

WHEN '0CUSTOMER_ATTR'.

LOOP AT I_T_DATA.

MOVE-CORRESPONDING I_T_DATA TO I_W_DATA1 .

*Getting Customer branch in 0customer_attr

SELECT SINGLE TLFXS INTO L_TLFXS FROM KNB1 WHERE KUNNR EQ I_W_DATA1-KUNNR.

*Getting Customer Name4 in 0customer_attr from table KNA1

SELECT SINGLE NAME4 INTO L_NAME4 FROM KNA1 WHERE KUNNR EQ I_W_DATA1-KUNNR.

I_W_DATA1-ZZTLFXS = L_TLFXS.

I_W_DATA1-ZZNAME4 = L_NAME4.

MODIFY I_T_DATA FROM I_W_DATA1.

CLEAR L_TLFXS.

CLEAR L_NAME4.

ENDLOOP.

  • Getting the RM for the employee and SVP using the ICNUM

WHEN '0EMPLOYEE_ATTR'.

LOOP AT I_T_DATA INTO I_W_DATA.

SELECT SINGLE ICNUM INTO L_ICNUM FROM PA0185

WHERE PERNR EQ I_W_DATA-PERNR

AND SUBTY = '23'

AND ENDDA GE SY-DATUM.

  • Getting the Joining date of RM

SELECT SINGLE BEGDA INTO L_RMBEGDA FROM PA0000

WHERE PERNR EQ I_W_DATA-PERNR

AND MASSN EQ '01'.

  • Getting the Resignation Date of RM

SELECT SINGLE BEGDA INTO L_RMENDDA FROM PA0000

WHERE PERNR EQ I_W_DATA-PERNR

AND MASSN EQ '14'.

*Getting the Tier II code and Name

SELECT SINGLE SOBID INTO L_PLANS FROM HRP1001

WHERE OTYPE EQ 'S'

AND OBJID EQ I_W_DATA-PLANS

AND RSIGN EQ 'A'

AND RELAT EQ '002'

AND ENDDA GE I_W_DATA-ENDDA

  • AND endda GE sy-datum

AND SCLAS EQ 'S'.

  • AND sobid EQ i_w_data-plans.

*

SELECT SINGLE PERNR INTO L_PERNR FROM PA0001

WHERE ENDDA GE SY-DATUM

  • WHERE endda GE i_w_data-endda

AND PLANS EQ L_PLANS.

SELECT SINGLE ENAME INTO L_NAME FROM PA0001

WHERE ENDDA GE SY-DATUM

AND PLANS EQ L_PLANS.

*Getting the TIER II branch

SELECT SINGLE ICNUM INTO L_ICNUM_TIER2 FROM PA0185

WHERE ENDDA GE SY-DATUM

AND PERNR EQ L_PERNR

AND SUBTY = '05'.

*Getting the Supervisor/HOD for Employee----


SELECT SINGLE MSTBR FROM PA0001 INTO L_MSTBR WHERE PERNR = I_W_DATA-PERNR AND

ENDDA GE SY-DATUM.

*Getting the Employee Product from infotype 185 styp 21.

SELECT SINGLE ICNUM INTO L_ICNUM_21 FROM PA0185

WHERE ENDDA GE SY-DATUM

AND PERNR EQ I_W_DATA-PERNR

AND SUBTY = '21'.

*Getting the Employee Branch Discription/Place of issue from infotype 185 styp 05.

SELECT SINGLE ISSPL INTO L_ISSPL FROM PA0185

WHERE ENDDA GE SY-DATUM

AND PERNR EQ I_W_DATA-PERNR

AND SUBTY = '05'.

*Getting the Employee Library no/business from infotype 185 styp 18.

SELECT SINGLE ICNUM INTO L_ICNUM_18 FROM PA0185

WHERE ENDDA GE SY-DATUM

AND PERNR EQ I_W_DATA-PERNR

AND SUBTY = '18'.

*Getting the Employee Department from infotype 185 styp 20.

SELECT SINGLE ICNUM INTO L_ICNUM_20 FROM PA0185

WHERE ENDDA GE SY-DATUM

AND PERNR EQ I_W_DATA-PERNR

AND SUBTY = '20'.

*Getting the Employee RBT Codefrom infotype 185 styp 24.

SELECT SINGLE ICNUM INTO L_ICNUM_24 FROM PA0185

WHERE ENDDA GE SY-DATUM

AND PERNR EQ I_W_DATA-PERNR

AND SUBTY = '24'.

*Getting the Employee ESI number from Infotype 588.

SELECT SINGLE ESINO INTO L_ESINO FROM PA0588

WHERE ENDDA GE SY-DATUM

AND PERNR EQ I_W_DATA-PERNR.

*Getting the Employee PF Eligibility no from Infotype 587.

SELECT SINGLE TSTID INTO L_EEBAS FROM PA0587

WHERE ENDDA GE SY-DATUM

AND PERNR EQ I_W_DATA-PERNR.

*Getting the Employee Basic salary amt from Infotype 0008.

NUM = '01'.

CLEAR V_AMOUNT.

SELECT SINGLE * INTO L_PA0008 FROM PA0008 WHERE PERNR EQ I_W_DATA-PERNR

AND ENDDA EQ '99991231'.

DO 30 TIMES.

IF STRLEN( NUM ) = 1.

CONCATENATE '0' NUM INTO NUM.

ENDIF.

CONCATENATE 'L_PA0008-LGA' NUM INTO FLDNAM.

ASSIGN (FLDNAM) TO <FS>.

CONCATENATE 'L_PA0008-BET' NUM INTO FLDNAM.

ASSIGN (FLDNAM) TO <FS1>.

IF <FS> = '1000'.

V_AMOUNT = <FS1>.

ENDIF.

CLEAR : <FS>, <FS1>.

NUM = NUM + 1.

ENDDO.

I_W_DATA-ZZBASPAY = V_AMOUNT.

  • endif.

  • HRMS_BIW_IO_OCCUPANCY-ZZMSTBR = s_area.

*----


  • select single mstbr into l_mstbr from pa0001

  • where endda GE sy-datum

  • and pernr eq l_pernr.

*Bringing RM branch

SELECT SINGLE ICNUM INTO L_ICNUM_RM FROM PA0185

WHERE ENDDA GE SY-DATUM

AND PERNR EQ I_W_DATA-PERNR

AND SUBTY = '05'.

  • new code for fields by pandu

*bringing date-of-birth

SELECT SINGLE GBDAT INTO L_GBDAT FROM PA0002 WHERE PERNR = I_W_DATA-PERNR.

  • bringing fathers name

SELECT SINGLE FAVOR INTO L_FAVOR FROM PA0021 WHERE PERNR = I_W_DATA-PERNR

AND SUBTY = '2'.

  • bringing address details

SELECT SINGLE * FROM PA0006 WHERE PERNR = I_W_DATA-PERNR

AND SUBTY = '2'

AND ENDDA GE SY-DATUM.

L_NAME2 = PA0006-NAME2.

L_STRAS = PA0006-STRAS.

L_ORT01 = PA0006-ORT01.

L_PSTLZ = PA0006-PSTLZ.

  • bringing communication dteails

SELECT SINGLE USRID INTO L_USRID FROM PA0105 WHERE PERNR = I_W_DATA-PERNR

AND SUBTY = '0010'

AND ENDDA GE SY-DATUM.

  • bringing location details

SELECT SINGLE XMETXT INTO L_XMETXT FROM PA9005 WHERE PERNR = I_W_DATA-PERNR

AND ENDDA GE SY-DATUM.

  • bringing bank details

SELECT SINGLE * FROM PA0009 WHERE PERNR = I_W_DATA-PERNR

AND ENDDA GE SY-DATUM.

L_BANKN = PA0009-BANKN.

L_BANKL = PA0009-BANKL.

  • bringing the bankname

SELECT SINGLE BANKA INTO L_BANKA FROM BNKA WHERE BANKL = L_BANKL.

  • bringing esop details

SELECT SINGLE CNAME INTO L_CNAME FROM PA9008 WHERE PERNR = I_W_DATA-PERNR

AND ENDDA GE SY-DATUM.

  • bringing vendor details

SELECT SINGLE LIFNR INTO L_LIFNR FROM LFB1 WHERE PERNR = I_W_DATA-PERNR.

SELECT SINGLE CTC_DATE INTO L_INCDAT FROM PB9007 WHERE REPORT = I_W_DATA-PERNR

AND ENDDA GE SY-DATUM.

*select single ctc_amt into l_ctc_amt from pb9007 where report = i_w_data-pernr

  • and endda ge sy-datum.

*l_incdat = pa9007-ctc_date.

*l_ctc_amt = pa9007-ctc_amt.

  • bringing resignation date

SELECT SINGLE BEGDA FROM PA0000 INTO L_RDATE WHERE PERNR = I_W_DATA-PERNR

AND ENDDA GE SY-DATUM

AND MASSN = '14'.

  • to get last working day

L_LWDAY = L_RDATE - 1.

  • to get reentry date

SELECT SINGLE BEGDA FROM PA0000 INTO L_REDAT WHERE PERNR = I_W_DATA-PERNR

AND ENDDA GE SY-DATUM

AND MASSN = '15'.

  • bringing workschedule details

SELECT SINGLE SCHKZ FROM PA0007 INTO L_SCHKZ WHERE PERNR = I_W_DATA-PERNR

AND ENDDA GE SY-DATUM.

  • bringing payroll status details

SELECT SINGLE * FROM PA0003 WHERE PERNR = I_W_DATA-PERNR

AND ENDDA GE SY-DATUM.

L_PRDAT = PA0003-PRDAT.

L_ABRDT = PA0003-ABRDT.

L_ABRSP = PA0003-ABRSP.

  • bringing paydate

SELECT SINGLE BEGDA INTO L_SMDAT FROM PA0008 WHERE PERNR = I_W_DATA-PERNR

AND ENDDA GE SY-DATUM.

*endadd

  • Getting wage type for the salaries calc

I_W_DATA-ZZRMID = L_ICNUM.

I_W_DATA-ZZICNUM = L_ICNUM_RM.

I_W_DATA-ZZRMBEGDA = L_RMBEGDA.

I_W_DATA-ZZRMENDDA = L_RMENDDA.

I_W_DATA-ZZEVPID = L_PERNR.

I_W_DATA-ZZEVPNAME = L_NAME.

I_W_DATA-ZZORGUTSVP = L_ICNUM_TIER2.

I_W_DATA-ZZMSTBR = L_MSTBR.

I_W_DATA-ZZICNUM_21 = L_ICNUM_21.

I_W_DATA-ZZICNUM_20 = L_ICNUM_20.

I_W_DATA-ZZICNUM_24 = L_ICNUM_24.

I_W_DATA-ZZICNUM_18 = L_ICNUM_18.

I_W_DATA-ZZESINO = L_ESINO.

I_W_DATA-ZZEEPF1 = L_EEBAS.

I_W_DATA-ZZISSPL = L_ISSPL.

  • code added by pandu

I_W_DATA-ZZGBDAT = L_GBDAT.

I_W_DATA-ZZFAVOR = L_FAVOR.

I_W_DATA-ZZNAME2 = L_NAME2.

I_W_DATA-ZZORT01 = L_ORT01.

I_W_DATA-ZZSTRAS = L_STRAS.

I_W_DATA-ZZPSTLZ = L_PSTLZ.

I_W_DATA-ZZUSRID = L_USRID.

I_W_DATA-ZZXMETXT = L_XMETXT.

I_W_DATA-ZZBANKL = L_BANKL.

I_W_DATA-ZZBANKN = L_BANKN.

I_W_DATA-ZZCNAME = L_CNAME.

I_W_DATA-ZZLIFNR = L_LIFNR.

I_W_DATA-ZZINCDAT = L_INCDAT.

  • i_w_data-zzctc_amt = l_ctc_amt.

I_W_DATA-ZZRDAT = L_RDATE.

I_W_DATA-ZZLWDAY = L_LWDAY.

I_W_DATA-ZZRDAT = L_REDAT.

I_W_DATA-ZZSCHKZ = L_SCHKZ.

I_W_DATA-ZZPRDAT = L_PRDAT.

I_W_DATA-ZZABRDT = L_ABRDT.

I_W_DATA-ZZABRSP = L_ABRSP.

I_W_DATA-ZZBANKA = L_BANKA.

I_W_DATA-ZZSMDAT = L_SMDAT.

*endchange

*Getting The details for EVP

CLEAR L_PERNR.

CLEAR L_NAME.

CLEAR L_ICNUM_TIER2.

CLEAR L_ICNUM_RM.

CLEAR L_RMBEGDA.

CLEAR L_RMENDDA.

CLEAR L_ESINO.

CLEAR L_EEBAS.

CLEAR L_ICNUM_21.

CLEAR L_ICNUM_20.

CLEAR L_ICNUM_24.

CLEAR L_ICNUM_18.

CLEAR L_MSTBR.

CLEAR L_ISSPL.

CLEAR L_PLANS.

*code added by pandu

CLEAR L_GBDAT.

CLEAR L_FAVOR.

CLEAR L_NAME2.

CLEAR L_ORT01.

CLEAR L_STRAS.

CLEAR L_PSTLZ.

CLEAR L_USRID.

CLEAR L_XMETXT.

CLEAR L_BANKN.

CLEAR L_BANKL.

CLEAR L_CNAME.

CLEAR L_LIFNR.

CLEAR L_RDATE.

CLEAR L_LWDAY.

CLEAR L_REDAT.

CLEAR L_PRDAT.

CLEAR L_SCHKZ.

CLEAR L_ABRDT.

CLEAR L_ABRSP.

CLEAR L_BANKA.

CLEAR L_SMDAT.

*endcode

SELECT SINGLE SOBID INTO L_PLANS2 FROM HRP1001

WHERE OTYPE EQ 'S'

AND OBJID EQ L_PLANS

AND RSIGN EQ 'A'

AND RELAT EQ '002'

AND ENDDA GE SY-DATUM

AND SCLAS EQ 'S'.

  • AND sobid EQ l_plans.

SELECT SINGLE PERNR INTO L_PERNR1 FROM PA0001

WHERE ENDDA GE SY-DATUM

AND PLANS EQ L_PLANS2.

SELECT SINGLE ENAME INTO L_NAME1 FROM PA0001

WHERE ENDDA GE SY-DATUM

AND PLANS EQ L_PLANS2.

I_W_DATA-ZZSRRMEVP = L_PERNR1.

I_W_DATA-ZZSRRMEVPNAME = L_NAME1.

***********************

*Monthly salary of Rm from PA0008

NUM = '01'.

IF I_W_DATA-ENDDA EQ '99991231'.

CLEAR V_AMOUNT.

SELECT SINGLE * INTO L_PA0008

FROM PA0008

WHERE PERNR EQ I_W_DATA-PERNR

AND ENDDA EQ '99991231'.

DO 30 TIMES.

IF STRLEN( NUM ) = 1.

CONCATENATE '0' NUM INTO NUM.

ENDIF.

CONCATENATE 'L_PA0008-BET' NUM INTO FLDNAM.

ASSIGN (FLDNAM) TO <FS>.

V_AMOUNT = V_AMOUNT + <FS>.

CLEAR <FS>.

NUM = NUM + 1.

ENDDO.

I_W_DATA-ZZMONSAL = V_AMOUNT.

ENDIF.

*************************************************

  • Getting salary monthly information

CLEAR : L_AMOUNT, L_AMOUNT_OFF,L_NAME1.

*CALL FUNCTION 'ZIBHR_EMP_INCENTIVE'

  • EXPORTING

  • S_PERNR = i_w_data-pernr

  • IMPORTING

  • INCENTIVE = l_amount

  • INCENTIVE_OFF = l_amount_off

  • .

*

  • i_w_data-zzmonsal_off = l_amount_off.

MODIFY I_T_DATA FROM I_W_DATA.

CLEAR L_ICNUM.

CLEAR L_PERNR.

ENDLOOP.

  • COst Center Enhancement for data source

  • Getting the employee count for a COST CENTER

WHEN '0COSTCENTER_ATTR'.

CLEAR : VL_COUNT.

LOOP AT I_T_DATA INTO I_W_DATA_KOSTL.

SELECT PERNR INTO TABLE IT_PERNR FROM PA0185

WHERE ICNUM EQ I_W_DATA_KOSTL-KOSTL

AND SUBTY = '05'

AND ENDDA EQ '99991231'.

DESCRIBE TABLE IT_PERNR LINES VL_COUNT.

MOVE : VL_COUNT TO I_W_DATA_KOSTL-ZZHEADCOUNT.

MODIFY I_T_DATA FROM I_W_DATA_KOSTL.

REFRESH : IT_PERNR.

ENDLOOP.

Former Member
Former Member replied

I find it quite useless to post many coding lines, you must test the program and

post coding which is responsible for long runtimes.

I have provided two blogs on the mayor traces:

SQL trace:

The SQL Trace (ST05) – Quick and Easy

SE30

The ABAP Runtime Trace (SE30) - Quick and Easy

And a blog on internal tables processing

Tell us total runtime and top three in hitlist.

Siegfried

0 View this answer in context
Not what you were looking for? View more on this topic or Ask a question