SQL or ABAP ?

[Former Member]

Hi all.

I have a question that perhaps you could answer.

The problem is the fallowing:

What is better (concerning run time ) for my program:

Grouping and summarizing at SQL level (sum() group by ) or in abap by collect?

I have to mention that sometimes the selections can have more then 1 million lines if i summarize at abap level and much less if is done in SQL because in internal table i already have the summarized data.

Thank you,

Cristian.

[Former Member]

Aggregate functions are always better than selecting and processing.

Albert

[Former Member]

So, in your opinion, is better to group data in SQL.

Thank you,

Cristian.

[Former Member]

This would be easy to test. Why don't you try it?

My money would be on ABAP..

Rob

[TuncayKaraca]

@Cristian,

You also have to consider buffer. If you use SQL aggregate functions, SQL statement will bypass buffer. It can cause performance problems. Check out <a href="http://help.sap.com/saphelp_46c/helpdata/en/cf/21f29f446011d189700000e8322d00/frameset.htm">Which Accesses Proceed Directly to the Database?</a>

But maybe your tables you use in SQL statement are not be buffered.

You may be do some tests for both approach and watch out with ST05.

[Former Member]

I already tried. My program is running on abap summarization now. But...for lets say 10.000 articles and 21 stores for 6 months, you say it will run faster if i select raw data from table and summarize in abap?

As far as i know, internal table can hold only 2 GB of data.

Ok, if ABAP is faster, where can i find information about optimizing my select and abap code? I am quite new in ABAP, I worked on a system that had reports created directly in sql.

Thank you.

P.S. Data is read from an infostructure, the data is already summarized at a store, day article level, but i have to create the report for selected periods.

[Former Member]

Rob, in fact, this program of mine, replaces KE30 on a primitive level. We have some space problems with our database (bad implementation) and we have to archive COPA tables. Still, we need information about margin, sales for the entire year, so this program lets say, simulates in a way some functions KE30 had (primitive anyway).

For a sale analysis and negotiation with vendors we need margin for articles on store and company level, etc...so i really have to find a way to optimize code so that it will run in a decent period of time.

I dont know how KE30 is running (COPA sumarization and stuff) but it runs much faster then my code, and i have to find a method to process the same information at 50-60 % run time of KE30.

[Former Member]

Well, the good news is that SAP transactions are notoriously slow, so you should be able to achieve this. Can you post the portion of the code you have developed that does this summarization?

Rob

[Former Member]

The code I will post is with ABAP summarization. I wanted to ask if SQL is better, before beginning to work on this solution.

Selection of RAW data from S520 infostructure.

<b>SELECT

matnr

werks

matkl

sptag

vkmng

umsgvo

umsgeo_01

FROM s520

INTO CORRESPONDING FIELDS OF TABLE it_selectie_date

WHERE matnr IN s_matnr AND werks IN s_werks AND sptag IN s_data.

LOOP AT it_selectie_date INTO wa_selectie_date.

wa_selectie_date-marja_valorica = wa_selectie_date-umsgvo - wa_selectie_date-umsgeo_01. " cifra de afaceri - cogs

MODIFY it_selectie_date FROM wa_selectie_date.

ENDLOOP.

</b>

Sumarization of information according to options on selection screen.

The fallowing code sumarizes data at interval selected but the other options have the same table as a starting point but sumarization depends. (month level, company, etc).

<b> LOOP AT it_selectie_date INTO wa_selectie_date.

wa_intermediar-matnr = wa_selectie_date-matnr.

wa_intermediar-werks = wa_selectie_date-werks.

wa_intermediar-matkl = wa_selectie_date-matkl.

wa_intermediar-vkmng = wa_selectie_date-vkmng.

wa_intermediar-umsgvo = wa_selectie_date-umsgvo.

wa_intermediar-marja_valorica = wa_selectie_date-marja_valorica.

COLLECT wa_intermediar INTO it_intermediar.

ENDLOOP.

LOOP AT it_intermediar INTO wa_intermediar.

IF wa_intermediar-umsgvo = 0.

wa_intermediar-marja_procent = 0.

ELSE.

wa_intermediar-marja_procent = wa_intermediar-marja_valorica / wa_intermediar-umsgvo * 100.

ENDIF.

MODIFY it_intermediar FROM wa_intermediar.

ENDLOOP.

LOOP AT it_intermediar INTO wa_intermediar.

READ TABLE it_mara WITH KEY matnr = wa_intermediar-matnr werks = wa_intermediar-werks INTO wa_mara.

IF sy-subrc NE 0.

wa_final-matnr = wa_mara-matnr.

wa_final-maktx = wa_mara-maktx.

wa_final-lifnr = wa_mara-lifnr.

wa_final-name1 = wa_mara-name1.

wa_final-werks = wa_mara-werks.

wa_final-matkl = wa_mara-matkl.

wa_final-clasa_material = wa_mara-matkl(1).

wa_final-luna = wa_intermediar-denumire_luna.

wa_final-anul = wa_intermediar-an.

wa_final-umsgvo = 0.

wa_final-vkmng = 0.

wa_final-marja_valorica = 0.

wa_final-marja_procent = 0.

APPEND wa_final TO it_final.

ELSE.

wa_final-matnr = wa_intermediar-matnr.

wa_final-luna = wa_intermediar-denumire_luna.

wa_final-anul = wa_intermediar-an.

wa_final-maktx = wa_mara-maktx.

wa_final-lifnr = wa_mara-lifnr.

wa_final-name1 = wa_mara-name1.

wa_final-werks = wa_intermediar-werks.

wa_final-matkl = wa_intermediar-matkl.

wa_final-umsgvo = wa_intermediar-umsgvo.

wa_final-vkmng = wa_intermediar-vkmng.

wa_final-clasa_material = wa_intermediar-matkl(1).

wa_final-marja_valorica = wa_intermediar-marja_valorica.

wa_final-marja_procent = wa_intermediar-marja_procent.

APPEND wa_final TO it_final.

ENDIF.

CLEAR wa_final.

ENDLOOP.

</b>

[Former Member]

Before you go any further check this:

SORT it_mara BY matnr werks.   "<====
LOOP AT it_intermediar INTO wa_intermediar.
  READ TABLE it_mara WITH KEY 
  matnr = wa_intermediar-matnr
  werks = wa_intermediar-werks
  INTO wa_mara
  BINARY SEARCH.       "<====

Unless this table is sorted or hashed, you should add the binary search addition to the read. Try this one change and get back to the forum if there are still problems.

Rob

[Former Member]

1) I do not have infostructure S520 in my system so I cannot comment on your select statement. Run an SQL trace and check if it picks appropriate indexes.

2) Avoid using INTO CORRESPONDING FIELDS OF TABLE in your select statement. Instead define the internal table containing only the fields in your field list and use the clause INTO TABLE

3) For your read on internal table it_mara, sort on matnr and werks and read with binary search as suggested by Rob or use hashed or sorted tables with index matnr and werks.

4) You have used one too many loops. I believe you can do what you intended to do using just 2 loops. Look at the sample code below

LOOP AT it_selectie_date INTO wa_selectie_date.
  wa_intermediar-matnr          = wa_selectie_date-matnr    .
  wa_intermediar-werks          = wa_selectie_date-werks    .
  wa_intermediar-matkl          = wa_selectie_date-matkl    .
  wa_intermediar-vkmng          = wa_selectie_date-vkmng    .
  wa_intermediar-umsgvo         = wa_selectie_date-umsgvo   .
  wa_intermediar-marja_valorica = wa_selectie_date-umsgvo -
                                  wa_selectie_date-umsgeo_01.
  COLLECT wa_intermediar INTO it_intermediar.
ENDLOOP.


LOOP AT it_intermediar INTO wa_intermediar.

  IF wa_intermediar-umsgvo = 0.
    wa_intermediar-marja_procent = 0.
  ELSE.
    wa_intermediar-marja_procent = wa_intermediar-marja_valorica /
    wa_intermediar-umsgvo * 100.
  ENDIF.
  
  READ TABLE it_mara WITH KEY matnr = wa_intermediar-matnr 
                                                    werks = wa_intermediar-werks 
                                                    INTO wa_mara.
  IF sy-subrc NE 0.
    wa_final-matnr = wa_mara-matnr.
    wa_final-maktx = wa_mara-maktx.
    wa_final-lifnr = wa_mara-lifnr.
    wa_final-name1 = wa_mara-name1.
    wa_final-werks = wa_mara-werks.
    wa_final-matkl = wa_mara-matkl.
    wa_final-clasa_material = wa_mara-matkl(1).
    wa_final-luna = wa_intermediar-denumire_luna.
    wa_final-anul = wa_intermediar-an.
    wa_final-umsgvo = 0.
    wa_final-vkmng = 0.
    wa_final-marja_valorica = 0.
    wa_final-marja_procent = 0.
    APPEND wa_final TO it_final.
  ELSE.
    wa_final-matnr = wa_intermediar-matnr.
    wa_final-luna = wa_intermediar-denumire_luna.
    wa_final-anul = wa_intermediar-an.
    wa_final-maktx = wa_mara-maktx.
    wa_final-lifnr = wa_mara-lifnr.
    wa_final-name1 = wa_mara-name1.
    wa_final-werks = wa_intermediar-werks.
    wa_final-matkl = wa_intermediar-matkl.
    wa_final-umsgvo = wa_intermediar-umsgvo.
    wa_final-vkmng = wa_intermediar-vkmng.
    wa_final-clasa_material = wa_intermediar-matkl(1).
    wa_final-marja_valorica = wa_intermediar-marja_valorica.
    wa_final-marja_procent = wa_intermediar-marja_procent.
    APPEND wa_final TO it_final.
  ENDIF.
  CLEAR wa_final.
ENDLOOP.

Please let me know if this helps.

[Former Member]

Rob, I will try it. But dont you think that the problem is at the select level? If I run the select for 20 stores, average 7000 articles for 6 months or more, the internal table is huge. Ok, sorry if I look stubborn, I am not, I really appreciate advices, I am just asking because I am a beginner in ABAP.

The fact is that when i run the program, and i have a look at SM04, i use 15 GB of memory.

[Former Member]

ABAP problems can slow a program down much more than a badly constructed SELECT. Have a look at:

<a href="/people/rob.burbank/blog/2006/02/07/performance-of-nested-loops">The Performance of Nested Loops</a>

Rob

[ferry_lianto]

Hi,

Please have a look transaction MC9C perhaps it may help.

Enter your info structure S520 and version then enter ...

Regards,

Ferry Lianto

[Former Member]

Thank you all for the advices.

I did some optimizing for one of the select statements with a little bit of help and now the improvement is extraordinary.

I created an index for vbrk table (document type selection and date) and it helped.

@Mark, i will modify my code tomorrow and i will let you know.

Thank you all,

Cristian.

[Former Member]

The code you posted had no SELECT against VBRK. There are downsides to creating indexes on tables that are heavily updated by online transactions, so that would be the last solution I would be looking for. It's much better to look for an ABAP solution. could you post the VBRK SELECT?

Rob

[Former Member]

Yes Rob.

SELECT

vbeln

fkdat

fkart

FROM vbrk

INTO CORRESPONDING FIELDS OF TABLE it_vbrk_z

WHERE fkart IN ('ZRET','ZRE2','S1') AND fkdat IN s_data.

LOOP AT it_vbrk_z into wa_vbrk_z.

move wa_vbrk_z-fkdat to wa_vbrp_z-fkdat.

move wa_vbrk_z-fkart to wa_vbrp_z-fkart.

SELECT

matnr

matkl

werks

SUM( fkimg ) AS fkimg

SUM( netwr ) AS netwr

SUM( wavwr ) AS wavwr

INTO CORRESPONDING FIELDS OF wa_vbrp_z

FROM VBRP

WHERE vbeln EQ wa_vbrk_z-vbeln

GROUP BY matnr matkl werks.

IF wa_vbrp_z-werks IN s_werks AND wa_vbrp_z-matnr IN s_matnr.

APPEND wa_vbrp_z TO it_vbrp_z.

ENDIF.

ENDSELECT.

ENDLOOP.

The index is for FKDAT FKART

[Former Member]

Try using this code instead on the one you have. It should work faster.

TYPES: BEGIN OF ty_vbrk,
         vbeln TYPE vbrk-vbeln,
         fkdat TYPE vbrk-fkdat,
         fkart TYPE vbrk-fkart,
       END OF ty_vbrk,

       BEGIN OF ty_vbrp,
         matnr TYPE vbrp-matnr,
         matkl TYPE vbrp-matkl,
         werks TYPE vbrp-werks,
         vbeln TYPE vbrp-vbeln,
         posnr TYPE vbrp-posnr,
         fkimg TYPE vbrp-fkimg,
         netwr TYPE vbrp-netwr,
         wavwr TYPE vbrp-wavwr,
         fkdat TYPE vbrk-fkdat,
         fkart TYPE vbrk-fkart,
       END OF ty_vbrp.

DATA: w_vbrk    TYPE                 ty_vbrk,
      w_vbrp    TYPE                 ty_vbrp,
      w_vbrp_z  TYPE                 ty_vbrp,

      it_vbrk_z TYPE HASHED TABLE OF ty_vbrk
        WITH UNIQUE KEY vbeln               ,
      it_vbrp   TYPE        TABLE OF ty_vbrp,
      it_vbrp_z TYPE        TABLE OF ty_vbrp.

SELECT vbeln
       fkdat
       fkart
  FROM vbrk
  INTO TABLE it_vbrk_z
  WHERE fkart IN ('ZRET','ZRE2','S1')
  AND   fkdat IN s_data.

IF sy-subrc EQ 0.

  SELECT matnr
         matkl
         werks
         vbeln
         posnr
         fkimg
         netwr
         wavwr
    FROM vbrp
    INTO TABLE it_vbrp
    FOR ALL ENTRIES IN it_vbrk_z
    WHERE vbeln EQ it_vbrk_z-vbeln.

  IF sy-subrc EQ 0.
    SORT it_vbrp BY matnr matkl werks.
  ENDIF.

ENDIF.


REFRESH it_vbrp_z.

LOOP AT it_vbrp INTO w_vbrp.

  w_vbrp_z = w_vbrp.

  READ TABLE it_vbrk_z INTO w_vbrk WITH KEY vbeln = w_vbrp-vbeln
                                            TRANSPORTING
                                              fkdat
                                              fkart.
  IF sy-subrc EQ 0.
    w_vbrp_z-fkdat = w_vbrk-fkdat.
    w_vbrp_z-fkart = w_vbrk-fkart.
  ENDIF.

  AT END OF werks.
    SUM.
    w_vbrp_z-fkimg = w_vbrp-fkimg.
    w_vbrp_z-netwr = w_vbrp-netwr.
    w_vbrp_z-wavwr = w_vbrp-wavwr.
    APPEND w_vbrp_z TO it_vbrp_z.
    CLEAR  w_vbrp_z.
  ENDAT.

ENDLOOP.

[Former Member]

... mistake, ignore this.

Message was edited by:

Cristian Boartes

[Former Member]

for millions of records, you should always do it in the database.

It does not make sense to move that amount of data to the application server. And the performance on the appication server will anyway be slower (if program loop

and read without binary search then it willl be horrible)

For smaller amounts it is recommended to accept overhead on one of the application servers to move load away from the one central database.

You should carefully check what is the usual case, if the 1 million is the one time in a year exception, then you should not optimize all processes for this exception.

Siegfried