Performance issue on LIPS table

[kavya_2017]

Hi Experts,

I need to know the delivery for particular batches and materials,Hence i am using the below select query in my program

SELECT vbeln

posnr

matnr

werks

lgort

charg

lfimg

meins FROM lips

INTO TABLE int_lips

FOR ALL ENTRIES IN int_mchb

WHERE vbeln IN s_vbeln

AND pstyv IN s_pstyv

AND matnr EQ int_mchb-matnr

AND werks EQ int_mchb-werks

AND lgort EQ int_mchb-lgort

AND charg EQ int_mchb-charg.

My program is fine when delivery is given in the selection screen but it is taking lot of time when no delivery is entered in the selection screen.

Please guide me how can i increase my program performance. Is there is any need to create the secondary index?

Thanks in advance.

Regards,

Kavya

[MarcinPciak]

Using

vbeln IN s_vbeln

slows down your query as the cost of IN operator is high. If it is empty then all records are processed. As this is the left most column in the table so it double slows down as the set of records can't be restrcited to smaller group before next fields are compared.

The best would be

select-options s_vbeln ... obligatory.

"or
if s_vbeln[] is not initial.
  select ....
endif.

Regards

Marcin

[kavya_2017]

Hi Marcin,

Thanks for your reply, but the user don't want VBELN as mandatory field because they want to search delivery based on the material number and batch which will be given in selection screen.

So i need to extract VBELN from LIPS when s_vbeln[] is empty.

Please let me know if there are any other possibilities.

Thanks,

Kavya

[Former Member]

kavya, why don't you use VLPMA table as suggested by Klaus?

When S_VBELN is empty you can get all the rowsfrom LIPS_VLPMA for all materials ... and if S_VBELN is not empty, you can use your existing query..

[Former Member]

You must have 2 versions of the select statement. One should be based on VBELN and one on Material number. Simply add MATNR as a secondary index in SE11 and have 2 selects one with VBELN 1 without. Use the hint option to make sure that your new MATNR index is picked up.

[former_member195402]

Hi,

Maybe it is better to use db view LIPS_VLPMA instead of LIPS for this looks via db table VLPMA with material number!

Regards,

Klaus

[kavya_2017]

Hi Klaus,

Thanks for your suggestion, it resolved my problem.