Performance suggestion

[Former Member]

Hi experts!!

Any performance Suggestions for following codes:

form display_data .

DATA: i_npdreq TYPE TABLE OF znpdreq,

w_npdreq TYPE znpdreq.

data: i_link type table of crmd_link,

w_link type crmd_link.

data: i_partner type table of CRMD_PARTNER,

w_partner type CRMD_PARTNER.

data: i_but000 type table of but000,

w_but000 type but000.

data: i_orderadm_i type table of crmd_orderadm_i,

w_orderadm_i type crmd_orderadm_i.

select * into table i_npdreq

from znpdreq

where object_id = w_orderadm_h-object_id

and process_type = 'ZNPD'.

read table i_npdreq into w_npdreq

with key  process_type = 'ZNPD'.

i_final-guid        = w_orderadm_h-guid.

i_final-object_type = w_orderadm_h-object_type.

i_final-ZPRODNO     = w_npdreq-ZPRODNO.

i_final-ZPRODMS     = w_npdreq-ZPRODMS.

i_final-ZREQNO      = w_npdreq-zreqno.

i_final-ZPRODNA     = w_npdreq-ZPRODNA.

i_final-description = w_orderadm_h-description.

append i_final.

select * into table i_orderadm_i from crmd_orderadm_i

where header = w_orderadm_h-guid.

read table i_orderadm_i into  w_orderadm_i

with key header  = w_orderadm_h-guid.

loop at i_final where guid = w_orderadm_i-header.

i_final-ORDERED_PROD   = w_orderadm_i-ORDERED_PROD.

i_final-DESCRIPTION_UC = w_orderadm_i-description_uc.

modify i_final.

endloop." i_final _orderadm_i

select * into table i_link from crmd_link

where GUID_HI  = w_orderadm_h-guid.

loop at i_link into w_link.

loop at i_final where guid = w_link-guid_hi.

i_final-guid_hi = w_link-guid_hi.

i_final-guid_set = w_link-guid_set.

modify i_final.

endloop. "i_final for crmd_link

select * into table i_partner from crmd_partner

where guid  = w_link-GUID_SET

and partner_fct = '00000021'.

read table i_partner into  w_partner

with key partner_fct = '00000021'.

loop at i_final where guid_Set = w_partner-GUID.

*     *i_final-partner = w_partner-partner.

i_final-PARTNER_GUID = w_partner-PARTNER_GUID.

i_final-partner_no = w_partner-partner_no.

modify i_final.

endloop. "i_final for partner.

loop at i_partner into w_partner.

select * into table i_but000 from BUT000

where PARTNER_GUID = w_partner-PARTNER_NO.

read table i_but000 into  w_but000

with key PARTNER_GUID = w_partner-PARTNER_NO.

loop at i_final where partner_no = w_but000-partner_guid.

i_final-partner =  w_but000-partner.

i_final-mc_name1 = w_but000-mc_name1.

i_final-MC_NAME2 = w_but000-MC_NAME2.

modify i_final.

endloop.

endloop. "i_partner

endloop. "i_link

break-point."testing

break-point.

endform.                    " display_data

[Former Member]

Anee,

You have to remove all select statements form loops.

Use FOR ALL ENTRIES.

[Former Member]

Yeah - format it so that we can read it more easily.

Have you used any tools to identify if and where there are problems?

Rob

[Former Member]

See Dear First Format The Display properly .So After The Birds' Eye View we can suggest that never fetch all the fields from a table unless its not required in the code .It degrade the code performance so badly that u can only realize after running the se30.Anyway next better to use the key index of all the tables u r using in code.Or create another secondary index which is not a good habit.So better try for the first one.Never use select statement inside a loop.Alternatively u can use

'For all entries' Syntax Checking the emptiness for the parent table .

Use sort before reading a table.Next use binary search . Anyway u better to display it in proper format .Then we can discuss it more.

Expecting rewards If its usefull!!!!!!!!!

[Former Member]

Anee,

You have to remove all select statements form loops.

Use FOR ALL ENTRIES.

[Former Member]

<FUNCTION-POOL ZBAPI_OPPORTUNITY_FOR_SEARCH_HELP. "MESSAGE-ID ..

types: begin of order_h,

GUID type crmd_orderadm_h-guid,

OBJECT_ID type crmd_orderadm_h-object_id,

PROCESS_TYPE type crmd_orderadm_h-process_type,

DESCRIPTION type crmd_orderadm_h-description,

object_type type crmd_orderadm_h-object_type,

end of order_h.

• * parent header internal table with workarea

data: i_orderadm_h type HASHED TABLE OF order_h

with UNIQUE KEY OBJECT_ID PROCESS_TYPE,

• object_id

w_orderadm_h type order_h.

types: begin of order_i,

GUID type crmd_orderadm_i-guid,

header type crmd_orderadm_i-header,

PRODUCT TYPE crmd_orderadm_i-PRODUCT,

ORDERED_PROD type crmd_orderadm_i-ordered_prod,

description_uc type crmd_orderadm_i-description_uc,

end of order_i.

data: i_orderadm_i type HASHED TABLE OF order_i

with UNIQUE KEY guid header,

• object_id

w_orderadm_i type order_i.

• * child header internal table with workarea

DATA: i2_orderadm_h TYPE HASHED TABLE OF order_h

with UNIQUE key guid OBJECT_ID,

w2_orderadm_h TYPE order_h.

*For Status Text

types: begin of t_text,

STSMA type TJ30T-STSMA,

ESTAT type tj30t-ESTAT,

SPRAS type tj30t-spras,

TXT30 type tj30t-TXT30,

end of t_text.

data: i_TJ30T type HASHED TABLE OF t_Text

with UNIQUE KEY stsma estat,

w_tj30t type t_text.

*

• parent internal table for function module1 (crm_order_read)

DATA: lt_header_guid TYPE crmt_object_guid_tab,

lv_header_guid TYPE string,

lt_subject TYPE crmt_subject_wrkt,

it_subject LIKE LINE OF lt_subject.

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

*child internal table for function module2 (crm_order_read)

DATA: lt_header_guid2 TYPE crmt_object_guid_tab,

lv_header_guid2 TYPE crmt_object_guid_tab,

iv_header_guid2 TYPE string,

iv_header_guid TYPE crmt_object_guid.

*parent doc_flow internal table and work area

DATA: im_doc_flow TYPE crmt_doc_flow_wrkt,

w_doc_flow TYPE crmt_doc_flow_wrk.

*parent status internal table and work area

DATA: im_status TYPE crmt_status_wrkt,

w_status TYPE crmt_status_wrk.

*child doc_flow internal table and work area

DATA: im2_doc_flow TYPE crmt_doc_flow_wrkt,

w2_doc_flow TYPE crmt_doc_flow_wrk.

*child status internal table and work area

DATA: im2_status TYPE crmt_status_wrkt,

w2_status TYPE crmt_status_wrk.

types: begin of t_npdreq,

OBJECT_ID type znpdreq-OBJECT_ID,

process_type TYPE znpdreq-PROCESS_TYPE,

ZPRODNO type znpdreq-zprodno,

ZPRODMS type znpdreq-ZPRODMS,

ZREQNO type znpdreq-zreqno,

ZPRODNA type znpdreq-zprodna,

end of t_npdreq.

data: i_npdreq type HASHED TABLE OF t_npdreq

with UNIQUE KEY ZREQNO object_id,

w_npdreq type t_npdreq.

TYPES: begin of t_link,

GUID_HI type crmd_link-guid_hi,

GUID_SET type crmd_link-guid_set,

end of t_link.

data: i_link type HASHED TABLE OF t_link

with UNIQUE KEY guid_HI guid_set,

w_link type t_link.

types: begin of t_partner,

guid type crmd_partner-guid,

PARTNER_GUID type CRMD_PARTNER-partner_guid,

PARTNER_FCT type CRMD_PARTNER-partner_fct,

PARTNER_NO type crmd_partner-partner_no,

end of t_partner.

DATA: i_partner type HASHED TABLE OF t_partner

with UNIQUE key partner_fct partner_no,

w_partner type t_partner.

types: begin of t_but000,

PARTNER type but000-partner,

MC_NAME1 type but000-mc_name1,

MC_NAME2 type but000-mc_name2,

PARTNER_GUID type but000-partner_guid,

end of t_but000.

DATA: i_but000 type HASHED TABLE OF t_but000

with UNIQUE key PARTNER partner_guid,

w_but000 type t_but000.

DATA: flag(1) TYPE c.

*internal table for search_help

DATA: BEGIN OF i_final OCCURS 0,

guid TYPE crmd_orderadm_h-guid,

guid_hi TYPE crmd_link-guid_hi,

guid_set TYPE crmd_link-guid_set,

partner_guid TYPE crmd_partner-partner_guid,

partner_no TYPE crmd_partner-partner_no,

• partner type crmd_partner-partner,

object_type TYPE crmd_orderadm_h-object_id,

zprodno TYPE znpdreq-zprodno,

zprodms TYPE znpdreq-zprodms,

zreqno TYPE znpdreq-zreqno,

zprodna TYPE znpdreq-zprodna,

partner TYPE but000-partner,

description TYPE crmd_orderadm_h-description,

mc_name1 TYPE but000-mc_name1,

mc_name2 TYPE but000-mc_name2,

ordered_prod TYPE crmd_orderadm_i-ordered_prod,

description_uc TYPE crmd_orderadm_i-description_uc,

END OF i_final.

select GUID

OBJECT_ID

PROCESS_TYPE

DESCRIPTION

object_type

into table i_orderadm_h

from crmd_orderadm_h

WHERE process_type EQ 'ZNPD'.

• where object_id = '2001200081'.

*COLLECTING PARENT DATA

LOOP AT i_orderadm_h INTO w_orderadm_h where PROCESS_TYPE = 'ZNPD'.

lv_header_guid = w_orderadm_h-guid.

• '47B1161F696F009EE10080005A000954'.

iv_header_guid = lv_header_guid.

INSERT iv_header_guid INTO TABLE lt_header_guid.

CALL FUNCTION 'CRM_ORDER_READ'

EXPORTING

it_header_guid = lt_header_guid

IMPORTING

et_status = im_status

et_doc_flow = im_doc_flow.

READ TABLE im_status INTO w_status

WITH KEY status = 'ZGGLOPPT'.

LOOP AT im_doc_flow INTO w_doc_flow.

*Retriving child DATA

select GUID

OBJECT_ID

PROCESS_TYPE

DESCRIPTION

object_type

into table i2_orderadm_h

from crmd_orderadm_h

where guid = w_doc_flow-objkey_b.

LOOP AT i2_orderadm_h INTO w2_orderadm_h WHERE process_type = 'ZPCO'.

INSERT w2_orderadm_h-guid INTO TABLE lv_header_guid2.

• BREAK-POINT.

LOOP AT im_doc_flow INTO w_doc_flow.

CALL FUNCTION 'CRM_ORDER_READ'

EXPORTING

it_header_guid = lv_header_guid2

IMPORTING

et_status = im2_status

et_doc_flow = im2_doc_flow.

READ TABLE im2_status INTO w2_status

WITH KEY user_stat_proc = 'ZGGLCRM'.

• txt30 = 'COMPLETED'.

PERFORM display_data.

delete ADJACENT DUPLICATES FROM i_final.

it_output[] = I_FINAL[].

ENDLOOP.

ENDLOOP. "i2_orderadm_h

endloop.

endloop.

ENDFUNCTION.

form display_data .

• DATA: i_but000 TYPE TABLE OF but000,

• w_but000 TYPE but000.

• DATA: i_orderadm_i TYPE TABLE OF crmd_orderadm_i,

• w_orderadm_i TYPE crmd_orderadm_i.

SELECT OBJECT_ID

process_type

ZPRODNO

ZPRODMS

ZREQNO

ZPRODNA INTO TABLE i_npdreq

FROM znpdreq

WHERE object_id = w_orderadm_h-object_id

AND process_type = 'ZNPD'.

READ TABLE i_npdreq INTO w_npdreq

WITH KEY process_type = 'ZNPD'.

i_final-guid = w_orderadm_h-guid.

i_final-object_type = w_orderadm_h-object_type.

i_final-zprodno = w_npdreq-zprodno.

i_final-zprodms = w_npdreq-zprodms.

i_final-zreqno = w_npdreq-zreqno.

i_final-zprodna = w_npdreq-zprodna.

i_final-description = w_orderadm_h-description.

APPEND i_final.

SELECT GUID

header

PRODUCT

ORDERED_PROD

description_uc

INTO TABLE i_orderadm_i FROM crmd_orderadm_i

WHERE header = w_orderadm_h-guid.

READ TABLE i_orderadm_i INTO w_orderadm_i

WITH KEY header = w_orderadm_h-guid.

LOOP AT i_final WHERE guid = w_orderadm_i-header.

i_final-ordered_prod = w_orderadm_i-ordered_prod.

i_final-description_uc = w_orderadm_i-description_uc.

MODIFY i_final.

ENDLOOP." i_final orderadmi

SELECT guid_hi guid_Set INTO TABLE i_link FROM crmd_link

WHERE guid_hi = w_orderadm_h-guid.

LOOP AT i_link INTO w_link.

LOOP AT i_final WHERE guid = w_link-guid_hi.

i_final-guid_hi = w_link-guid_hi.

i_final-guid_set = w_link-guid_set.

MODIFY i_final.

ENDLOOP. "i_final for crmd_link

SELECT guid

PARTNER_GUID

PARTNER_FCT

PARTNER_NO

INTO TABLE i_partner FROM crmd_partner

where guid = w_link-guid_set

AND partner_fct = '00000021'.

READ TABLE i_partner INTO w_partner

WITH KEY partner_fct = '00000021'.

LOOP AT i_final WHERE guid_set = w_partner-guid.

i_final-partner_guid = w_partner-partner_guid.

i_final-partner_no = w_partner-partner_no.

MODIFY i_final.

ENDLOOP. "i_final for partner.

LOOP AT i_partner INTO w_partner.

SELECT PARTNER

MC_NAME1

MC_NAME2

PARTNER_GUID

INTO TABLE i_but000 FROM but000

WHERE partner_guid = w_partner-partner_no.

READ TABLE i_but000 INTO w_but000

WITH KEY partner_guid = w_partner-partner_no.

LOOP AT i_final WHERE partner_no = w_but000-partner_guid.

i_final-partner = w_but000-partner.

i_final-mc_name1 = w_but000-mc_name1.

i_final-mc_name2 = w_but000-mc_name2.

MODIFY i_final.

ENDLOOP.

ENDLOOP. "i_partner

ENDLOOP. "i_link

ENDFORM. " display_data >

[Former Member]

Hi Anee,

• Kindly declare the structure as per required fields for the corresponding fields. Donot reffer to the Direct database tables

as znpdreq.

form display_data .

DATA: i_npdreq TYPE TABLE OF znpdreq,

w_npdreq TYPE znpdreq.

data: i_link type table of crmd_link,

w_link type crmd_link.

data: i_partner type table of CRMD_PARTNER,

w_partner type CRMD_PARTNER.

data: i_but000 type table of but000,

w_but000 type but000.

data: i_orderadm_i type table of crmd_orderadm_i,

w_orderadm_i type crmd_orderadm_i.

*// Comments

• As per Select Command written, Kindly Specify the required fields to be fetched, since it affects the performance as per se30.

select * into table i_npdreq from znpdreq

where object_id = w_orderadm_h-object_id

and process_type = 'ZNPD'.

• If object_id = w_orderadm_h-object_id and process_type = 'ZNPD' is a unique combination then a single record will be fetched otherwise it will fetch all the records belongs to this

combination. Kindly check the table znpdreq which will be having other Primary key fields where as contaning unique values for all records.

Ans: Donot use ' process_type = 'ZNPD' ' in table statment, since all the records with ' process_type = 'ZNPD' '. Before using read statment sort the internal table with other primary key fields rather than object_id and process_type as per priority.

Then use read table i_npdreq into w_npdreq index '1'.

Otherwise go with binary search if the value known for the

sorting field specified above.

read table i_npdreq into w_npdreq

with key process_type = 'ZNPD'.

i_final-guid = w_orderadm_h-guid.

i_final-object_type = w_orderadm_h-object_type.

i_final-ZPRODNO = w_npdreq-ZPRODNO.

i_final-ZPRODMS = w_npdreq-ZPRODMS.

i_final-ZREQNO = w_npdreq-zreqno.

i_final-ZPRODNA = w_npdreq-ZPRODNA.

i_final-description = w_orderadm_h-description.

append i_final.

• Follow the same as it is mentioned for Select Statement above.

select * into table i_orderadm_i from crmd_orderadm_i

where header = w_orderadm_h-guid.

• Avoid writting this statement if there are more than one

records with this key field.

• Follow the same as it is mentioned for internal table above.

read table i_orderadm_i into w_orderadm_i

with key header = w_orderadm_h-guid.

• Donot use Loop Statment for a single record, since i_final

contains a single record so far, even the header is not yet

cleared, you can proceed with assigning field values.

i_final-ORDERED_PROD = w_orderadm_i-ORDERED_PROD.

i_final-DESCRIPTION_UC = w_orderadm_i-description_uc.

modify i_final where guid = w_orderadm_h-guid.

loop at i_final where guid = w_orderadm_i-header.

i_final-ORDERED_PROD = w_orderadm_i-ORDERED_PROD.

i_final-DESCRIPTION_UC = w_orderadm_i-description_uc.

modify i_final.

endloop." i_final orderadmi

select * into table i_link from crmd_link

where GUID_HI = w_orderadm_h-guid.

• Kindly Avoid the nested Loop.*

• Kindly Avoid the Select Query inside loop.*

loop at i_link into w_link.

loop at i_final where guid = w_link-guid_hi.

i_final-guid_hi = w_link-guid_hi.

i_final-guid_set = w_link-guid_set.

modify i_final.

endloop. "i_final for crmd_link

select * into table i_partner from crmd_partner

where guid = w_link-GUID_SET

and partner_fct = '00000021'.

read table i_partner into w_partner

with key partner_fct = '00000021'.

loop at i_final where guid_Set = w_partner-GUID.

• *i_final-partner = w_partner-partner.

i_final-PARTNER_GUID = w_partner-PARTNER_GUID.

i_final-partner_no = w_partner-partner_no.

modify i_final.

endloop. "i_final for partner.

loop at i_partner into w_partner.

select * into table i_but000 from BUT000

where PARTNER_GUID = w_partner-PARTNER_NO.

read table i_but000 into w_but000

with key PARTNER_GUID = w_partner-PARTNER_NO.

loop at i_final where partner_no = w_but000-partner_guid.

i_final-partner = w_but000-partner.

i_final-mc_name1 = w_but000-mc_name1.

i_final-MC_NAME2 = w_but000-MC_NAME2.

modify i_final.

endloop.

endloop. "i_partner

endloop. "i_link

break-point."testing

break-point.

endform. " display_data

Kindly follow the steps mentioned above. I am sure it will get you better solution.

Venkat