topic Re: Performance tuning in Application Development and Automation Discussions

Performance tuning

Former Member — Wed, 30 Aug 2006 12:03:41 GMT

Hi.

I have the following select statement which gives me time out error.

Table TB_MARC is having upto 10000 materials.

Can anyone suggest how can i tune this code

IF NOT TB_MARC[] IS INITIAL.

SELECT B_{MATNR A}VBELN B_{POSNR B}WERKS C_{BMENG C}EDATU

INTO CORRESPONDING FIELDS OF TABLE TB_VBEP

INTO TABLE TB_VBEP

FROM VBAK AS A INNER JOIN VBAP AS B

ON A_{VBELN = B}VBELN

INNER JOIN VBEP AS C

ON A_{VBELN = C}VBELN AND B_{POSNR = C}POSNR

FOR ALL ENTRIES IN TB_MARC

WHERE A~VKORG = '7500' AND

B~MATNR = TB_MARC-MATNR AND

B~WERKS = '7500' AND

B~ABGRU = ' ' AND "CONSIDERS ONLY NOT REJECTED SALES ORDER

C~EDATU >= L_PDAT " 21 Past days from current date

AND C~EDATU LE L_EDAT. " End date after 1 yr from current month

ENDIF.

Re: Performance tuning

Former Member — Wed, 30 Aug 2006 12:09:41 GMT

Try not to do 2 joins in one select statment .

FIrs do join for vbak and vbap. then do rest of code to read data from vbep.

Re: Performance tuning

Former Member — Wed, 30 Aug 2006 12:10:22 GMT

Hi osk,

Since you your base table TB_MARC is having upto 10000 records the program will select data in loop that may times from the databse.

Try to remove the FOR ALL ENTRIES options. Instead data using array fetch and then try filtering the data in the program.

Cheers

Re: Performance tuning

eddy_declercq — Wed, 30 Aug 2006 12:13:29 GMT

Hi,

You can check stuff with transaction ST03 (= Workload Analysis) or ST05 (= Performance Analysis SQL Trace) to discover the bottleneck(s).

Eddy

PS.

Put yourself on the SDN world map (http://sdn.idizaai.be/sdn_world/sdn_world.html) and earn 25 points.

Spread the wor(l)d!

Re: Performance tuning

Former Member — Wed, 30 Aug 2006 12:15:15 GMT

it is not recommended to use joins while retriving data from more than 2 tables better to use for all entries and make it loop for further modifying internal tableentries

Re: Performance tuning

Former Member — Wed, 30 Aug 2006 12:15:28 GMT

good

add this line that you have commented and try again,

INTO CORRESPONDING FIELDS OF TABLE TB_VBEP

bcz it is taking maximum time to get the data from the database table.

thanks

mrutyun

Re: Performance tuning

Former Member — Wed, 30 Aug 2006 12:18:20 GMT

I would suggest you to do is

LOOP at TB_MARC.

at new matnr.

select query.

endat.

endloop.

Regards

Anurag

Re: Performance tuning

Former Member — Wed, 30 Aug 2006 12:22:18 GMT

Hi ,

1) Remove corresponding from select satement

2) Remove * from select

3) Select field in sequence as defined in database

4) Avoid unnecessary selects

i.e check for internal table not initial

5) Use all entries and sort table by key fields

6) Remove selects ferom loop and use binary search

7) Try to use secondary index when you don't have

full key.

😎 Modify internal table use transporting option

9) Avoid nested loop . Use read table and loop at itab

from sy-tabix statement.

10) free intrenal table memory wnen table is not

required for further processing.

11)

Follow below logic.

if not it_plant[] is initial.

it_plant1[] = it_plant[].

sort it_plant1 by werks.

delete adjacent duplicates from it_plant1 comparing werks

SELECT AUFNR KTEXT USER4 OBJNR INTO CORRESPONDING FIELDS OF TABLE I_AUFKTAB

FROM AUFK

FOR ALL ENTRIES IN it_plant1

WHERE AUFNR IN S_AUFNR AND

KTEXT IN S_KTEXT AND

WERKS IN S_WERKS AND

AUART IN S_AUART AND

USER4 IN S_USER4 AND

werks eq it_plant1-werks.

free it_plant1.

Endif.

Regards

amole

Re: Performance tuning

Former Member — Wed, 30 Aug 2006 12:31:34 GMT

Hello osk,

try creating a view for the tables and access. Internally database optimises the access to the tables.

Re: Performance tuning

Former Member — Wed, 30 Aug 2006 13:51:22 GMT

Your problem is due to the fact that you are not using any key fields in your WHERE clause. I think all you can do is run it in the background.

Rob

Re: Performance tuning

Former Member — Wed, 30 Aug 2006 14:17:47 GMT

Did you try creating the index in the order of the where condition, this however has a disadvantage any insertion/updation will take longer if there are many indexes defined. SAP suggest less than 10 indexes for the table.

hith

Sunil Achyut