Skip to Content
0
Sep 03, 2007 at 09:02 AM

Performance Tuning in case of Database Access

73 Views

Hi,

I am using following code...database access is huge for this code...pls help me out to make database access minimum. I am using 3 internal tables.

select partner1 partner2 into (mtab-busi_part, mtab-BUT051_PART)

from but051.

*

Select name_first name_last PARTNER_GUID into (mtab-bp_first, mtab-bp_last, MTAB-R_PARTNER_GUID)

From but000 where partner = mtab-busi_part.

*MTAB-OBJECT_ID = ITAB-OBJECT_ID.

append mtab.

endselect.

ENDSELECT.

*ENDLOOP.

loop at mtab.

CONCATENATE mtab-bp_FIRST mtab-bp_LAST INTO mTAB-bp_full

separated BY SPACE.

modify mtab.

endloop.

loop at mtab.

if mtab-bp_full = ' '.

select name_org1 into (mtab-bp_full)

from but000 where partner = mtab-busi_part.

  • append mtab.

endselect.

endif.

modify mtab.

clear mtab.

endloop.

SELECT OBJECT_ID GUID INTO (NTAB-object_id, Ntab-guid)

FROM CRMD_ORDERADM_H

  • for all entries in itab

where process_type = '1001' and object_id in o_id.

select single date_1 date_2 from crmv_item_index into (ntab-date_1, ntab-date_2 )

where object_id = ntab-object_id.

  • endselect.

Select partner_no partner_fct into (Ntab-partner_guid, Ntab-partner_fct)

from bbp_pdview_bup where guid_hi = Ntab-guid .

*and partner_fct <> '00000015'

Select partner name_org1 into (Ntab-partner_no2, Ntab-others)

from but000 where partner_guid = Ntab-partner_guid.

if sy-subrc = 0.

SELECT SINGLE DESCRIPTION FROM CDBC_PARTNER_FT INTO NTAB-DESC

WHERE PARTNER_FCT = NTAB-PARTNER_FCT AND SPRAS = 'EN'.

endif.

SELECT PAFKT ABTNR PAAUTH

FROM BUT051 INTO corresponding fields of nTAB

WHERE PARTNER2 = ntab-partner_no2 .

if sy-subrc = 0.

SELECT single BEZ30 FROM TB913

INTO CORRESPONDING FIELDS OF nTAB

WHERE PAFKT = nTAB-PAFKT AND SPRAS = 'E'.

endif.

if sy-subrc = 0.

SELECT single BEZ20 FROM TB915

INTO CORRESPONDING FIELDS OF nTAB

WHERE PAAUTH = nTAB-PAAUTH AND SPRAS = 'E'.

endif.

*endselect.

if sy-subrc = 0.

SELECT single BEZ20 FROM TB911

INTO (nTAB-BEZ2)

WHERE ABTNR = nTAB-ABTNR AND SPRAS = 'E'.

endif.

endselect.

APPEND NTAB.

*clear ntab.

*ENDSELECT.

ENDSELECT.

*clear ntab.

ENDSELECT.

ENDSELECT.

loop at ntab.

if ntab-others = ' '.

select name_first name_last into (ntab-first_name1, ntab-last_name1)

from but000 where partner = ntab-partner_no2.

endselect.

CONCATENATE ntab-FIRST_NAME1 ntab-LAST_NAME1 INTO nTAB-others

separated BY SPACE.

endif.

modify ntab.

clear ntab.

endloop.

SORT NTAB BY GUID.

SELECT OBJECT_ID GUID INTO (KTAB-object_id, Ktab-guid)

FROM CRMD_ORDERADM_H

  • for all entries in itab

where process_type = '1001' and object_id in o_id.

Select partner_no into (Ktab-partner_no1)

From crmd_order_index where header = Ktab-guid and pft_8 = 'X' and object_type = 'BUS2000126'.

*endselect.

Select name_first name_last into (Ktab-first_name, Ktab-last_name)

From but000 where partner = Ktab-partner_no1.

*endselect.

APPEND KTAB.

ENDSELECT.

ENDSELECT.

ENDSELECT.

loop at Ktab.

CONCATENATE Ktab-FIRST_NAME Ktab-LAST_NAME INTO KTAB-RESP_EMPLOYEE

separated BY SPACE.

MODIFY KTAB.

clear Ktab.

endloop.

loop at Ktab.

if Ktab-RESP_EMPLOYEE = ' '.

select name_ORG1 into (Ktab-RESP_EMPLOYEE)

from but000 where partner = Ktab-partner_no1.

endselect.

endif.

modify Ktab.

clear Ktab.

endloop.

SELECT OBJECT_ID GUID INTO (itab-object_id, itab-guid)

FROM CRMD_ORDERADM_H

where process_type = '1001' and object_id in o_id.

append itab.

endselect.

LOOP AT iTAB.

LOOP AT NTAB .

IF NTAB-object_id = iTAB-object_id .

itab-date_1 = ntab-date_1.

ITAB-DESC = NTAB-DESC.

itab-partner_no2 = NTab-partner_no2.

itab-partner_fct = ntab-partner_fct.

itab-bez30 = ntab-bez30.

itab-bez20 = ntab-bez20.

itab-bez2 = ntab-bez2.

itab-others = ntab-others.

INSERT lines of nTAB INTO ITAB.

modify itab.

CLEAR ITAB.

  • delete itab where object_id = ' ' and partner_no2 = ' '.

ENDIF.

endloop.

endloop.

sort itab by OBJECT_ID descending PARTNER_NO2 .

delete adjacent duplicates from itab comparing partner_no2 object_id.

*

sort itab by OBJECT_ID descending PARTNER_NO2 .

*****

loop at iTab where partner_fct = '00000015'.

LOOP AT mTAB WHERE BUT051_PART = iTAB-partner_no2 .

itab-busi_part = mtab-busi_part.

itab-bp_full = mtab-bp_full.

ITAB-R_PARTNER_GUID = MTAB-R_PARTNER_GUID.

  • INSERT LINES OF mTAB INTO iTAB.

modify itab transporting busi_part bp_full r_partner_guid.

endloop.

endloop.

sort itab by busi_part descending partner_no2.

delete itab where object_id = ' '.

loop at ITab.

LOOP AT KTAB.

IF KTAB-GUID = ITAB-GUID.

move Ktab-partner_no1 to itab-partner_no1.

  • move Ktab-R_partner_GUID to itab-R_partner_GUID.

move Ktab-RESP_EMPLOYEE to itab-RESP_EMPLOYEE.

modify itab.

ENDIF.

endloop.

endloop.