topic Re: improve abap code in Application Development and Automation Discussions

improve abap code

former_member210823 — Wed, 09 Dec 2015 05:41:50 GMT

hello

How to incease speed of this query ?

select afko~aufnr afko~PLNBEZ

from afko join aufk on afko~aufnr = aufk~aufnr

into table IT_all_ord

where afko~aufnr like '1-%'

and ( AFKO~PLNTY EQ 'N' OR AFKO~PLNTY EQ 'R' OR AFKO~PLNTY EQ 'S' OR AFKO~PLNTY EQ 'M' )

and aufk~objnr in ( select objnr from jcds where STAT = 'I0009' AND INACT = ' '

and jcds~UDATE > budat -low and jcds~UDATE < BUDAT-HIGH ).

Re: improve abap code

Former Member — Wed, 09 Dec 2015 06:32:33 GMT

This message was moderated.

Re: improve abap code

monami — Wed, 09 Dec 2015 06:36:21 GMT

Hi,

Do the second select first and break the operation into two selections.

Avoid the OR operations.

Thanks & regards,

Monami.

Re: improve abap code

Former Member — Wed, 09 Dec 2015 07:01:04 GMT

Hi,

yes and you can use OR operations in your where conditions as it is.

I hope you will get date field BUDAT in your selection screen.

also let me know are u getting AUFNR field in selection screen?

Regards,

Kannan

Re: improve abap code

former_member210823 — Wed, 09 Dec 2015 07:06:39 GMT

this is my paramaters :

*parameters

SELECTION-SCREEN BEGIN OF BLOCK ORDER WITH FRAME TITLE text-t01.

SELECTION-SCREEN BEGIN OF LINE.

SELECTION-SCREEN COMMENT 1(20) text-112.

SELECT-OPTIONS:ORDER for AFKO-AUFNR OBLIGATORY.

SELECTION-SCREEN END OF LINE.

SELECTION-SCREEN BEGIN OF LINE.

SELECTION-SCREEN COMMENT 1(20) text-113.

SELECT-OPTIONS:MATERIAL FOR AFKO-PLNBEZ .

SELECTION-SCREEN END OF LINE.

SELECTION-SCREEN BEGIN OF LINE.

SELECTION-SCREEN COMMENT 1(20) text-114.

SELECT-OPTIONS:BUDAT for AFRU-BUDAT OBLIGATORY .

SELECTION-SCREEN END OF LINE.

SELECTION-SCREEN END OF BLOCK ORDER.

Re: improve abap code

monami — Wed, 09 Dec 2015 07:08:41 GMT

Hi ,

Please be careful while using 'for all entries'. It does not always make your code efficient.

Thanks & regards,

Monami.

Re: improve abap code

former_member210823 — Wed, 09 Dec 2015 07:10:46 GMT

and I change my code for example of it ?

dose program of kannan bhavish is better than it?

TABLES :AFKO,AFRU.

*parameters

SELECTION-SCREEN BEGIN OF BLOCK ORDER WITH FRAME TITLE text-t01.

SELECTION-SCREEN BEGIN OF LINE.

SELECTION-SCREEN COMMENT 1(20) text-112.

SELECT-OPTIONS:ORDER for AFKO-AUFNR OBLIGATORY.

SELECTION-SCREEN END OF LINE.

SELECTION-SCREEN BEGIN OF LINE.

SELECTION-SCREEN COMMENT 1(20) text-113.

SELECT-OPTIONS:MATERIAL FOR AFKO-PLNBEZ .

SELECTION-SCREEN END OF LINE.

SELECTION-SCREEN BEGIN OF LINE.

SELECTION-SCREEN COMMENT 1(20) text-114.

SELECT-OPTIONS:BUDAT for AFRU-BUDAT OBLIGATORY .

SELECTION-SCREEN END OF LINE.

SELECTION-SCREEN END OF BLOCK ORDER.

TYPES: BEGIN OF ty_all_ord ,

aufnr TYPE aufk-aufnr,

PLNBEZ TYPE afko-PLNBEZ,

END OF ty_all_ord,

tyh_all_ord type hashed table of ty_all_ord WITH UNIQUE KEY aufnr PLNBEZ .

data : IT_all_ord type tyh_all_ord.

select distinct afko~aufnr afko~PLNBEZ

from afko join aufk on afko~aufnr = aufk~aufnr

join jcds on jcds~objnr EQ aufk~objnr

into table IT_all_ord

where afko~aufnr like '1-%'

and jcds~stat EQ 'I0009'

AND jcds~udate IN BUDAT

AND jcds~inact EQ space

and ( AFKO~PLNTY EQ 'N' OR AFKO~PLNTY EQ 'R' OR AFKO~PLNTY EQ 'S' OR AFKO~PLNTY EQ 'M' )

and NOT EXISTS ( SELECT stat

FROM jcds

WHERE objnr EQ aufk~objnr

AND inact EQ space

AND ( stat EQ 'I0076'

OR stat EQ 'I0043'

OR stat EQ 'E0005' ) ).

Re: improve abap code

former_member184158 — Wed, 09 Dec 2015 07:19:33 GMT

This message was moderated.

Re: improve abap code

former_member245321 — Wed, 09 Dec 2015 07:29:36 GMT

Your sub-select always does a full table scan for every entry from the upper select.

Put aufk~objnr on your sub-select to check whether table jcds contains entries concerning aufk~objnr.

select afko~aufnr afko~PLNBEZ

from afko join aufk on afko~aufnr = aufk~aufnr

into table IT_all_ord

where afko~aufnr like '1-%'

and ( AFKO~PLNTY EQ 'N' OR AFKO~PLNTY EQ 'R' OR AFKO~PLNTY EQ 'S' OR AFKO~PLNTY EQ 'M' )

and aufk~objnr in ( select objnr from jcds where

jcds ~OBJNR = aufk~objnr "<<<---

budat -low and jcds~UDATE < BUDAT-HIGH ).

Re: improve abap code

former_member210823 — Wed, 09 Dec 2015 08:04:40 GMT

is not better used view insted of for all entries ?

Re: improve abap code

Former Member — Wed, 09 Dec 2015 08:08:36 GMT

This message was moderated.

Re: improve abap code

matt — Wed, 09 Dec 2015 09:26:41 GMT

FOR ALL ENTRIES is, in the vast majority of cases, less performant than a properly crafted INNER JOIN. This has been discussed to death, and I won't let it be discussed further.

For the OP, try using IN instead of the ORs. E.g. AFKO~PLNTY IN ('N', 'R', 'S', 'M')

It is impossible with these performance questions to be definitive, as much depends on the intelligence of the SQL optimiser. the actual data being queried and indexes that have been set up. You really have to try different things and see what works - in conjunction with analysis of EXPLAIN SQL in ST05 (or other places), to see how things are handled at a database level.