improve abap code

[former_member210823]

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 ).

[former_member396478]

This message was moderated.

[former_member184158]

This message was moderated.

[monami]

Hi,

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

Avoid the OR operations.

Thanks & regards,

Monami.

[former_member396478]

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

[former_member210823]

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.

[monami]

Hi ,

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

Thanks & regards,

Monami.

[former_member210823]

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' ) ).

[former_member396478]

This message was moderated.

[former_member245321]

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 ).

[former_member210823]

is not better used view insted of for all entries ?

[matt]

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.