Performance Issues

[Former Member]

Hi all,

I am posting my sample code for subroutine. Can anyone please suggest me some means to improve the performance of this code. This program takes almost 17 hours in our DR and PR. So using se30 wont help me to analyze it immediately . Also I know I need to eliminate the nested selects and select * statement. Can anyone suggest me any other tips to improve the performance of this program. Will secondary indexes help me in this regard ?

Code sample :

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

FORM get_data_no_sobsk.

• Extract the material by plant, then corresponding details-----

SELECT *

FROM marc

WHERE werks IN s_werks.

• and sobsk ne space.

• check marc-sobsk ca 'ABCDEFGHIJKLMNOPQRSTUVWXYZ'.

• Material Descriptions

CLEAR makt.

SELECT *

FROM makt

WHERE matnr = marc-matnr

AND spras IN s_spras.

• General Material Data

CLEAR mara.

SELECT *

FROM mara

WHERE matnr = marc-matnr

AND mtart IN s_mtart.

• Plant description for destination company

CLEAR t001w-name1.

SELECT name1

UP TO 1 ROWS

INTO t_material-dstcomp

FROM t001w

WHERE werks = marc-werks.

ENDSELECT.

• Material Valuation

CLEAR mbew.

SELECT *

UP TO 1 ROWS

FROM mbew

WHERE matnr = marc-matnr

AND bwkey = marc-werks.

ENDSELECT.

• Material Valuation

CLEAR t460t-ltext.

SELECT SINGLE ltext

INTO t_material-ltext

FROM t460t

WHERE werks = marc-werks

AND sobsl = marc-sobsk

AND spras IN s_spras.

• format internal table with fields ready for unix

MOVE-CORRESPONDING mara TO t_material.

MOVE-CORRESPONDING marc TO t_material.

MOVE-CORRESPONDING mbew TO t_material.

MOVE-CORRESPONDING makt TO t_material.

t_material-cr = w_eor.

• insert cariage return and tabs

t_material-cr = w_eor.

t_material-tab1 = w_tab.

t_material-tab2 = w_tab.

t_material-tab3 = w_tab.

t_material-tab4 = w_tab.

t_material-tab5 = w_tab.

t_material-tab6 = w_tab.

t_material-tab7 = w_tab.

t_material-tab8 = w_tab.

t_material-tab9 = w_tab.

t_material-tab10 = w_tab.

t_material-tab11 = w_tab.

t_material-tab12 = w_tab.

t_material-tab13 = w_tab.

t_material-tab14 = w_tab.

t_material-tab15 = w_tab.

t_material-tab16 = w_tab.

t_material-tab17 = w_tab.

t_material-tab18 = w_tab.

t_material-tab19 = w_tab.

t_material-tab20 = w_tab. " PRS #1386042

t_material-tab21 = w_tab. "TAG 5013

WRITE mbew-bwprh TO t_material-bwprh NO-GROUPING.

• WRITE mbew-stprs TO t_material-stprs NO-GROUPING.

• Create the internal table with extracted data

APPEND t_material.

ENDSELECT.

ENDSELECT.

ENDSELECT.

ENDFORM. " get_Data_no_sobsk

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

<b>Note : I am not the edeveloper of this code.</b>

Regards,

Ben.

[Former Member]

Hi Daniel,

Use <b>SELECT <feild1> <feild2> .....<feildn> from <dtab></b> instead of <b>SELECT * from <dtab></b>.

Thank you,

Ramu N.

[Former Member]

Hai Daniel

use for all Entries for improving Performence and also Not use Select--Endselect instead. Better to use select .... from Tablename into table Internal table

I will send a sample report

Check it

REPORT zs_stockreport3

NO STANDARD PAGE HEADING

LINE-SIZE 255

MESSAGE-ID zz1.

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

• Table Declaration *

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

TABLES: mara,

marc,

mard.

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

• Types Declaration *

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

TYPES: BEGIN OF typ_mara,

matnr TYPE mara-matnr, "Material Number"

mbrsh TYPE mara-mbrsh, "Industrial Sector"

mtart TYPE mara-mtart, "Material Type"

meins TYPE mara-meins, "Base Unit of Measure"

END OF typ_mara.

TYPES: BEGIN OF typ_makt,

matnr TYPE makt-matnr, "Material Number"

maktx TYPE makt-maktx, "Material Description"

END OF typ_makt.

TYPES: BEGIN OF typ_marc,

matnr TYPE marc-matnr, "Material Number"

werks TYPE marc-werks, "Plant Number"

END OF typ_marc.

TYPES: BEGIN OF typ_mard,

matnr TYPE marc-matnr, "Material Number"

werks TYPE marc-werks, "Plant Number"

lgort TYPE mard-lgort, "Storage Location"

END OF typ_mard.

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

• Intrnal tables Declaration *

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

DATA: it_mara TYPE STANDARD TABLE OF typ_mara WITH HEADER LINE.

DATA: it_makt TYPE STANDARD TABLE OF typ_makt WITH HEADER LINE.

DATA: it_marc TYPE STANDARD TABLE OF typ_marc WITH HEADER LINE.

DATA: it_mard TYPE STANDARD TABLE OF typ_mard WITH HEADER LINE.

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

• Variable Declaration *

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

DATA: v_count TYPE i.

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

• Selection Screen *

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

SELECTION-SCREEN : BEGIN OF BLOCK b1 WITH FRAME TITLE text-001.

SELECT-OPTIONS : s_matnr FOR mara-matnr.

SELECTION-SCREEN : SKIP.

PARAMETERS : p_mtart LIKE mara-mtart.

SELECTION-SCREEN : END OF BLOCK b1.

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

• Start Events *

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

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

• Initialization *

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

INITIALIZATION.

***

PERFORM initial_input.

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

• At Selection-screen *

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

AT SELECTION-SCREEN.

***

PERFORM validte_inputdata.

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

• Start of Selection *

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

START-OF-SELECTION.

***

SELECT matnr

mbrsh

mtart

meins

INTO TABLE it_mara

FROM mara

WHERE matnr IN s_matnr and

mtart = p_mtart.

if sy-subrc = 0.

sort it_mara by matnr.

else.

MESSAGE e001 WITH 'No data Found' ' For the Given'

'Selection Criteria'(400).

endif.

IF NOT it_mara[] IS INITIAL.

SELECT matnr

maktx

INTO TABLE it_makt FROM makt

FOR ALL ENTRIES IN it_mara

WHERE matnr = it_mara-matnr

AND spras = sy-langu.

if sy-subrc = 0.

sort it_makt by matnr.

endif.

ENDIF.

IF NOT it_mara[] IS INITIAL.

select matnr

werks

from marc

into table it_marc

for all entries in it_mara

where matnr = it_mara-matnr.

if sy-subrc = 0.

sort it_marc by matnr werks.

endif.

endif.

IF NOT it_marc[] IS INITIAL.

select matnr

werks

lgort

from mard

into table it_mard

for all entries in it_marc

where matnr = it_marc-matnr and

werks = it_marc-werks.

if sy-subrc = 0.

sort it_mard by matnr werks lgort.

endif.

endif.

DATA: a TYPE i.

loop at it_mara.

a = sy-tabix MOD 2.

IF a = 1.

FORMAT COLOR 5.

ELSE.

FORMAT COLOR OFF.

ENDIF.

read table it_marc with key matnr = it_mara-matnr

binary search.

if sy-subrc = 0.

read table it_mard with key matnr = it_marc-matnr

werks = it_marc-werks

binary search.

if sy-subrc = 0.

read table it_makt with key matnr = it_mara-matnr

binary search.

if sy-subrc = 0.

WRITE:/ sy-vline.

WRITE: 2 it_mara-matnr,

18 sy-vline, it_mara-mbrsh,

38 sy-vline, it_mara-mtart,

54 sy-vline, it_mara-meins,

78 sy-vline, it_makt-maktx,

120 sy-vline, it_marc-werks,

135 sy-vline, it_mard-lgort, 155 sy-vline.

clear : it_mara.

endif.

clear : it_makt.

endif.

endif.

endloop.

WRITE: /(155) sy-uline.

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

• Top of Page *

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

TOP-OF-PAGE.

***

FORMAT COLOR 1.

WRITE: /(155) sy-uline.

WRITE:/ sy-vline.

WRITE: 2 'Material Number',

18 sy-vline, 'Industrial Sector',

38 sy-vline, 'Material Type',

54 sy-vline, 'Base Unit of Measure',

78 sy-vline, 'Material Description',

120 sy-vline, 'Plant',

135 sy-vline, 'Storage Location',155 sy-vline.

WRITE: /(155) sy-uline.

FORMAT RESET.

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

• End of Page *

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

&----

---

*& Form Initial_Input

&----

---

• Initailization of Select Option

----

---

FORM initial_input .

CLEAR s_matnr.

REFRESH s_matnr.

s_matnr-low = '100-100'.

s_matnr-high = '111-111'.

s_matnr-sign = 'I'.

s_matnr-option = 'BT'.

APPEND s_matnr.

p_mtart = 'ROH'.

ENDFORM. " Initial_Input

&----

---

*& Form Validte_Inputdta

&----

---

• Validation of Select Option

----

---

FORM validte_inputdata .

SELECT SINGLE * FROM mara

WHERE matnr IN s_matnr.

IF sy-subrc <> 0.

MESSAGE e001 WITH 'Material'(002) 'Type'(003) 'Does Not Exit'(400).

ENDIF.

ENDFORM. " Validte_Inputdta

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

• End of Selection *

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

Thanks & regards

Sreenivasulu P

[Former Member]

*First n foremost, try to avoid using select * . give the names of the fields instead in the select statement.

*Instead of select/endselect, use select into table..

Regards..

[Former Member]

Hi Friends,

<b>Also I know I need to eliminate the nested selects and select * statement. Can anyone suggest me any other tips to improve the performance of this program. Will secondary indexes help me in this regard ?

</b>

Make a note of this in my query.

Regards,

Ben.

[Former Member]

Hi Daniel,

To answer your query. I would say that you would not need a secondary index for the tables on which you have the select statements.

Fetching data from MARA, MARC, MAKT and MBEW should not be a problem. We have data extract programs which picks millions of records in short time and i believe that this secondary index is really not required.

As i have mentioned earlier.

1). After ur select in MARC try to move unique material numbers to a seprate internal table.

For example if your material is maintained in 10 plants then the subsequent select statements will fire 10 times to get the same information. So move the material numbers to another internal table, delete the duplicates and then use either joins or subqueries.

2). Goto St05 and there is a button EXPLAIN SQL query. Enter your query and see the path it takes to fetch the data and also the cost of your select on the database. This will let you know where is the problem. Remember this tool requires some expertise to understand.

Hope this helps

Cheers

VJ

If you wish to know how to use subquery let me know.

[Former Member]

Hi Vijayendra Rao,

Would you please eloborate a little on subquery ? I went to st05 and clicked on <b>Explain one sql request</b>.IN the editor I typed select * from mara. and clicked on explain . This shows me an popup box which says <b>An error in Explain SQL. Change UPDATE/DELETE to SELECT if possible.</b>

Can you please explain me how to use his one too.

Thanks,

Ben.

Message was edited by: Daniel Ben

[Former Member]

Hi Daniel,

Here is how you write a subquery.

  SELECT * FROM MSLB AS M INTO CORRESPONDING FIELDS OF TABLE I_MSLB
    WHERE WERKS IN S_WERKS
      AND LIFNR IN S_LIFNR
      AND LBLAB NE 0
      AND EXISTS ( SELECT MATNR FROM MARC WHERE LVORM = ' '
                          AND WERKS = M~WERKS
                          AND MATNR = M~MATNR )
      AND EXISTS ( SELECT LIFNR FROM LFA1 WHERE LOEVM = ' '
                        AND SPERR = ' ' AND SPERM = ' '
                        AND KTOKK IN S_KTOKK ).                    .

Cheers

VJ

[Former Member]

Hi Daniel,

Send me ur email id. I will send u a doc which would indicate how to use SDBE - Explain Sql transaction.

Cheers

VJ

[Former Member]

Hi Vijayendra Rao,

This is my mail Id : <b>daniel.ben@hotmail.com</b>.

please send the doc to this Id. I am awarding full points to you and closing the post.

Thanks,

Ben.

[former_member927251]

Hi Daniel,

About Code Inspector :

1. Goto SCI transaction and define a check variant which will examine your code for errors. In this variant you can specify the naming convention for your Local Variables, Global Variables etc. In the same variant you can define the Performance Checks like Loop within a loop, nested selects, security checks, programming conventions etc. Make the variant global so that everybody has access to it.

2. Now you are ready to use this variant. Goto SCII --> Select Single --> Select an entry from the dropdown --> give the corresponding progam name/class name --> Give a variant name under Check Variant.

3. Execute.

4. It will show all the discrepancies in your code from where you can double click and goto the respective line of code and correct the error.

Note that you can also use a STANDARD VARIANT.

Hope this helps.

Please reward points and close the thread.

Regards,

Amit Mishra

[Former Member]

Hi Daniel,

Hope you have recieved all the docs.

Happy reading.

Good luck

Cheers

VJ

[Former Member]

Hi Daniel,

Your main problem is the SELECT and ENDSELECT commands. Remove SELECT and ENDSELECT and use an array fetch which will bring data at one shot.

The other main problem is your initial select is on MARC table. This will get duplicate entries of material in the internal table for which you are further using in your select statement. This means for the same material number the Select statement will be triggered twice.

I can see a lot more options where it can definitely be improved. This is just a dead code as per me. There is a lot of scope for improvement.

Cheers

VJ

[Former Member]

HI

GOOD

I AM MENTIONING SOME OF THE POINTS HERE JUST USE THEM IN YOUR CODE.

1- DONT USE SELECT * IF YOU R ACCESSING SINGLE FIELD FORM THE DATABASE TABLE.

2-TRY TO USE CORRESPONDIG FIELDS OF STATEMENT IN YOUR SQL QUERY STATEMENT.

3-USE THE TCODE SCI(CODE INSPECTOR) TO FURTHER ANALYSE YOUR CODE.

THANKS

MRUTYUN

[Former Member]

Hi Mr.Tripathy,

Can you please explain me in detail how to use code inspector to analyse the code.

Regards,

Ben.

Message was edited by: Daniel Ben

[former_member188685]

I will suggest you to use

select into table instead of select endselect.

and also use for all entries if required.

select endselect causing the performance.

Regards

vijay

[Former Member]

Ben,

1.Remove all nested Select statments.

2. Do not use " Select * "...alway specify field names eg. "Select MATNR ....."

3. Rather than selecting data separately from MARA, MARC & MAKT use joins which will be much faster.

4. Try to use key fields in "Where clause"

5. Select all data from MBEW into internal table for all entries in first internal table ( combination of MARA, MAKT, MARC) and then process data from internal table.

Cheers,

Nilesh

Message was edited by: Nilesh Kshirsagar

Message was edited by: Nilesh Kshirsagar

[Former Member]

Hi,

• Do not use select-exit-endselect logic. Use select single or select up to 1 rows. The trace shows this is more efficient.

• Avoid using select-endselect loop with logic in the middle. This keeps unnecessary communication to DB2 open. Instead, select the data you need into an internal table and loop at the internal table for your looping logic.

• Order your WHERE clause exactly to the primary key or index,

• Avoid using select * and Select individual fields instead where practical.

• In order to use the addition ‘FOR ALL ENTRIES IN <TABLE>’, make sure the table is not empty or it will select all rows.

• Avoid using the addition ‘INTO CORRESPONDING FIELDS OF TABLE <TABLE>’, we have seen that it is very CPU intensive. Order the fields in the internal table and the select statement the same and use ‘INTO TABLE <TABLE>’.

• Be careful with the ‘OR’ and ‘IN” operators in the where clause of an SQL statement. Their use can exclude the use of existing secondary indexes

• Always use SQL trace (ST05) to examine the SQL statements in your code to determine whether statements are scanning tables or using the index of a table.

• Always use runtime analysis (SE30) to find out how well a program is running and where it is spending most of its time.

hope this would be usefull..

If u need any further clarifications please get back

Thanks

Jhansi