Select Query using ForAllEntries with Huge Internal Table

[Former Member]

Hi All,

I'm currently facing query which takes ages to load (or even Dump when it takes too long).

I have a internal table "itab" which contained tons of data, [49927x30(336)]Standard Table, and i have a query as below which will do ForAllEntries on mentioned internal table.

SELECT a~vkorg a~kunnr a~vbeln b~sfakn b~fksto

          INTO TABLE it_bill FROM vrkpa AS a

          JOIN vbrk AS b ON b~vbeln = a~vbeln

          FOR ALL ENTRIES IN itab WHERE a~kunde IN r_kunde

                                      AND a~vkorg IN s_vkorg

                                      AND a~fkart IN ('Z01','Z51')

                                      AND a~vbeln = itab-vbeln.

Please help to guide how to make the query processed much faster.

Thanks!

[Former Member]

Hi All,

I'm currently facing query which takes ages to load (or even Dump when it takes too long).

I have a internal table "itab" which contained tons of data, [49927x30(336)]Standard Table, and i have a query as below which will do ForAllEntries on mentioned internal table.

SELECT a~vkorg a~kunnr a~vbeln b~sfakn b~fksto

          INTO TABLE it_bill FROM vrkpa AS a

          JOIN vbrk AS b ON b~vbeln = a~vbeln

          FOR ALL ENTRIES IN itab WHERE a~kunde IN r_kunde

                                      AND a~vkorg IN s_vkorg

                                      AND a~fkart IN ('Z01','Z51')

                                      AND a~vbeln = itab-vbeln.

Please help to guide how to make the query processed much faster.

Thanks!

[sachin_yadav3]

Have you tried using open cursor method on this inner join?

[Juwin]

If you don't want to change the current table structures: You need to split the internal table ITAB, into smaller portions, remove duplicates from those and do FOR ALL ENTRIES for those smaller portions. Each row of ITAB is converted into a OR condition, when you do FAE. And, it is a well known fact that DB access slows down when you have more OR conditions.

If you can change the way the program is currently written, then I would suggest you to check the possibility of replacing FAE by JOINS.

Thanks,

Juwin

[raphael_almeida]

I agree with Juwin, the ideal would actually decrease the load of the internal table "itab" and use it on FAE, I believe your application will be lighter with it.

BR,

Raphael Pacheco.

[jay_kumar8]

Try Removing a~kunde IN r_kunde

and append the entries to an  internal table lt_kunde having field Kunde

and add as a~kunde = lt_kunde-kunde.

Regards

Raj

[jay_kumar8]

Sorry yaar above mentioned is wrong...

You can use seperate query for retreving data by using for all entries from the tables VRKPA and VBRK.

After that append to final internal table.

Regards

Raj

[former_member182371]

Hi,

please have a look at max bianchi 's answer in this thread:

SAPSQL_STMNT_TOO_LARGE

maybe you could try something similar to this approach.

Best regards,

Pablo

[former_member235395]

Hi Fox,

I think, all options mentioned are valid. So, are you try separating the querys?

Do first query to VBRK using for all entries from ITAB and then, do a second query to VRKPA using for all entries from FIRST QUERY. After that, complete IT_BILL

Regards,

[Jelena_Perfiljeva]

Where do you get ITAB content from? FAE may not even be needed here.

[AbhishekV]

Hi Fox,

-Considering removing the s_vkorg from the select statement and filter the entries locally in your code by using a delete statement.

-Please ensure that you remove duplicates if any in the range r_kunde.

-Considering evaluating the number of records that you would fetch using full table scan instead of using a For all entries and if total number of records are not significantly high you might opt to remove for all entries and intstead select all the entries.

Check out these options and I am sure this would show results of a performance improvement.

Fox Haund wrote:

Hi All,

I'm currently facing query which takes ages to load (or even Dump when it takes too long).
I have a internal table "itab" which contained tons of data, [49927x30(336)]Standard Table, and i have a query as below which will do ForAllEntries on mentioned internal table.

SELECT a~vkorg a~kunnr a~vbeln b~sfakn b~fksto
          INTO TABLE it_bill FROM vrkpa AS a
          JOIN vbrk AS b ON b~vbeln = a~vbeln
          FOR ALL ENTRIES IN itab WHERE a~kunde IN r_kunde
                                      AND a~vkorg IN s_vkorg
                                      AND a~fkart IN ('Z01','Z51')
                                      AND a~vbeln = itab-vbeln.


Please help to guide how to make the query processed much faster.
Thanks!

[Former Member]

Dear All,

Thanks for all the reply.

For clarifying, the itab table consist of collective of data from BKPF join with BSID and BSAD (query sample of BKPF - BSID join as below).

SELECT tbs~kunnr tbs~augdt tbs~augbl tbs~gjahr tbs~belnr tbs~budat
          tbs~bldat tbs~waers tbs~xblnr tbs~blart tbs~monat tbs~shkzg
          tbs~gsber tbs~dmbtr tbs~wrbtr tbs~zfbdt tbs~zterm tbs~rebzg tbs~vbeln
          tbs~xragl tbk~bktxt
   APPENDING TABLE itab FROM bsid AS tbs
      INNER JOIN bkpf AS tbk
              ON tbk~bukrs = tbs~bukrs
             AND tbk~belnr = tbs~belnr
             AND tbk~gjahr = tbs~gjahr
           WHERE tbk~bukrs = p_bukrs
             AND tbk~blart IN ('UI','RV')
             AND tbk~xreversal = ''     "
             AND tbs~kunnr    IN s_kunnr
             AND tbs~budat    <= p_keydat
             AND tbs~bstat     = ''     "
             AND tbs~umskz     = ''

And i have tried one of the opinion for break down the FAE query into 2 as below, but the result (check with SE30) is more heavier than before.

Has i made any mistake, or any better approach?

For switching FAE for Inner Join, currently i believe its not viable since the itab table is collective data and for include vrkpa / vbrk in the itab join (consist of 4/5 select query) will much more heavy (any ideas is welcome though).

Thanks!

SELECT vkorg fkart kunnr vbeln
          INTO TABLE it_bill FROM vrkpa
          FOR ALL ENTRIES IN itab WHERE kunde IN r_kunde
                                      AND  vkorg IN s_vkorg
                                      AND  fkart IN ('Z01','Z51')
                                      AND  vbeln  = itab-vbeln.

SELECT vbeln fksto sfakn
          INTO CORRESPONDING FIELDS OF TABLE it_bill_cancel FROM vbrk
          FOR ALL ENTRIES IN it_bill WHERE  vkorg = it_bill-vkorg
                                      AND  fkart = it_bill-fkart
                                      AND  vbeln = it_bill-vbeln
                                      AND  fksto = 'X'.

IF it_bill_cancel[] IS NOT INITIAL.
   SORT : it_bill, it_bill_cancel BY vbeln.
   LOOP AT it_bill_cancel.
       READ TABLE it_bill WITH KEY vbeln = it_bill_cancel-vbeln BINARY SEARCH.
       IF sy-subrc = 0.
           it_bill-fksto  = it_bill_cancel-fksto.
           it_bill-sfakn = it_bill_cancel-sfakn.
           MODIFY it_bill INDEX sy-tabix TRANSPORTING fksto sfakn.
       ENDIF.
   ENDLOOP.
ENDIF.

[jay_kumar8]

If Retreving data from VBRK takes long time then you can check by changing the order as per the database order of fields...

1. SELECT vbeln fksto sfakn 
2.           INTO CORRESPONDING FIELDS OF TABLE it_bill_cancel FROM vbrk 
3.           FOR ALL ENTRIES IN it_bill WHERE vbeln = it_bill-vbeln 
4.                                       AND  fkart = it_bill-fkart 
5.                                       AND   vkorg = it_bill-vkorg.

After you can filter records based on   fksto = 'X'. 

if still didn't work out..then need to create a secondary index on the database table for the where condition fields.

And also ensure you are deleting duplicates when using FAE in the where condition fields.

Regards

Raj

[Former Member]

Hi Raj,

Currenty the biggest load is on retrieving from table VRKPA (as shown below), which is the first select query that will do FAE on internal table itab of 50k data.

Thanks

[Former Member]

Dear Fox Haund,

Please try the below logic :

TYPES : BEGIN OF ty_bill,
          vbeln TYPE vbrk-vbeln,
          fkart TYPE vbrk-fkart,
          vkorg TYPE vbrk-vkorg,
          fkdat TYPE vbrk-fkdat,
          vtweg TYPE vbrk-vtweg,
          fksto TYPE vbrk-fksto,
          sfakn TYPE vbrk-sfakn,
        END OF ty_bill.

TYPES : BEGIN OF ty_vrkpa,
          vkorg TYPE vrkpa-vkorg,
          fkart TYPE vrkpa-fkart,
          kunnr TYPE vrkpa-kunnr,
          vbeln TYPE vrkpa-vbeln,
        END OF ty_vrkpa.

DATA : it_bill_cancel TYPE STANDARD TABLE OF ty_bill,
       it_vrkpa       TYPE STANDARD TABLE OF ty_vrkpa.

SORT itab BY vbeln.
DELETE ADJACENT DUPLICATES FROM itab COMPARING vbeln.

IF itab[] IS NOT INITIAL.
  SELECT vbeln
          fkart
          vkorg
          fkdat
          vtweg
          fksto
          sfakn
    INTO TABLE it_bill_cancel
  FROM vbrk
    FOR ALL ENTRIES IN itab
  WHERE vbeln = itab-vbeln.

  DELETE it_bill_cancel WHERE fksto = 'X'.
  DELETE it_bill_cancel WHERE fkart NOT IN ('Z01','Z05').
  DELETE it_bill_cancel WHERE vkorg NOT IN s_vkorg.
ENDIF.

IF it_bill_cancel[] IS NOT INITIAL.
  SELECT vkorg
         fkart
         kunnr
         vbeln
    INTO TABLE it_vrkpa
  FROM vrkpa
    FOR ALL ENTRIES IN it_bill_cancel
  WHERE kunde IN r_kunde
    AND vkorg = it_bill_cancel-vkorg
    AND fkdat = it_bill_cancel-fkdat
    AND vtweg = it_bill_cancel-vtweg
    AND fkart = it_bill_cancel-fkart
    AND vbeln = it_bill_cancel-vbeln.
ENDIF.

Regards,

Yance

[jay_kumar8]

Then you can try like this...

First select

1. SELECT vkorg fkart kunnr vbeln 
2.           INTO TABLE it_bill FROM vrkpa 
3.           FOR ALL ENTRIES IN itab WHERE vkorg IN s_vkorg                                      
4.                                       AND  vbeln  = itab-vbeln. 

then remove entries from it_bill where kunde ne r_kunde

DELETE IT_BILL WHERE KUNDE NOT IN R_KUNDE.

and then filter entires where fkart  eq ('Z01','Z51') 

Regards

Raj

[Jelena_Perfiljeva]

I'm quite puzzled by the suggestions to remove conditions from WHERE, read clearly redundant data and then delete it. How did you arrive at such conclusion? Very much confused how this could be more efficient in any way in this scenario...

@OP - why do you even have VRKPA there? It's an index table that is used when we don't know the document number but know the partner #. It mostly exists to facilitate VF05 transaction and will be very much redundant in HANA. I'm not sure which partner you need, but check if you can simply use BSID-KUNNR or one of the customer numbers in VBRK. Or add VBPA (which has VBELN as primary key) if you need some other partner type. You already have VBRK primary key (VBELN) in the internal table, so there is no reason whatsoever to add VRKPA, from what I see.

Also see the replies in this thread, there were some good suggestions if you have a newer ABAP version:

[Former Member]

Hi Fox,

You need to act on multiple points to come out of performance problem.

1 - Remove Join condition - instead split your selection and start with key table first where you have most of the selection parameters.

2 - Before doing for all entry clause copy reference table to temporary table and short temporary table by where clause fields and delete duplicate entries. Then use temporary table in for all entries.

3 - At the end if data volume is very high process data in packages for high volume tables.

Thanks and regards,

Ripal Patel

[Former Member]

Hi All,

Thanks for all the suggestion and for Jelena for noticing.

Currently i have a new question regarding Loop - Collect, please help if possible

Thanks.