SQL

[Former Member]

SELECT vbeln posnr etenr edatu wadat FROM vbep INTO CORRESPONDING FIELDS OF TABLE it_vbep FOR ALL ENTRIES IN it_output

WHERE vbeln = it_output-vbeln

AND posnr = it_output-posnr

AND etenr = ( SELECT MAX( etenr ) FROM vbep WHERE vbeln = it_output-vbeln AND posnr = it_output-posnr ).

SORT it_vbep BY vbeln posnr etenr DESCENDING.

LOOP AT it_output INTO wa_output.

READ TABLE it_vbep WITH KEY vbeln = wa_output-vbeln

posnr = wa_output-posnr.

IF sy-subrc EQ 0.

wa_output-zedatu = it_vbep-edatu.

wa_output-zwadat = it_vbep-wadat.

ENDIF.

Modify it_output from wa_output.

Endloop.

Option B :

SELECT vbeln posnr etenr edatu wadat FROM vbep INTO CORRESPONDING FIELDS OF TABLE it_output FOR ALL ENTRIES IN it_output

WHERE vbeln = it_output-vbeln

AND posnr = it_output-posnr

AND etenr = ( SELECT MAX( etenr ) FROM vbep WHERE vbeln = it_output-vbeln AND posnr = it_output-posnr ).

May I know is there any different between these 2 options?

How I can I improve the performance based on option A? I notice the usage of read .. binary search. May I know how I can use it? Will that improve the performance greatly?

Thanks

[former_member194613]

SORT it_vbep BY vbeln posnr etenr DESCENDING.

LOOP AT it_output INTO wa_output.

READ TABLE it_vbep WITH KEY vbeln = wa_output-vbeln

posnr = wa_output-posnr.

Why sort descending??? You need ascendung sort-order because the read must have a binary search and that is inccorrect if the SORT is incorrect.

The binary search will make option A much faster.

Siegfried

[Former Member]

> Why sort descending??? You need ascendung sort-order

> because the read must have a binary search and that

> is inccorrect if the SORT is incorrect.

Hi Siegfried Boes,

Table VBEP has 3 primary key fields VBELN, POSNR and ETENR. For some reason he wants the last schedule line for every Sales order item.

The sort statement

SORT it_vbep BY vbeln posnr etenr DESCENDING.

is as good as saying

SORT it_vbep BY vbeln ASCENDING

posnr ASCENDING

etenr DESCENDING.

He now has the last schedule line for all the selected Sales Orders. His read statement searches on VBELN and POSNR only. Both these fields are sorted in an ascending order and hence it will not harm the READ with BINARY search.

As to the question of why he needs the last schedule line for every sales order item, he is the best person to answer that.

[romanweise]

hmm, quite potential for performace here:

- sub selects are eval -> bypassing database buffers

- aggregating is eval -> bypassing database buffers

- using more than 1 field of the FOR ALL ENTRIES IN Table in the where clause is evil-> bypassing database buffers ;o)

there is no discussion if the internal table is sorted ascending or descending. why sorting at all? - if you do not get the select better, at least put the database result in a hash table and read the table with table key istead of with key - this is an constant access better then everthing else.

[Former Member]

Hi Hui Leng Yeoh,

1) Nested selects are not very efficient from a performance perspective. Since both your options involve nested selects, I would not recommend either.

2) I would not recommend INTO CORRESPONDING FIELDS OF TABLE too.

I have tried to replicate your logic and improve the performance. Please have a look.

TYPES: BEGIN OF ty_vbep,
         vbeln TYPE vbep-vbeln,
         posnr TYPE vbep-posnr,
         etenr TYPE vbep-etenr,
         edatu TYPE vbep-edatu,
         wadat TYPE vbep-wadat,
       END OF ty_vbep.

DATA: wa_index      TYPE                 sy-tabix ,
      wa_vbep       TYPE                 ty_vbep  ,
      it_vbep       TYPE        TABLE OF ty_vbep  ,
      it_vbep_h     TYPE HASHED TABLE OF ty_vbep
        WITH UNIQUE KEY vbeln posnr               ,
      wa_output     LIKE                 it_output,
      it_output_tmp LIKE        TABLE OF it_output.


REFRESH it_vbep_h.

IF NOT it_output[] IS INITIAL.

  it_output_tmp[] = it_output[].

  SORT it_output_tmp BY vbeln posnr.

  DELETE ADJACENT DUPLICATES FROM it_output_tmp COMPARING vbeln posnr.

  SELECT vbeln
         posnr
         etenr
         edatu
         wadat
    FROM vbep
    INTO TABLE it_vbep
    FOR ALL ENTRIES IN it_output_tmp
    WHERE vbeln EQ it_output_tmp-vbeln
    AND   posnr EQ it_output_tmp-posnr.

  IF sy-subrc EQ 0.

    SORT it_vbep BY vbeln
                    posnr
                    etenr DESCENDING.

    DELETE ADJACENT DUPLICATES FROM it_vbep COMPARING vbeln posnr.

    INSERT LINES OF it_vbep INTO TABLE it_vbep_h.

    REFRESH it_vbep.

  ENDIF.

ENDIF.


IF NOT it_vbep_h[] IS INITIAL.

  LOOP AT it_output INTO wa_output.

    wa_index = sy-tabix.

    READ TABLE it_vbep_h INTO wa_vbep WITH KEY vbeln = wa_output-vbeln
                                               posnr = wa_output-posnr
                                               TRANSPORTING
                                                 edatu
                                                 wadat.

    IF sy-subrc EQ 0.

      wa_output-zedatu = wa_vbep-edatu.

      wa_output-zwadat = wa_vbep-wadat.

      MODIFY it_output FROM wa_output INDEX wa_index
        TRANSPORTING
          zedatu
          zwadat.

    ENDIF.

  ENDLOOP.

ENDIF.

[former_member194613]

@Mark,

your solution does also not provide the somewhat weird

SORT it_vbep BY vbeln posnr etenr DESCENDING.

Generally I would be careful with decending order together with 'binary search'.

The binary search does the binary access according to the key field which MUST be or ascending order.

Additionally, it goes to the first line with this key, if there are several lines with identical key. The sort order of next field after the key is probably irrelevant, but I do not really know.

Your hashed table solution is a bit of over the top:

The hashed tables have only an advantage against sorted tables or binary search, if the table is very very large.

see here:

Measurements on internal tables which explain which reads should be taken can be found here:

/people/siegfried.boes/blog/2007/09/12/runtimes-of-reads-and-loops-on-internal-tables

Therefore it will be very difficult to get back the overheadm which you create with your intermediate table and the delete adjacent duplicates.

In general, a loop with read binary search is fast enough to be no performance problem anymore. Usually there are then other bottlenecks in a whole application.

Siegfried