Performance issue while using for all entries

[Former Member]

Hi,

I'm using "for all entries" at many levels of my report. But i'm facing problems where the base internal table used for "for all entries" sizes more than 7-8 thousand records. It works pretty well otherwise for limited number of rows. I was wondering if any of you can give me some pointers on this.. or if somebody faced the same problem and resolved it as well.

thanks,

Asim

[Former Member]

Hello Asim,

When ur using FOR ALL ENTRIES check the body of the driver table and select all the key fields of the database table.

For example.

select * from mara into table itab where matnr in so_matnr.
if not itab[] is initial.
select amtnr maktx into table itab1 for all entries in itab
where matnr eq itab-matnr
    and spras eq sy-langu.
endif.

Regards,

Vasanth

[AjithC]

Hi Asim,

'For all entries' will defenitly have performance issues if the number of records are high. This is because internally the query is getting transalated using 'IN' clause. If the records are high, there might be a split in query (not sure).

So I suggest you to try the following. Take one such query as example and try these various options. Do an SQL Trace (ST05) and then compare the results.

1. Use Join

2. Select all and ignore/delete the unwanted entries (Useful only if you are selecting most of the entries)

Regards

Ajith Chandran

[Former Member]

Hi Asim,

If you can paste your code, maybe we can suggest an alternative.

Regards

Raj

[Former Member]

Hi Asim

When you are for all entries, mostly you will be comparing only one of the fields from the internal table, say field1.

select ....

from ..

into table..

for all entries in it_xyz

where field1 = it_xyz-field1

....

In your internal table xyz, if there are 7-8 thousand records, it is not necessary that field1 is distinct in the internal table. There can be just 100 distinct field1..

So, just copy the entire internal table into temporary internal table.

it_temp[] = it_xyz[].

sort it_temp by field1.

delete adjacent duplicates from it_temp comparing field1.

Now you can use this temp table with less number of records in for all entries.

Regards

Navneet

[Former Member]

Hi all,

My query is a bit complex... as it refers to itself... I tried the self join technique first, it was quick but in cases where there were too many self references it really took its time to comeback or time out. but then i broke the self join and wrote two separate queries to get my result. below is the code:

  SELECT * FROM swpsteplog INTO CORRESPONDING FIELDS OF TABLE lt_swpsteplog FOR ALL ENTRIES IN lt_swwwihead
                WHERE wf_id = lt_swwwihead-wi_id
                  AND node_id NOT IN (117, 136) "117 deadline data fw to approver, 136 set deadline date
                  AND returncode <> ''.                     "= '0000'.
  SORT lt_swpsteplog BY wi_id node_id.
  DELETE ADJACENT DUPLICATES FROM lt_swpsteplog COMPARING wi_id node_id.

  IF lt_swpsteplog[] IS NOT INITIAL.
    SELECT * FROM swpsteplog INTO TABLE lt_swpsteplog2
                  FOR ALL ENTRIES IN lt_swpsteplog
                  WHERE wf_id = lt_swpsteplog-wf_id "lv_wi_id
                    AND pred_wi_id = lt_swpsteplog-wi_id
                    AND node_id <> lt_swpsteplog-node_id.

    APPEND LINES OF lt_swpsteplog2 TO lt_swpsteplog.
    SORT lt_swpsteplog BY wi_id node_id.
    DELETE ADJACENT DUPLICATES FROM lt_swpsteplog COMPARING wi_id node_id.

As you might have guessed this is a workflow report that i'm working on and the second select that i've written taps out all the predecessor nodes, without which the result is incomplete. here is the self join that i wrote as well.

  SELECT "DISTINCT
    a~wf_id a~pred_wi_id a~node_id a~wi_id a~task_id a~wi_agent
  INTO  table lt_steplog "TABLE
  FROM swpsteplog AS a
    INNER JOIN swpsteplog AS b ON
    a~wf_id = b~wf_id AND a~pred_wi_id = b~wi_id
    FOR ALL ENTRIES IN lt_swwwihead
    WHERE b~wf_id = lt_swwwihead-wi_id
    AND   b~returncode <>
    AND   b~node_id not in (117, 136).

regards,

Asim

[Former Member]

Hi,

I had the same problem... so, first of all i don't anymore use the SELECT * Statement, I just select the fields that interesting me.

In second, I don't use the for all entries too much cost, I prefer to use an indexed loop something like that

  SELECT bukrs belnr gjahr blart budat bstat
           FROM bkpf INTO TABLE tg_bkpf.

  IF tg_bkpf IS INITIAL.
    EXIT.
  ENDIF.

  SELECT bukrs belnr gjahr wrbtr shkzg bschl hkont fkber fistl projk dmbtr aufnr
           FROM bseg INTO TABLE tg_bseg

  vl_cpt = 1.

  SORT tg_bkpf BY bukrs belnr gjahr.
  SORT tg_bseg BY bukrs belnr gjahr hkont.

  LOOP AT tg_bkpf INTO sg_bkpf.
    LOOP AT tg_bseg INTO sg_bseg FROM vl_cpt.
      " key too high
      " the (18) means the 18 first character of the structure
      " the 18 are the concat of the table primary key
      IF sg_bseg(18) > sg_bkpf(18).
        vl_cpt = sy-tabix.
        EXIT.
        " same key
      ELSEIF sg_bseg(18) = sg_bkpf(18).

        " fill the fileds
        MOVE XX TO sg_final-XX.

        " add to the final table
        APPEND sg_final TO tg_final.
      ENDIF.
    ENDLOOP.
  ENDLOOP.

Don't use the IN, LIKE or else in the Where Clause, the = on the key, with all of this you should have better perf

Hope this will be helpfull!

Kevin

[Former Member]

Hi Asim

Dont use select * .. Instead of this mention all the fields.

Also dont use Corresponding fields. try to define an internal table in order and use into table internal table instead of corresponding.

Also try to fetch all the key fields in the table. then only u will get all the records

before using for all entried, sort and delete the duplicate entries.

Also check whether the internal table is not initial.

SELECT <b>Fields</b> FROM swpsteplog <b>INTO TABLE lt_swpsteplog</b> FOR ALL ENTRIES IN lt_swwwihead

WHERE wf_id = lt_swwwihead-wi_id

<b>AND ( node_id <> 117 and node_id <> 136)</b>* AND node_id NOT IN (117, 136) "117 deadline data fw to approver, 136 set deadline date

AND returncode <> ''. "= '0000'.

SORT lt_swpsteplog BY wi_id node_id.

DELETE ADJACENT DUPLICATES FROM lt_swpsteplog COMPARING wi_id node_id.

IF lt_swpsteplog[] IS NOT INITIAL.

SELECT <b>FIELDS</b>* FROM swpsteplog INTO TABLE lt_swpsteplog2

FOR ALL ENTRIES IN lt_swpsteplog

WHERE wf_id = lt_swpsteplog-wf_id "lv_wi_id

AND pred_wi_id = lt_swpsteplog-wi_id

AND node_id <> lt_swpsteplog-node_id.

instead of appending line use

<b>USE LOOP INTERNAL TABLE 1.

READ TABLE INTERNAL TABLE 2 WHERE CONDITION.

MOVE ALL THE FIELDS INTO INTERNAL TABLE 3.</b>

APPEND LINES OF lt_swpsteplog2 TO lt_swpsteplog.

SORT lt_swpsteplog BY wi_id node_id.

DELETE ADJACENT DUPLICATES FROM lt_swpsteplog COMPARING wi_id node_id.

Reward me if its helpful.

Regards

Ravi