Skip to Content
author's profile photo Former Member
Former Member

SQL or ABAP ?

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.

Add a comment
10|10000 characters needed characters exceeded

Related questions

12 Answers

  • author's profile photo Former Member
    Former Member
    Posted on Nov 20, 2007 at 06:25 PM

    Aggregate functions are always better than selecting and processing.

    Albert

    Add a comment
    10|10000 characters needed characters exceeded

  • author's profile photo Former Member
    Former Member
    Posted on Nov 20, 2007 at 06:28 PM

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

    Thank you,

    Cristian.

    Add a comment
    10|10000 characters needed characters exceeded

  • author's profile photo Former Member
    Former Member
    Posted on Nov 20, 2007 at 06:49 PM

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

    My money would be on ABAP..

    Rob

    Add a comment
    10|10000 characters needed characters exceeded

  • Posted on Nov 20, 2007 at 06:51 PM

    @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 Which" target="_blank">http://help.sap.com/saphelp_46c/helpdata/en/cf/21f29f446011d189700000e8322d00/frameset.htm">Which Accesses Proceed Directly to the Database?

    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.

    Add a comment
    10|10000 characters needed characters exceeded

  • author's profile photo Former Member
    Former Member
    Posted on Nov 20, 2007 at 06:56 PM

    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.

    Add a comment
    10|10000 characters needed characters exceeded

  • author's profile photo Former Member
    Former Member
    Posted on Nov 20, 2007 at 07:02 PM

    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.

    Add a comment
    10|10000 characters needed characters exceeded

    • 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

  • author's profile photo Former Member
    Former Member
    Posted on Nov 20, 2007 at 07:17 PM

    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>

    Add a comment
    10|10000 characters needed characters exceeded

    • 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.

  • author's profile photo Former Member
    Former Member
    Posted on Nov 20, 2007 at 07:28 PM

    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.

    Add a comment
    10|10000 characters needed characters exceeded

    • 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

  • Posted on Nov 20, 2007 at 07:47 PM

    Hi,

    Please have a look transaction MC9C perhaps it may help.

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

    Regards,

    Ferry Lianto

    Add a comment
    10|10000 characters needed characters exceeded

  • author's profile photo Former Member
    Former Member
    Posted on Nov 20, 2007 at 11:26 PM

    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.

    Add a comment
    10|10000 characters needed characters exceeded

    • Former Member 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.

Before answering

You should only submit an answer when you are proposing a solution to the poster's problem. If you want the poster to clarify the question or provide more information, please leave a comment instead, requesting additional details. When answering, please include specifics, such as step-by-step instructions, context for the solution, and links to useful resources. Also, please make sure that you answer complies with our Rules of Engagement.
You must be Logged in to submit an answer.

Up to 10 attachments (including images) can be used with a maximum of 1.0 MB each and 10.5 MB total.