topic Re: How to optimize this select statement better? in Application Development and Automation Discussions

How to optimize this select statement better?

Former Member — Fri, 11 Nov 2011 07:40:29 GMT

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

Re: How to optimize this select statement better?

Former Member — Fri, 11 Nov 2011 07:52:45 GMT

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

Re: How to optimize this select statement better?

Former Member — Fri, 11 Nov 2011 07:57:31 GMT

thanks Arun! I will try this and let you know the results.

Re: How to optimize this select statement better?

ThomasZloch — Fri, 11 Nov 2011 09:35:04 GMT

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

Re: How to optimize this select statement better?

yuri_ziryukin — Fri, 11 Nov 2011 14:55:15 GMT

I agree with Thomas. Please use VLKPA table for the fast selection using KUNNR field.

Re: How to optimize this select statement better?

kesavadas_thekkillath — Mon, 14 Nov 2011 09:11:37 GMT

Ignore

Edited by: Keshav.T on Nov 14, 2011 2:42 PM

Re: How to optimize this select statement better?

kammaje_cis — Mon, 14 Nov 2011 11:14:35 GMT

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

Re: How to optimize this select statement better?

Former Member — Mon, 14 Nov 2011 15:06:31 GMT

@Krishna Kishor Kammaje - There's nothing wrong with using NE on non-key fields.

Rob

Re: How to optimize this select statement better?

kammaje_cis — Tue, 15 Nov 2011 06:29:28 GMT

@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 (LIPS_{VGTYP EQ 'A' OR LIPS}VGTYP EQ 'B' ) rather than using LIPS~VGTYP NE C_V?

-Krishna

Re: How to optimize this select statement better?

Former Member — Tue, 15 Nov 2011 08:58:35 GMT

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

Re: How to optimize this select statement better?

Former Member — Tue, 15 Nov 2011 14:10:19 GMT


If I know that VGTYP can be A or B and nothing else, then
Is it not better to use (LIPS_{VGTYP EQ 'A' OR LIPS}VGTYP 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

Re: How to optimize this select statement better?

kammaje_cis — Thu, 17 Nov 2011 03:41:19 GMT

Thanks Rob. That helped me to understand 'NE'.

Re: How to optimize this select statement better?

brian_zhu — Thu, 17 Nov 2011 06:37:34 GMT

HI ,

Please check point 2 in SAP Note 185530.

Using the index table vlkpa will be a better choice.

Cheers,

Brian

Re: How to optimize this select statement better?

surajarafath — Fri, 18 Nov 2011 03:07:55 GMT

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.

Re: How to optimize this select statement better?

vjreburiano — Wed, 20 Jan 2016 07:34:51 GMT

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.

ENDSELECT.

Test it via ST12 for the SQL performance. Welcome your inputs too. Cheers !