Select statement extremely slow when field dont have value

[siongchao_ng]

Hi all,

Following is the select statement I use: If user enter a value for ARBPL in the select-options field, the selection process is very slow because the user entered value does not exist in database table. If there is ARBPL value, then the selection process is ok. How can I rectify this problem whenever there is no value in the database tables entered by user in the select options field. Thanks.

   get process order main material
  SELECT a~aufnr
         a~werks
         b~plnbez
         b~gstrp
         b~gltrp
         c~wemng
         d~bedid
         e~objid
         e~arbpl
*         f~xloek
         f~matkl

    INTO CORRESPONDING FIELDS OF TABLE lt_process_order
    FROM aufk AS a
    INNER JOIN afko as b
    ON b~aufnr = a~aufnr
    INNER JOIN afpo AS c
    ON c~aufnr = b~aufnr
    INNER JOIN afvc AS d
    on d~bedid = b~bedid
    INNER JOIN crhd AS e
    ON e~objid = d~arbid
    INNER JOIN mara AS f
    ON f~matnr = b~plnbez
    WHERE a~werks IN s_plant
    AND   a~aufnr IN s_po
    AND   b~gstrp IN s_from
    AND   e~arbpl IN s_res
    AND   f~matkl LIKE 'PG%'
    AND   a~autyp = '40' "only take process order, as aufk also share with orders not related to production
    AND   e~verwe = '0008'. "only take resource related to production

[siongchao_ng]

Hi all,

Following is the select statement I use: If user enter a value for ARBPL in the select-options field, the selection process is very slow because the user entered value does not exist in database table. If there is ARBPL value, then the selection process is ok. How can I rectify this problem whenever there is no value in the database tables entered by user in the select options field. Thanks.

   get process order main material
  SELECT a~aufnr
         a~werks
         b~plnbez
         b~gstrp
         b~gltrp
         c~wemng
         d~bedid
         e~objid
         e~arbpl
*         f~xloek
         f~matkl

    INTO CORRESPONDING FIELDS OF TABLE lt_process_order
    FROM aufk AS a
    INNER JOIN afko as b
    ON b~aufnr = a~aufnr
    INNER JOIN afpo AS c
    ON c~aufnr = b~aufnr
    INNER JOIN afvc AS d
    on d~bedid = b~bedid
    INNER JOIN crhd AS e
    ON e~objid = d~arbid
    INNER JOIN mara AS f
    ON f~matnr = b~plnbez
    WHERE a~werks IN s_plant
    AND   a~aufnr IN s_po
    AND   b~gstrp IN s_from
    AND   e~arbpl IN s_res
    AND   f~matkl LIKE 'PG%'
    AND   a~autyp = '40' "only take process order, as aufk also share with orders not related to production
    AND   e~verwe = '0008'. "only take resource related to production

[arindam_m]

Hi,

Better to uncomplicate the Join and achive the same in 2-3 SELECT statements.

Cheers,

Arindam

[matt]

No, really. It isn't.

[krishna_k19]

Hi chao,

    why you are using inner joins, better inner joins use "For all entries" and validate selection screen parameters through At Selection Screen events..

Regads,

Krishna

[Former Member]

Hi,

If you want to improve the performance of this query,Use 'for all entries' Instead of Join.

Regards

Ajit Tiwari

[Former Member]

Hi,

Dont use inner join's it reduces performance, better is to use "for all entries"

[siongchao_ng]

Sorry guys, the error is not due to the select statement but due to no entries loaded into the lt_process_order internal table. This when used in subsequent select statement which depended on this table is causing all data to be selected.

Eg: Subsequent select statement is causing all records selected due to lt_process_order table is empty!!

   SELECT a~mblnr
         a~mjahr
         a~zeile
         a~werks
         a~aufnr
         a~grund
         a~bwart
         a~erfmg
         b~matnr
         b~mtart
         b~matkl
   INTO CORRESPONDING FIELDS OF TABLE lt_mseg
   FROM mseg AS a
  INNER JOIN mara AS b
  ON a~matnr = b~matnr
  FOR ALL ENTRIES IN lt_process_order
  WHERE a~aufnr = lt_process_order-aufnr
  AND  b~mtart = 'ZPMA'
  AND  a~werks IN s_plant
  AND ( a~bwart EQ '261'
            OR a~bwart EQ '262' ).

[krishna_k19]

Then write a code

if lt_process_order is not initial.

   then process your query.

endif..

try like this

Rgds,

Krishna

[Former Member]

Put a condition before you process the subsequent select statement.

Check for the lt_process_order holds any data or not.

i.e if lt_process_order[] is not initial.

   <your select statement>

  endif.

Thanks,

Namrata

[matt]

A common error.

Almost as common as the erroneous belief that many responders to this post seem to have. I've said it before and I'll say it again.

In nearly ever case, INNER JOIN performs better than FOR ALL ENTRIES.

The only exceptions are when one or more of the tables is buffered, or with very specific forms of data. In these cases, you must test to find out which is most efficient, as there are no hard and fast rules.

I don't know where this myth about INNER JOIN arose, but I'm pleased that now, at last, in the ABAP for HANA courses it is being debunked by SAP.

http://scn.sap.com/thread/1174072

[RaymondGiuseppi]

There are three common beginner's errors with FOR ALL ENTRIES

- to forget to check internal table is not empty, else whole database will be read as the WHERE clause will be ignored

- to forget to select an unique identifier for every records (whole primary keys), so duplicate records deletion don't delete similar but actually different records

- to hope that performance will be better than JOIN statements (especially with huge internal table in the clause)

Regards,

Raymond

[Former Member]

Hi Chao,

Instead of using the above complicated INNER JOIN code just divide it into select statements for different tables one by one e.g. AUFK, AFKO, AFPO, CRHD & MARA.

While you select from one table use those retrieved entries to filter data from the next select statement using "FOR ALL ENTRIES in....".

For example:

SELECT        AUFNR

                    AUART

                    AUTYP

                    REFNR

                    ERNAM

                    ERDAT

                    AENAM

                    AEDAT

FROM aufk

into table it_aufk

where ...............(some condition - if any)

If sy-subrc is initial.

SELECT      AUFNR

                    GLTRP

                    GSTRP

                    FTRMS

FROM afko

into table it_afko

FOR ALL ENTRIES IN it_aufk

WHERE aufnr = it_aufk-aufnr.

Endif.

This will improve your performance and simplify the code rather than using INNER JOIN.

Hope this helps.

Regards,

Arnab

[SwadhinGhatuary]

Hello Siong Chao

validate selection screen parameters through At Selection Screen on <field_name>events or by  At Selection Screen,then use "For all entries" It's better .Join are beneficial for 2 tables only.

[ThomasZloch]

There is maybe still room for more, you are joining AFVC on non-indexed field BEDID (or did you create a secondary index for it?), and CRHD without leading field OBJTY (there is maybe just one fixed value that you can add to the ON condition?).

In addition to what was said already I also repeat my usual arguments: Joins can be difficult to be constructed correctly, but they have the big advantage that the CBO can flexibly determine the best access paths for the involved tables at runtime based on actual selection conditions provided. Also, many times the result is needed in one final internal table e.g. for SALV output, and when using a chain of FAE's you still need combine everything subsequently.

Thomas