Optimizing program (SELECT query).

[Former Member]

The below piece of code is taking a long time for execution. Can somebody please suggest a better way to write it.

-

---

lv_sydatum2 = sy-datum - 5.

SELECT anlage

auszdat

FROM ever

INTO TABLE lt_ever PACKAGE SIZE 5000

WHERE anlage NE space

AND auszdat LE lv_sydatum2.

SELECT ablbelnr

FROM eablg

into TABLE lt_eab

FOR ALL ENTRIES IN lt_ever[]

WHERE anlage = lt_ever-anlage

AND ablesgr = '01'

AND adatsoll GT lt_ever-auszdat.

SELECT ablbelnr

from eabl

appending TABLE lt_eab1

for all ENTRIES IN lt_eab[]

where ablbelnr = lt_eab-ablbelnr

and ablstat = '0'.

CLEAR lt_ever.

REFRESH lt_ever.

CLEAR lt_eab.

REFRESH lt_eab.

ENDSELECT.

-

---

[friedrich_keller]

Hello Akshay,

Plese provide a high level excerpt of the spec in order to let us understand what exactly you want to achieve. Also some information concerning volumes would be good (e.g., total number of contracts in the system, monthly/bi-monthly/... reading etc.).

Kind regards,

Fritz

[Former Member]

The requirement is to read the Meter read orders created for all the contracts which have moved out. The meter read orders are getting created for dates after the move-out date.

Total number of contracts in the system = 9,247,201

Out of these, the moved out contracts are = 7,580,749

number of meter read documents in table EABLG = 143033,318

The meter read orders are created on a monthly basis.

[Former Member]

Hi

You can have RFC_READ_TABLE for first query as suggested

Also you can try following FMs

ISU_DB_EABLG_FORALL

ISU_DB_EVER_SELECT_IN_RANGE

ISU_DB_EVER_SELECT

Regards,

Prajakta

[Former Member]

Hi Akshay,

Dont use 'anlage NE space' in the first select.

If lot of data will be coming from the first query itself, try using FM 'RFC_READ_TABLE' .

DATA : lt_data TYPE TABLE OF tab512.

DATA : lt_fields  TYPE TABLE OF rfc_db_fld,
       lt_option TYPE TABLE OF rfc_db_opt.

lw_fields-fieldname = 'ANLAGE'.
lw_fields-length    = '10'.
lw_fields-type      = 'CHAR'.

APPEND lw_fields TO lt_fields.

lw_option-text = 'auszdat LE lv_sydatum2'.

APPEND lw_option TO lt_option.

CALL FUNCTION 'RFC_READ_TABLE'
  EXPORTING
    query_table                = 'EVER'
*   DELIMITER                  = ' '
*   NO_DATA                    = ' '
*   ROWSKIPS                   = 0
*   ROWCOUNT                   = 0
  TABLES
    OPTIONS                    = lt_option
    fields                     = lt_fields
    data                       = lt_data
 EXCEPTIONS
   TABLE_NOT_AVAILABLE        = 1
   TABLE_WITHOUT_DATA         = 2
   OPTION_NOT_VALID           = 3
   FIELD_NOT_VALID            = 4
   NOT_AUTHORIZED             = 5
   DATA_BUFFER_EXCEEDED       = 6
   OTHERS                     = 7

Also check lt_ever[] is not initial before using for all entries.

Regards,

Manish