topic Re: Performance issue in Application Development and Automation Discussions

Performance issue

Former Member — Thu, 29 Jan 2009 06:02:11 GMT

Hi experts,

I have the following query.

SELECT LIKP_{VBELN LIPS}POSNR LIKP_{ERDAT LIKP}INCO1 LIKP~INCO2

LIKP_{BOLNR LIKP}WADAT_IST LIKP~LIFEX

LIPS_{LFIMG LIPS}BWART LIPS_{PSTYV LIKP}VBTYP VBPA~LIFNR

INTO TABLE I_LIKP FROM LIKP

INNER JOIN LIPS

ON LIPS_{VBELN = LIKP}VBELN

INNER JOIN VBPA

ON VBPA_{VBELN = LIKP}VBELN AND

VBPA~POSNR = 0

FOR ALL ENTRIES IN I_VBFA

WHERE LIPS~VBELN = I_VBFA-VBELN

AND LIPS~POSNR = I_VBFA-POSNN AND

VBPA~PARVW = 'SP'. " SP denoted Carrier Type Partner

can anybody tell me whether this query is good performance wise? if not how can we modify this statement?

Thanks & Regards,

Sudheer

Edited by: sudheer kumar on Jan 29, 2009 7:02 AM

use meaningful subject

Edited by: Vijay Babu Dudla on Jan 29, 2009 5:29 AM

Re: Performance issue

Former Member — Thu, 29 Jan 2009 06:05:08 GMT

Hi,

i suggest you to write ONLY this query in the program and then execute...

SE30 and ST05.

Based on the outcome, if the database time/performance is more, you need to modify the query...

SE30: Run time analysis: ABAP part should be more than the database part..

ST05: Database trace..You can check the time in seconds to fetch the data..from the table...(it should be ideally less--Only in Yellow color..Red color indicates bad performance)

Regards

Shiva

Re: Performance issue

Former Member — Thu, 29 Jan 2009 06:07:45 GMT

You can do the following changes:

1. Change LIPS_{VBELN = I_VBFA-VBELN to LIKP}VBELN = I_VBFA-VBELN

2. Since you are supplying only partner functin PARNR as input to the query, and are only interested in partners attached to delivery header (as you say VBPA~POSNR = 0), I would advice you to decouple VBPA from the join, as the query is occurring at item level. .

Re: Performance issue

Former Member — Thu, 29 Jan 2009 06:58:27 GMT

I will suggest you to use the view WB2_V_LIKP_LIPS2. And inner joins will not affect performance when compared to use of loops if you run two separate queries.

Re: Performance issue

Former Member — Thu, 29 Jan 2009 10:26:25 GMT

check in se30 and st05 .there you can check your performance.

Re: Performance issue

Former Member — Thu, 29 Jan 2009 11:41:14 GMT

> ST05: Database trace..You can check the time in seconds to fetch the data..from the table...

> (it should be ideally less--Only in Yellow color..Red color indicates bad performance)

recommendation is good

interpretation is wrong, red color means only that it need more than 100ms

This can be o.k., if 10.000 records are read!

Go to Table -> Summarize by SQL statements!

Statement should appear in first row, if it is the most expensive select statement.

Reason for large duration is:

+ many records are read => Really necessary?

+ or each record needs a long time, check MinTime/R or AvgTime/R, usually if they are larger

than 10.000 microseconds, then performance of the statement is bad!

For a good statement these entries go down to 200 or even 100 microseconds.

Siegfried