topic Code optimization II in Application Development and Automation Discussions

Code optimization II

Former Member — Wed, 29 Nov 2006 13:29:26 GMT

Hi,

This time i need to optimize the following select it trooks to much time to execute

SELECT zuonr dmbtr wrbtr waers

INTO CORRESPONDING FIELDS OF TABLE t_bsad

FROM bsad

WHERE bukrs = p_bukrs AND

bldat IN r_rate AND

blart = 'RV'.

r_date is a rango usually 3 moths

Thanks In Advance

Juan

Re: Code optimization II

Former Member — Wed, 29 Nov 2006 13:33:15 GMT

Instead of using into corresponding field, you can declare the table t_bsad having only those fields which are selected.

You need to pass more key fields like kunnr, gjahr etc, as there is not much we can do here(Other than creating an index).

Regards,

Ravi

Re: Code optimization II

Former Member — Wed, 29 Nov 2006 14:21:37 GMT

hi Juan,

Avoid using Corresponding fields statement and specify all the key fields in where condition and the order should be the order in which they are there in the data base table ..

Regards,

Santosh

Re: Code optimization II

Former Member — Wed, 29 Nov 2006 14:25:46 GMT

Rather than BSAD, you should go to BKPF where you can use an index and then BSEG.

Rob

Re: Code optimization II

andreas_mann3 — Wed, 29 Nov 2006 14:26:22 GMT

Hi,

you must feed field <b>KUNNR</b>

Re: Code optimization II

Former Member — Wed, 29 Nov 2006 14:30:44 GMT

Thanks Andreas.. and all of you

Best regards

Juan

Message was edited by:

Juan Carlos Diez de Medina

Re: Code optimization II

Former Member — Wed, 29 Nov 2006 14:32:30 GMT

Hi,

The following instruction will help to improve performance of SELECT statement

1. Avoid INTO CORRESPONDING clause in the SELECT Statement

( Declare a internal table with the respective field list. If field list less than 1/3

utilization of fields)

2. Use Symbols like EQ,BT,NE.. instead of =, <>

3. Check in 'where' clause whether full key utilised or not. If not and the table is

transparent table, create secondary index on these fields (in your case

bukrs,bldat and blart). It improves data acess).

Finally you code will look like this:

TYPES:
    BEGIN OF tys_bsad,
      zuonr  type zuonr,
      dmbtr  type dmbtr,
      wrbtr   type wrbtr,
      waers  type waers,
   END OF tys_bsad.

DATA: t_bsad type table of tys_bsad.

SELECT zuonr dmbtr wrbtr waers
     INTO TABLE t_bsad
   FROM bsad
WHERE bukrs EQ p_bukrs AND
             bldat IN r_rate AND
             blart  EQ 'RV'.

if helps plz reward points.

Regards

Bhupal Reddy

Re: Code optimization II

Former Member — Wed, 29 Nov 2006 14:54:19 GMT

This seems to work pretty well:


REPORT ztest NO STANDARD PAGE HEADING LINE-SIZE 255 MESSAGE-ID 00.

TABLES: bkpf, bseg, bsad.

PARAMETERS    : p_bukrs LIKE bsad-bukrs.
SELECT-OPTIONS  r_rate  FOR bsad-bldat.

DATA: BEGIN OF t_bkpf OCCURS 0,
        bukrs LIKE bkpf-bukrs,
        belnr LIKE bkpf-belnr,
        gjahr LIKE bkpf-gjahr,
        waers LIKE bkpf-waers.
DATA: END   OF t_bkpf.

DATA: BEGIN OF t_bseg OCCURS 0,
        bukrs LIKE bkpf-bukrs,
        belnr LIKE bkpf-belnr,
        gjahr LIKE bkpf-gjahr,
        zuonr LIKE bsad-zuonr,
        dmbtr LIKE bsad-dmbtr,
        wrbtr LIKE bsad-wrbtr.
DATA: END   OF t_bseg.

DATA: BEGIN OF t_bsad OCCURS 0,
        zuonr LIKE bsad-zuonr,
        dmbtr LIKE bsad-dmbtr,
        wrbtr LIKE bsad-wrbtr,
        waers LIKE bsad-waers.
DATA: END   OF t_bsad.

SELECT bukrs belnr gjahr waers
  FROM bkpf
  INTO TABLE t_bkpf
  WHERE bukrs = p_bukrs AND
        bstat IN (' ', 'A', 'B', 'D', 'M', 'S', 'V', 'W', 'Z') AND
        blart = 'RV' AND
        bldat IN r_rate.

CHECK NOT t_bkpf[] IS INITIAL.

SELECT bukrs belnr gjahr zuonr dmbtr wrbtr
  INTO CORRESPONDING FIELDS OF TABLE t_bseg
  FROM bseg
  FOR ALL ENTRIES IN t_bkpf
  WHERE bukrs = t_bkpf-bukrs AND
        belnr = t_bkpf-belnr AND
        gjahr = t_bkpf-gjahr AND
        koart = 'D'          AND
        augbl <> space.

SORT: t_bkpf BY bukrs belnr gjahr,
      t_bseg BY bukrs belnr gjahr.

LOOP AT t_bseg.
  READ TABLE t_bkpf WITH KEY
    bukrs = t_bseg-bukrs
    belnr = t_bseg-belnr
    gjahr = t_bseg-gjahr
    BINARY SEARCH.

  MOVE: t_bseg-zuonr TO t_bsad-zuonr,
        t_bseg-dmbtr TO t_bsad-dmbtr,
        t_bseg-wrbtr TO t_bsad-wrbtr,
        t_bkpf-waers TO t_bsad-waers.
  APPEND t_bsad.
ENDLOOP.

Rob