Select query from lips table too slow

[Former Member]

Hello experts,

I would like to find out if there are ways I can improve the performance of a select query in which there are no key fields in the WHERE clause. I have the following select statement from LIPS table and its quite slow:

 IF gt_mchb[] IS NOT INITIAL.

    SELECT matnr
           werks
           lgort
           charg
           lfimg 
           ntgew 
           FROM lips INTO TABLE gt_lips
           FOR ALL ENTRIES IN gt_mchb
           WHERE matnr = gt_mchb-matnr
           AND werks = gt_mchb-werks
           AND lgort = gt_mchb-lgort
           AND charg = gt_mchb-charg
           AND ntgew = gt_mchb-clabs.

  ENDIF. 

Thanks,

Mwela.

[Former Member]

Use "Delivery Items by Material" index table VLPMA, if you want to search delivery items by material.

You can make a join between VLPMA (as the leading table in the join) and LIPS and it will definitely be faster as VLPMA is driven by material. I only used MATNR in WHERE clause of VLPMA but I advise you try to supply as many keys (like VSTEL, VKORG, KUNNR, KUNAG and son) as you can to improve performance

SELECT vlpma~matnr
       lips~werks
       lips~lgort
       lips~charg
       lips~lfimg
       lips~ntgew
       FROM vlpma INNER JOIN lips
       ON vlpma~vbeln = lips~vbeln AND
          vlpma~posnr = lips~posnr
       INTO TABLE gt_lips
       FOR ALL ENTRIES IN gt_mchb
       WHERE vlpma~matnr = gt_mchb-matnr
       AND lips~werks = gt_mchb-werks
       AND lips~lgort = gt_mchb-lgort
       AND lips~charg = gt_mchb-charg
       AND lips~ntgew = gt_mchb-clabs.

[Former Member]

Hi,

You can try and use the Primary or secondary indexes of the table in the where fields.

For any table you can see the primary or seconday index in the below tables.

Primary Index: Can be seen in SE11 or SE16

or

SELECT *

FROM dd03l

INTO TABLE ts_tab

WHERE tabname = 'table \name'

AND Keyflag = 'X'.

Secondary Index: Can be seen from table DD17s

SELECT *

FROM dd17s

INTO TABLE ts_table

WHERE sqltab = 'tablename'

AND as4local = 'A'

AND as4vers = '0000'.

[surajarafath]

Go to LIPS table using SE11 and Create index for the table according to sequence.

Then you see the performance.

[yuri_ziryukin]

The only valuable and correct answer in this thread was the first one from Vishnu.

[former_member195402]

Hi,

please also don't forget, that you will get DISTINCT data after FOR ALL ENTRIES !

If you have 2 entries with LFIMG 3 and 5, then you will get both entries, but if you have 2 entries in LIPS with LFIMG 4 and 4, then you will get only one entry with 4.

Regards,

Klaus