topic Re: Select statement extremely slow when field dont have value in Application Development and Automation Discussions

Select statement extremely slow when field dont have value

siongchao_ng — Tue, 07 May 2013 03:56:51 GMT

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

Re: Select statement extremely slow when field dont have value

arindam_m — Tue, 07 May 2013 04:08:19 GMT

Hi,

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

Cheers,

Arindam

Re: Select statement extremely slow when field dont have value

krishna_k19 — Tue, 07 May 2013 04:16:52 GMT

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

Re: Select statement extremely slow when field dont have value

Former Member — Tue, 07 May 2013 04:21:22 GMT

Hi,

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

Regards

Ajit Tiwari

Re: Select statement extremely slow when field dont have value

Former Member — Tue, 07 May 2013 04:25:53 GMT

Hi,

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

Re: Select statement extremely slow when field dont have value

siongchao_ng — Tue, 07 May 2013 04:28:10 GMT

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

Re: Select statement extremely slow when field dont have value

krishna_k19 — Tue, 07 May 2013 04:32:02 GMT

Then write a code

if lt_process_order is not initial.

then process your query.

endif..

try like this

Rgds,

Krishna

Re: Select statement extremely slow when field dont have value

Former Member — Tue, 07 May 2013 04:33:24 GMT

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.

endif.

Thanks,

Namrata

Re: Select statement extremely slow when field dont have value

Former Member — Tue, 07 May 2013 04:36:24 GMT

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

Re: Select statement extremely slow when field dont have value

SwadhinGhatuary — Tue, 07 May 2013 04:42:15 GMT

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.

Re: Select statement extremely slow when field dont have value

matt — Tue, 07 May 2013 04:57:56 GMT

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

Re: Select statement extremely slow when field dont have value

matt — Tue, 07 May 2013 05:00:39 GMT

No, really. It isn't.

Re: Select statement extremely slow when field dont have value

RaymondGiuseppi — Tue, 14 May 2013 13:54:24 GMT

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

Re: Select statement extremely slow when field dont have value

ThomasZloch — Tue, 14 May 2013 14:29:47 GMT

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