CODEWITHSUNDEEP
abap
ekekakos
ekekakos

Reputation: 611

Which has better performance: SELECT...ENDSELECT (1 by 1) or SELECT...INTO TABLE / LOOP AT

I have to read 10.000.000 records from a table.

Is it better:

Upvotes: 3

Views: 10364

Answers (2)

András
András

Reputation: 1390

They have about the same speed

Contrary to popular belief, SELECT ... ENDSELECT does not fetch the rows one-by-one, so its performance is not much worse than SELECT ... INTO TABLE. See the explanation here.

The big problem with SELECT ... ENDSELECT is that it prevents other performance improvements

Consider this coding:

SELECT matnr FROM mara
    INTO lv_matnr WHERE (...).
  SELECT SINGLE ebeln
      FROM ekpo 
      INTO lv_ebeln 
      WHERE matnr = lv_matnr.
  SELECT SINGLE zfield
      FROM ztable 
      INTO lv_zfield 
      WHERE matnr = lv_matnr.
  ...
ENDSELECT.

Most of the time will be spent with the SELECT SINGLEs on table ekpo and ztable, and often the solution for this is using FOR ALL ENTRIES1, however you need an internal table for that.
So it has to be converted into a SELECT ... INTO TABLE anyway:

SELECT matnr FROM mara
    INTO TABLE lt_matnr WHERE (...).
IF lt_mara IS NOT INITIAL.
  SELECT matnr, ebeln FROM ekpo
      INTO TABLE @lt_ekpo          "make it SORTED by matnr"
      FOR ALL ENTRIES IN @lt_matnr
      WHERE matnr = @table_line.
  SELECT matnr, zfield FROM ztable
      INTO TABLE @lt_ztable        "make it SORTED by matnr"
      FOR ALL ENTRIES IN @lt_matnr
      WHERE matnr = @table_line.
ENDIF. 
LOOP AT lt_matnr ASSIGNING <fs_mara>.
  READ TABLE lt_ekpo ASSIGNING <fs_ekpo>
      WITH KEY matnr = <fs_matnr>.
  READ TABLE lt_ztable ASSIGNING <fs_ztable>
      WITH KEY matnr = <fs_matnr>.
  ...
ENDLOOP.

You should avoid SELECT ... ENDSELECT, not because of its own performance, but to make other improvements easier.

  1. You should use JOINs whenever you can instead of FOR ALL ENTRIES

Upvotes: 5

Florian
Florian

Reputation: 5071

If all 10,000,000 entries fit into ABAP's main memory, you should select all of them with a single SELECT ... INTO TABLE ..., followed by a LOOP.

This reduces expensive database interaction to a minimum and will be fastest.

If the records don't fit into main memory, you need to retrieve them in packages. Check out the PACKAGE SIZE addition of the SELECT statement.

Upvotes: 6

Related Questions