Performance issue

[Former Member]

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

[Former Member]

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

[Former Member]

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. .

[Former Member]

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.

[Former Member]

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

[Former Member]

> 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