01-05-2012 9:45 AM
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.
01-05-2012 9:59 AM
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.
01-09-2012 5:58 PM
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'.
01-10-2012 2:13 AM
Go to LIPS table using SE11 and Create index for the table according to sequence.
Then you see the performance.
01-10-2012 8:01 AM
The only valuable and correct answer in this thread was the first one from Vishnu.
01-10-2012 8:15 AM
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