topic Re: Performance issue on LIPS table in Application Development and Automation Discussions

Performance issue on LIPS table

kavya_2017 — Tue, 15 Feb 2011 07:44:26 GMT

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

Re: Performance issue on LIPS table

MarcinPciak — Tue, 15 Feb 2011 08:17:01 GMT

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

Re: Performance issue on LIPS table

Former Member — Tue, 15 Feb 2011 08:31:34 GMT

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

Re: Performance issue on LIPS table

kavya_2017 — Tue, 15 Feb 2011 09:46:47 GMT

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

Re: Performance issue on LIPS table

Former Member — Tue, 15 Feb 2011 16:49:41 GMT

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

Re: Performance issue on LIPS table

Former Member — Tue, 15 Feb 2011 20:19:30 GMT

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.

Re: Performance issue on LIPS table

kavya_2017 — Fri, 25 Feb 2011 09:30:23 GMT

Hi Klaus,

Thanks for your suggestion, it resolved my problem.