topic performance of the SQL in Application Development and Automation Discussions

performance of the SQL

Former Member — Mon, 13 Oct 2008 05:10:01 GMT

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.

Re: performance of the SQL

Former Member — Mon, 13 Oct 2008 07:02:17 GMT

Hi,

if you encounter such a long runtime it's worth to check the complete source with SE30 (runtime analyzer) to identify the long running statements / parts of your code.

If you identify SQL statements as the cause of the performance issue use ST05 to get execution plans

for the SQL in question to check if indexes are used properly (if you have frequent SELECT's insided LOOP's and NO index support the slow performance will kill you )

A quick glance on your code:

Remove the LOOP's and define the SELECT 's in a way that you don't need them

(looping in a list with many items kills you also in terms of performance).

Bye

Re: performance of the SQL

Former Member — Mon, 13 Oct 2008 07:29:00 GMT

Hi,

Thank you very much for fast reply .iam totally new to ABAP ,can you please guide me further any thing i need to do .

Regards

Re: performance of the SQL

Former Member — Mon, 13 Oct 2008 07:54:46 GMT

Hi Rama,

1st: improve the know-how for understanding whats going on in your source

2nd: know the slowest parts of your source (identify the "lowest hanging fruits" ) and start tune these

parts.

Tools:

Check this blog for using SE30 and ST05:

Unfortunatley, I'm not able to give you an advice in terms of code - up to now we are only guessing what may slow you down.

It may be good to have a senior ABAPer on your side to help you with the tools mentioned above.

Bye

Re: performance of the SQL

Former Member — Mon, 13 Oct 2008 08:54:56 GMT

HI,

thank you very much and very gretaful to you providing good blog.

Regards

Re: performance of the SQL

Former Member — Mon, 13 Oct 2008 10:37:35 GMT

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:

SE30

And a blog on internal tables processing

Tell us total runtime and top three in hitlist.

Siegfried

Re: performance of the SQL

Former Member — Tue, 14 Oct 2008 07:15:00 GMT

Hi,

i think you are not using index while read the entries from db. Try to use index always.

i suggest, there is a FM for read infoset in HR. use that FM. it will be better