11-11-2011 7:40 AM
Hi Guys,
Any ideas on how to improve the performance of this select statement?
SELECT LIKP~VBELN LIKP~ZZST_OPERATION
LIPS~VGBEL LIPS~POSNR LIPS~WERKS
INTO (LIKP-VBELN, LIKP-ZZST_OPERATION,
LIPS-VGBEL, LIPS-POSNR, LIPS-WERKS)
FROM LIKP JOIN LIPS
ON LIKP~VBELN EQ LIPS~VBELN
WHERE LIKP~KUNNR EQ ZVXXSENDEN_MAST-KUNNR
AND LIKP~ZZST_OPERATION IN S_ZZST
AND LIPS~VGTYP NE C_V. "Only Deliveries based on SO
ON CHANGE OF LIPS-VGBEL. "Document number of the reference documen
MOVE C_NO TO W_ADD_RECORD.
Moderator Message: Use "code" tags to format your code.
Edited by: Suhas Saha on Nov 11, 2011 1:26 PM
11-11-2011 7:52 AM
Please use an Internal Table for storing all values that you pick from LIKP and LIPS ( I think you are using a select endselect statement now).
The internal table that you will use will contain the fields VBELN ,ZZST_OPERATION,VGBEL, POSNR and WERKS
For the where condition -is there a possibility for using all entries of ZVXXSENDEN_MAST ? if yes do it .
(WHERE LIKP~KUNNR EQ ZVXXSENDEN_MAST-KUNNR
AND LIKP~ZZST_OPERATION IN S_ZZST)
And now you will have to use AT NEW statement for the operation in the end. But because VGBEL is the third field in the list , AT NEW statement might not be possible for this internal table as such. YOu wil have to create a new internal table with the fields in the order : VBELN ,VGBEL, ZZST_OPERATION,POSNR and WERKS
Copy the data from first internal table to the second.
Sort this internal table by VBELN and VGBEL and finally use AT NEW STATEMENT for last functionality.
Regards,
Arun
11-11-2011 7:52 AM
Please use an Internal Table for storing all values that you pick from LIKP and LIPS ( I think you are using a select endselect statement now).
The internal table that you will use will contain the fields VBELN ,ZZST_OPERATION,VGBEL, POSNR and WERKS
For the where condition -is there a possibility for using all entries of ZVXXSENDEN_MAST ? if yes do it .
(WHERE LIKP~KUNNR EQ ZVXXSENDEN_MAST-KUNNR
AND LIKP~ZZST_OPERATION IN S_ZZST)
And now you will have to use AT NEW statement for the operation in the end. But because VGBEL is the third field in the list , AT NEW statement might not be possible for this internal table as such. YOu wil have to create a new internal table with the fields in the order : VBELN ,VGBEL, ZZST_OPERATION,POSNR and WERKS
Copy the data from first internal table to the second.
Sort this internal table by VBELN and VGBEL and finally use AT NEW STATEMENT for last functionality.
Regards,
Arun
11-11-2011 7:57 AM
thanks Arun! I will try this and let you know the results.
11-11-2011 9:35 AM
The simple reason is that your WHERE condition does not use a primary or secondary index of table LIKP. Since you have KUNNR, I suggest you try including table VLKPA in your join select.
Please also look at SAP note 185530 and search for previous discussions of this topic.
Thomas
11-11-2011 2:55 PM
I agree with Thomas. Please use VLKPA table for the fast selection using KUNNR field.
11-14-2011 9:11 AM
11-14-2011 11:14 AM
I would suggest not using 'NE'.
Instead try EQ 'all possible values'.
Also seems like this SELECT is inside a LOOP. You may try to get it outside the loop.
Thanks
Krishna
11-14-2011 3:06 PM
@Krishna Kishor Kammaje - There's nothing wrong with using NE on non-key fields.
Rob
11-15-2011 6:29 AM
@Rob Burbank
Cosnider a scneario:
If I know that VGTYP can be A or B and nothing else, then
Is it not better to use (LIPSVGTYP EQ 'A' OR LIPSVGTYP EQ 'B' ) rather than using LIPS~VGTYP NE C_V?
-Krishna
11-15-2011 2:10 PM
If I know that VGTYP can be A or B and nothing else, then
Is it not better to use (LIPSVGTYP EQ 'A' OR LIPSVGTYP EQ 'B' ) rather than using LIPS~VGTYP NE C_V?
Without trying it, I'd say that they would be about the same ("about" meaning not worth the while to determine the difference). If anything, I would expect the NE in this case to be somewhat quicker because it is ony checking one condition while the OR is checking two.
Avoid using NE on key fields when that would cause the database not to use the index though.
Rob
11-17-2011 3:41 AM
11-15-2011 8:58 AM
I would suggest not using 'NE'.
Please do not mix 2 recommendation:
+ If possible then change a negative condition into a positive condition.
For example: x can be 1,2,3, and 4 and nothing else.
Instead you writing x <> '4' , it is better to write x IN ( '1', '2', '3' )
But be aware, if a new value becomes possible, then the SELECT is incorrect.
Sometimes it is clear, that there will be no change or it is possible to get the possible values from a
buffered customizing table.
+ There is frequently the recommendation to skip the NE-conditions without replacement (your sentence sounds like this)
This is in general wrong, because it can imply then many more records are selected and transferred to the application
server. It must be decided for the actual problem, whether the condition can have such an effect and whether it can
have an negative effect on the optimizer to choose a suboptimal access path. Usually the condition is without effect on
the access path, but with effect on the result set (on the database!)
Siegfried
11-17-2011 6:37 AM
HI ,
Please check point 2 in SAP Note 185530.
Using the index table vlkpa will be a better choice.
Cheers,
Brian
11-18-2011 3:07 AM
Try to use an internal table to get the data and do your modifications by looping the internal table. that would be good..
you try the following code..
types: begin of ty_itbl,
vgbel type LIPS-VGBEL,
vbeln type LIKP-VBELN,
zzst_operation type LIKP-ZZST_OPERATION,
posnr type LIPS-POSNR,
werks type LIPS-WERKS,
end of ty_itbl.
data wt_itbl type table of ty_itbl,
data wa_itbl type ty_itbl.
SELECT LIKP~VBELN LIKP~ZZST_OPERATION
LIPS~VGBEL LIPS~POSNR LIPS~WERKS
INTO CORRESPONDING FIELDS OF TABLE WT_ITBL
FROM LIKP JOIN LIPS
ON LIKP~VBELN EQ LIPS~VBELN
WHERE LIKP~KUNNR EQ ZVXXSENDEN_MAST-KUNNR
AND LIKP~ZZST_OPERATION IN S_ZZST
AND LIPS~VGTYP NE C_V.
LOOP AT WT_ITBL INTO WA_ITBL.
*/*use control breaks..and what u need to change.../
*/*AT new vgbel...endat...*/
ENDLOOP.
01-20-2016 7:34 AM
Hi, this is how i will optimize this expensive SQL statement by using an internal tables for LIKP and LIPS.
SELECT from table ZVXXSENDEN_MAST...
SELECT VBELN KUNNR ZZST_OPERATION FROM LIKP
INTO CORRESPONDING FIELDS OF TABLE T_LIKP
WHERE KUNNR EQ ZVXXSENDEN_MAST-KUNNR AND ZZST_OPERATION IN S_ZZST.
IF SY-SUBRC EQ 0.
SELECT VBELN VGBEL POSNR WERKS FROM LIPS
INTO CORRESPONDING FIELDS OF TABLE T_LIPS " T_LIPS
FOR ALL ENTRIES IN T_LIKP
WHERE VBELN EQ T_LIKP-vbeln AND VGTYP NE C_V.
UNASSIGN <fs_output1>.
IF NOT T_LIPS[] IS INITIAL.
LOOP at T_LIPS ASSIGNING <fs_output1>.
"Processsing goes here....
ENDLOOP.
ENDIF.
ENDIF.
ENDSELECT.
Test it via ST12 for the SQL performance. Welcome your inputs too. Cheers !