into corresponding fields of table VERSUS into table

[Former Member]

Hi,

I want the select statement outside the loop can any one write the performance to increase and select single statmenet should be outside the loop.

IF i_komfk-vbtyp CA 'PO'.              " debit/credit memo
    SELECT vbeln INTO TABLE ivbfa FROM vbfa
           WHERE vbelv = i_komfk-vbeln AND
                 ( vbtyp_n = '5' OR vbtyp_n = '6' ).



    LOOP AT ivbfa INTO ivbfa_wa.
      CLEAR: fksto_lv,sfakn_lv.


    SELECT SINGLE fkart fksto sfakn INTO (fkart_lv, fksto_lv, sfakn_lv)
           FROM vbrk
           WHERE vbeln = ivbfa_wa-vbeln.

      CHECK: sy-subrc = 0,
             fksto_lv IS INITIAL,
             sfakn_lv IS INITIAL.
      MESSAGE e310(zz) WITH fkart_lv ivbfa_wa-vbeln.
    ENDLOOP.
  ENDIF.

Moderator message: next time please use code tags and post in the correct forum. Also use more descriptive subject lines.

Edited by: Thomas Zloch on Jun 17, 2010 2:45 PM

[herzelhaimharel_gilor]

DATA : lt_vbrk TYPE TABLE OF vbrk .

FIELD-SYMBOLS : <lfs_vbrk> TYPE vbrk .

SELECT vbrk_{fkart vbrk}fksto vbrk~sfakn INTO CORRESPONDING FIELDS OF TABLE lt_vbrk

FROM vbrk AS vbrk

INNER JOIN vbeln AS vbeln ON vbeln_{vbeln = vbrk}vbeln

WHERE vbeln~vbelv = i_komfk-vbeln

AND ( vbeln_{vbtyp_n = '5' OR vbeln}vbtyp_n = '6' ).

LOOP AT lt_vbrk ASSIGNING <lfs_vbrk> WHERE fksto IS INITIAL AND sfakn IS INITIAL.

MESSAGE e310(zz) WITH <lfs_vbrk>-fkart ivbfa_wa-vbeln.

ENDLOOP .

[former_member194613]

Avoid the *corresponding fiedls of *.

why, this is just repeated and repeated but never measured!

Into CORRESPONDING is perfectly o.k. for SELECT statement which go to the database, only for buffers it is

an overhead, because the buffer is more faster!

Here is no buffered table involved !

[Former Member]

>

> Avoid the *corresponding fiedls of *.
> 
> why, this is just repeated and repeated but never measured!

Not entirely true. See:[Performance - what will kill you and what will leave you with only a flesh wound|/people/rob.burbank/blog/2006/11/16/performance--what-will-kill-you-and-what-will-leave-you-with-only-a-flesh-wound] (MOVE-CORRESPONDING rather than INTO CORRESPONDING FIELDS OF).

Rob

[former_member194613]

Overall, there is no only a performance question but also a functional problem.

What are the messages good for? Is the whole thing just a check? The values of the SELECT SINGLE are not used.

Please explain your logic, I have doubts that it is o.k.

[Former Member]

Hi,

Please check the logic again. For getting Debit/Credit Memo you can use FKART field of VBRK. This will avoid your looping. Still if you want to use, than go for 'select all entries'.

Regards,

Saud

[former_member194613]

> MOVE-CORRESPONDING rather than INTO CORRESPONDING FIELDS OF).

sounds similar, but is it really the same?

[Former Member]

Hi Siegfried - does the kernel handle INTO CORRESPONDING differently than MOVE-CORRESPONDING. I was assuming (perhaps incorrectly) that both would be done the same way.

I agree that it would interesting to compare the two, but I think it would be hard to come up with a valid test. How to separate the database portion of the SELECT from the INTO CORRESPONDING portion?

As an aside, when I took the basic ABAP course years ago, the instructor was very insistent that MOVE-CORRESPONDING (or was it INTO CORRESPONDING?) was inefficient and should be avoided. I disagreed with him then, but more from an ease of programming standpoint rather than performance.

Rob

[former_member194613]

Hello Rob,

maybe you should update your program and test the difference between INTO CORRESPONDING and MOVE-CORRESPONDING, there is one!

Together with a SELECT statement going to the DB I can not see with a INTO CORRESPONDING should not be used.

It should be used if it makes sense:

+ if the fields are needed in a different order (cheap)

+ if the not all fields are needed and INTO does not work because gaps would appear. Then it is even better to use INTO CORRESPONDING than INTO.

Siegfried

[Former Member]

answered

[former_member194613]

yes, there is a difference

you can use INTO CORRESPONDING in this way

SELECT *
              INTO CORRESPONDING FIELDS ls_small_1
              FROM dbtab1
              WHERE ...
              APPEND ls_small_1 TO lt_small_1.
ENDSELECT.

SELECT *
              INTO ls_1
              FROM dbtab1
              WHERE ...

              MOVE-CORRESPONDING ls_1 TO ls_small_1.
              APPEND ls_small_1 TO lt_small_1.
ENDSELECT.

ls_small_1 does contain all fields of the table, that is the purpose of INTO CORRESPONDING

The width is reduced then you will find a considerable advantage for the first version.

The first one can also be used with INTO TABLE, even another bit faster.

There have been optimizations over the releases, and unfortunately I do not know, what was optimized when.

Siegfried

[former_member192616]

Hi,

>

> ls_small_1 does contain all fields of the table, that is the purpose of INTO CORRESPONDING
> The width is reduced then you will find a considerable advantage for the first version.
> Siegfried

should that be: "ls_small_1 does not contain all fields of the table..." ? Otherwise it might

confuse...

Kind regards,

Hermann

[former_member194613]

Herrman,

thanks, yes of course ... I hope the small in the name implied that.

[former_member192616]

Hi Siegfried,

ok, just to make sure that nobody is confused

One more question regarding Rob's point.

I can remember as well that it was taught in BC490 that "SELECT ...INTO CORRESPONDING FIELDS OF" is bad

for performance (and this was not in the context of the table buffer but for plain open

sql for the database). I heard this in several trainings from several people.

Do you happen to know why it used to be taught like this? (I'm not sure what is taught today anyway)

Kind regards,

Hermann

[former_member182566]

>

> One more question regarding Rob's point.
>
> I can remember as well that it was taught in BC490 that "SELECT ...INTO CORRESPONDING FIELDS OF" is bad for performance.
>
> Do you happen to know why it used to be taught like this? (I'm not sure what is taught today anyway)

I think the comparison is between

INTO CORRESPONDING FIELDS OF

and

INTO (f1, ..., fn)

and the argument is that in the first case the system will have to determine which field goes where.

[Former Member]

I can remember as well that it was taught in BC490 that "SELECT ...INTO CORRESPONDING FIELDS OF" is bad for performance

Here's the corresponding quote from the SAP help from [minimize the amount of data transferred|http://help.sap.com/saphelp_nw70ehp2/helpdata/en/41/7af4cba79e11d1950f0000e82de14a/frameset.htm] supporting Rui's answer:

Note here that the INTO CORRESPONDING FIELDS addition in the INTO clause is only efficient with large volumes of data, otherwise the runtime required to compare the names is too great. For small amounts of data, use a list of variables in the INTO clause.

I would suspect that the column name comparison is done once per invocation of the statement. It makes sense that there's some overhead involved, though I'd suspect this should be in most cases insignificant compared to the query time...

[former_member192616]

Hi Rui,

HI Harald,

yes. But in the training (many, many years ago) it was pointed out as really bad and to be avoided whenever

and wherever possible... that's why i ask for the reasons for such statements. Maybe in very old releases or so.

During lunch accidently met one of the BC490 trainers and he said that today it is not mentioned as bad

anymore... more or less what you quote from the documentation is taught today.

Kind regards,

Hermann

[Sandra_Rossi]

Hi Harald, your link refers to update techniques. I guess the correct url is: [Minimize the Amount of Data Transferred|http://help.sap.com/saphelp_nw70ehp2/helpdata/en/aa/4734970f1c11d295380000e8353423/frameset.htm]

Just to confirm what you say, I made this little test program (hope it's correct!) in 7.0 sp 13 kernel sp 236. SFLIGHT data is generated as indicated in [SDN wiki - Flight Data Application - Demo Example for Integration Technologies|http://wiki.sdn.sap.com/wiki/display/ABAP/FlightDataApplication-DemoExampleforIntegrationTechnologies]):

  DATA: i1 TYPE i, i2 TYPE i.
  TYPES : BEGIN OF ty_ls_db,
            seatsocc_b  TYPE sflight-seatsocc_b,
            planetype   TYPE sflight-planetype,
            connid      TYPE sflight-connid,
          END OF ty_ls_db.
  DATA lt_sflight2 TYPE TABLE OF ty_ls_db.
  DATA lt_sflight TYPE TABLE OF sflight.
  DATA l_testnum TYPE syindex.
  DATA l_testocc TYPE syindex.
  DO 5 TIMES.
    ULINE.
    write : / 'Run', sy-index.
  DO 3 TIMES.
    l_testocc = 20 * ( 2 ** sy-index ).
    DO 2 TIMES.
      l_testnum = sy-index.
      DO l_testocc TIMES. "40, 80 or 160 times
        IF sy-index = 2.
          GET RUN TIME FIELD i1.
        ENDIF.
        CASE l_testnum.
          WHEN 1.
            SELECT seatsocc_b planetype connid
                  INTO CORRESPONDING FIELDS OF TABLE lt_sflight
                  FROM sflight.
          WHEN 2.
            SELECT seatsocc_b planetype connid
                  INTO TABLE lt_sflight2
                  FROM sflight.
        ENDCASE.
      ENDDO.
      GET RUN TIME FIELD i2.
      DATA: t1 TYPE i, t2 TYPE i.
      CASE l_testnum.
        WHEN 1.
        t1 = i2 - i1.
      WHEN 2.
        t2 = i2 - i1.
      ENDCASE.
    ENDDO.
    i2 = t2 * 1000 / t1.
    WRITE : / l_testocc, 'occurrences ---> INTO CORRESPONDING...:', t1, '/ INTO TABLE:', t2, '/ Ratio:', i2.
  ENDDO.
  ENDDO.

Both open sql statements generate the same SQL statement:

SELECT
  "SEATSOCC_B" , "PLANETYPE" , "CONNID"
FROM
  "SFLIGHT"
WHERE
  "MANDT" = :A0

There is no evidence of better SQL:

Run          1
        40  occurrences ---> INTO CORRESPONDING...:     65.389  / INTO TABLE:     71.056  / Ratio:      1.087
        80  occurrences ---> INTO CORRESPONDING...:    133.819  / INTO TABLE:    155.400  / Ratio:      1.161
       160  occurrences ---> INTO CORRESPONDING...:    302.518  / INTO TABLE:    320.691  / Ratio:      1.060
---------------------------------------------------------------------------------------------------
Run          2
        40  occurrences ---> INTO CORRESPONDING...:     73.588  / INTO TABLE:     67.510  / Ratio:        917
        80  occurrences ---> INTO CORRESPONDING...:    167.849  / INTO TABLE:    156.958  / Ratio:        935
       160  occurrences ---> INTO CORRESPONDING...:    313.378  / INTO TABLE:    320.100  / Ratio:      1.021
---------------------------------------------------------------------------------------------------
Run          3
        40  occurrences ---> INTO CORRESPONDING...:     73.699  / INTO TABLE:     77.650  / Ratio:      1.054
        80  occurrences ---> INTO CORRESPONDING...:    161.512  / INTO TABLE:    159.408  / Ratio:        987
       160  occurrences ---> INTO CORRESPONDING...:    313.383  / INTO TABLE:    329.411  / Ratio:      1.051
---------------------------------------------------------------------------------------------------
Run          4
        40  occurrences ---> INTO CORRESPONDING...:     79.260  / INTO TABLE:     78.553  / Ratio:        991
        80  occurrences ---> INTO CORRESPONDING...:    156.768  / INTO TABLE:    160.575  / Ratio:      1.024
       160  occurrences ---> INTO CORRESPONDING...:    303.171  / INTO TABLE:    309.742  / Ratio:      1.022
---------------------------------------------------------------------------------------------------
Run          5
        40  occurrences ---> INTO CORRESPONDING...:     76.289  / INTO TABLE:     87.361  / Ratio:      1.145
        80  occurrences ---> INTO CORRESPONDING...:    141.419  / INTO TABLE:    135.221  / Ratio:        956
       160  occurrences ---> INTO CORRESPONDING...:    308.372  / INTO TABLE:    322.496  / Ratio:      1.046

[former_member192616]

Hi Sandra,

thanks for your effort!

>

> There is no evidence of better SQL

... in your example with your volumes.

If you have the chance, vary the resut set sizes (small result sets, medium result sets and big result sets).

The corresponding might become relatively more expensive with the small result sets...

Kind regards,

Hermann

[Former Member]

>

> I can remember as well that it was taught in BC490 that "SELECT ...INTO CORRESPONDING FIELDS OF" is bad
> for performance (and this was not in the context of the table buffer but for plain open
> sql for the database).

OK Hermann, you are testing the limits of my memory. I do not recall if it was in terms of MOVE-CORRESPONDING or INTO CORRESPONDING FIELDS OF, but the logic was something like:

Suppose you have two structures

begin of tab1,
  f1,
  f2,
  f3,
  f4,
end of tab1.

and

begin of tab2,
  f1,
  f2,
  f3,
  f4,
end of tab2.

if you assign fields from tab1 to tab2 using CORRESPONDING, the kernel will look at tab1-f1 and then look at the fields of tab2, starting at f1 in sequence until it finds a match. Then it looks at tab1-f2 and looks at all the filelds in itab starting again with f1. It does this until it has gone through all of the fieds in tab1.

This was from the basic ABAP course, not the performance and tuning one. The instructor is intelligent and knew his stuff.

Rob

[Former Member]

your link refers to update techniques. I guess the correct url is: [Minimize the Amount of Data Transferred|http://help.sap.com/saphelp_nw70ehp2/helpdata/en/aa/4734970f1c11d295380000e8353423/frameset.htm]

Yes, thanks a lot, that's exactly what I was trying to reference. I hope that most of the times I get it right, but sometimes I create bogus links because I navigate within the help and then copy the start URL that's still in the address bar of the browser. Stupid me...

As far as your measurement results are concerned I agree with Hermann. A different example might produce different results. From a theoretical perspective I'd expect inconclusive results for most tests, simply because the difference is most likely very small (compared to the other times needed). Furthermore it is my understanding that you're measuring run time (and not CPU time) and thus the measurement depends on the system load. I'm too lazy to check the documentation, but as far as I remember the same holds true for SE30: At least in the olden days the recommendation was always to repeat the measurements and take system load into consideration. Please correct me if that's no longer up-to-date...

Here's what I think would be a good test example:

<ul style="list-style:square!important">

<li>the table is buffered (so very fast reads)</li>

<li>the result set if small, but the query is executed often (as my understanding is that the the overhead of figuring out which fields to put where occurs once per invocation)</li>

<li>The query is a select * on a table with lots of fields and the target structure contains all those table fields, but "hidden" among tons of other fields (and obviously variations where the relevant fields are positioned would be good to find out if field names are retrieved by sequential matching or if there's some optimized access to determine the right fields)</li>

</ul>

I'm too lazy to test this though, because in my experience it's really tough to obtain results/proof for minute time differences (and even if the results seem to look good the actual error interval doesn't allow one to come to any conclusions). So I was hoping more or less that somebody with insight into what is actually happening underneath could comment...

Cheers, harald

[former_member194613]

the measurements by Sandra are exactly right, that is the usual behavior, the INTO CORRESPONDING creates a small overhead (anyway no problem) and the variations of the SELECT from DB are higher.

This holds for all releases going down to 4.0 (I have no access to older systems anymore).

Recommendations in BC490 ... I do not know, I know only our SAP internal classes, and there the statement was 'Do not use INTO CORRESPONDING with buffered tables.

Ok, the buffered accesses are much faster and it does not make much sense to use fieldlists at all.

Siegfried

[achim_stroh]

Hello Siegfried,

you are right, <select * into corresponding fields of table> might bypass table buffer - at least sometimes you can see in st05 trace that the internal select statement adds an "order by", for example until the buffer is filled. (My following test is based on SAP_ABA 700 0020 with ORACLE 11.2.0.2.0):

(ztable is marked to be a fully buffered client dependent customizing-table that contains 10 entries in 4 columns (2 key-fields), lt_ztable has same structure or is smaller - in my case without client-field)

select * from ztable into corresponding fields of table lt_ztable.

results in st05 trace: (first number is the duration - so the "most expensive" select-part on database takes 573 milliseconds)
first time:

59 ZTABLE               OPEN            1024 R 6 400

2 ZTABLE               LD OPEN     0      0 R 6 400

64 ZTABLE               PREPARE            0 SELECT WHERE "MANDT"=:A0 ORDER BY "MANDT", "PROCESS_TYPE", "ITEM_TYPE"

2 ZTABLE               OPEN               0 SELECT WHERE "MANDT"='400' ORDER BY "MANDT", "PROCESS_TYPE", "ITEM_TYPE"

573 ZTABLE               FETCH      11   1403

8 ZTABLE               LD END      0      0 R 0

2 ZTABLE               OPEN               0 R 6 400

11 ZTABLE               FETCH       0     64
1 ZTABLE               CLOSE       0      0

but then the buffer is filled and the next test-trace results in following result - all in buffer - so compared to first call very very fast:

4 ZTABLE               OPEN               0 R 6 400

6 ZTABLE               FETCH       0     64

0 ZTABLE               CLOSE       0      0

So to make the first select faster, you could avoid the <*> and also avoid <corresponding fields of>, e.g. something like this:

select PROCESS_TYPE ITEM_TYPE [...] from ztable into table lt_ztable.

trace result (till buffer is filled): as you can see the internal select statement is now smaller and without "order by"

4 ZTABLE               OPEN             256 R 6 400

81 ZTABLE               PREPARE            0 SELECT WHERE "MANDT"=:A0

2 ZTABLE               OPEN               0 SELECT WHERE "MANDT"='400'

453 ZTABLE               FETCH      11   1403

Kind regards,

Achim