performance issue !!!!

[Former Member]

Hello friends...

Wat kind of performance tuning i can go for this...

plis help me ...

SELECT LFA1_{LIFNR KNVP}KUNNR

FROM LFA1

JOIN KNVP ON KNVP_{LIFNR = LFA1}LIFNR

JOIN KNA1 ON KNA1_{KUNNR = KNVP}KUNNR

INTO CORRESPONDING FIELDS OF TABLE IT_KUNNR

WHERE LFA1~LIFNR BETWEEN '000000002' AND '0001222222'

• AND lfa1~land1 = 'DE'

AND LFA1~LAND1 IN S_LAND1

AND KNVP~VKORG IN S_VKORG

AND KNVP~VTWEG IN S_VTWEG

AND KNVP~SPART IN S_SPART

AND KNA1~SORTL NOT LIKE 'C?%'

AND KNA1~SORTL NOT LIKE 'OB'

AND KNA1~SORTL NOT LIKE 'DO NOT USE'.

regards:

Sapna

[Former Member]

Hi Sapna,

I don't have clear idea. but go through this link below i think it will help you.

http://www.sapbrainsonline.com/ARTICLES/TECHNICAL/optimization/optimization.html#Tools%20available%2...

Regards

Raghavendra.D.S

[Former Member]

HI Sapna,

First thing is select all key fields from both the table and remove the hard coding of LIFNR.

Regards,

Atish

[Former Member]

I think this is one of the rare cases where it might be better to split it into two or three SELECTs and use FOR ALL ENTRIES.

You are also selecting a large amount of data from LFA1. That might also be your problem.

Rob

[KjetilKilhavn]

I don't know if it will help, but index-wise your best table seems to be LFA1. So perhaps (you have to test it to find out if there is anything to gain in your system) you will get better performance by selecting from LFA1 first, and then selecting with FOR ALL ENTRIES.

There is also an index on SORTL in KNA1, but the danger with that is that the optimizer may dislike the NOT LIKE criteria and not use the SORTL index.

From LFA1 you get the vendor number, which is indexed in KNVP and thus should give good performance.

I see two big disadvantages of such a solution. It requires additional communication between the database and the application server (including data transport). Second, and perhaps more importantly, the FOR ALL ENTRIES criterium could be basing its selection on a monstrous internal table of vendors, which is very bad for performance.

That should leave you confused for the weekend; Did he give any advice or didn't he?

[kesavadas_thekkillath]

while using joins try to mention the key in a flow

ie...

SELECT LFA1_{LIFNR KNVP}KUNNR

FROM LFA1 inner join

KNVP ON KNVP_{LIFNR = LFA1}LIFNR

inner join KNA1 on KNVP_{KUNNR = KNA1}KUNNR ...

and instead of corresponding try appending tables.

try to avoid 'like' as much as possible...because it needs a complete search of that column.

or try writing this query with two selects....and use for all entries

[amit_khare]

You need to break this SELECT instead of joins.

The major flaw as per performance here is use of so many Negative Logical Conditions in the Where Clause.

Even passing all the key fields to extract data will not be able to help you a lot.

For more on performance tuning during Data Extraction refer the link -

http://www.sap-img.com/abap/performance-tuning-for-data-selection-statement.htm

Regards,

Amit

Reward all helpful replies.

[Former Member]

You have 3 conditions on SORTL

AND KNA1~SORTL NOT LIKE 'C?%'
AND KNA1~SORTL NOT LIKE 'OB'
AND KNA1~SORTL NOT LIKE 'DO NOT USE'.

Only the first is a 'not like' test the other 2 are 'Not Equal' tests.

The use of NOT conditions in your select will create long run times. It may be quicker to select the rows without these conditions and then remove them within your program. Test this!

MattG.

[Former Member]

Hi Sapna,

I also encountered similiar case like this. What I did was -- I broke the select statement into 2 selects.

So you can use the select statement as:

select (fields) from lfa1 as a innerjoin knvp as b

into itab1 where (conditions).

sort itab1 by kunnr.

delete adjacent duplicates from itab1 comparing kunnr.

if itab1[] is not initial.

select (fields) from kna1

for all entries in itab2

where kunnr = itab1-kunnr

and (conditions)

endif.

after this you can move the required fields to another final internal table appropriately,

Try this. this will definitely work.

Reward points if useful.

Br,

Harmeet Singh.