Performance issue

[Former Member]

Moved to correct forum. Please use a meaningful subject in future

Hi,

I have one issue with select query.getting the data from rbkp table using for all entries statement.

SELECT lifnr FROM lfb1 INTO TABLE lt_lifnr

WHERE bukrs IN s_bukrs

AND lifnr IN s_lifnr.

IF NOT lt_lifnr[] IS INITIAL.

SELECT * FROM rbkp INTO TABLE z_rbkp

FOR ALL ENTRIES IN lt_lifnr

WHERE bukrs IN s_bukrs

AND gjahr IN s_gjahr

AND blart IN p_blart

AND lifnr = lt_lifnr-lifnr

AND xblnr IN s_xblnr.

endif.

executed this logic.but it is taking much time and giving dump that it exceeded the maximum time.

Can any body please advise me to get the data.

thanks in advance.

Edited by: Matt on May 25, 2009 1:29 PM

Moderator message - Total Posts: 161 Total Questions: 76 (75 unresolved) If you assign po(i)nts and close your old posts, that will encourage people to respond to your new questions.

Edited by: Rob Burbank on May 25, 2009 9:35 AM

[Former Member]

Hi,

When you are writing the select query for , For all entries try to avoid SELECT * FROM RBKP...

instead of that SELECT FLD1 FLD2 from RBKP like this you can do it. So it will reduce the fetch time in the table.

Regards

Thiru

[Former Member]

Hi,

we need total RBKP data, that is the reason, we choosed select *.

[Former Member]

well i dont think it will help much but you may give it a shot.

try to fetch RBKP data matching your selection criterias, without for all entries.

then try to check your for all entries criteria with internal table management (loop [condition] endloop).

[Former Member]

Hi,

1. first use all available key fields in where condition in proper order and then non-key fields.

2. crerate index and use it in where condition. Index improves performance .

3. Try to avoid * in select stmt if possible

Hope these will help you.

Rgds,

Kiran

[Former Member]

Hi,

Please try with below select. secondary index is there on xbelnr,lifnr,bukrs.So I changed the order in where condition.Please select the required fields instead of * . Please try to provide key field belnr in where condition if possible.

SELECT lifnr FROM lfb1 INTO TABLE lt_lifnr

WHERE bukrs IN s_bukrs

AND lifnr IN s_lifnr.

IF NOT lt_lifnr[] IS INITIAL.
  SELECT * FROM rbkp INTO TABLE z_rbkp
  FOR ALL ENTRIES IN lt_lifnr
         WHERE xblnr IN s_xblnr
         AND lifnr = lt_lifnr-lifnr
        AND bukrs = *lt_lifnr-bukrs*
        AND gjahr IN s_gjahr
        AND blart IN p_blart.
endif.

Thanks.

Suma.

[rainer_hbenthal]

I do not understand the first select.

Why not

         SELECT * FROM rbkp INTO TABLE z_rbkp
         where lifnr in s_lifnt
         WHERE bukrs IN s_bukrs
                AND   gjahr IN s_gjahr
                AND   blart IN p_blart
                AND   lifnr = lt_lifnr-lifnr
                AND   xblnr IN s_xblnr.

[Former Member]

Hi,

SELECT lifnr bukrs FROM lfb1 INTO TABLE lt_lifnr
        WHERE bukrs IN s_bukrs
          AND lifnr IN s_lifnr.

IF NOT lt_lifnr[] IS INITIAL.
  SELECT BELNR GJAHR BLART BLDAT BUDAT
         USNAM TCODE CPUDT CPUTM VGART " Select what all required Fields
         FROM rbkp INTO TABLE it_rbkp 
      FOR ALL ENTRIES IN lt_lifnr
      WHERE bukrs IN lt_lifnr-bukrs " make it like this
        AND gjahr IN s_gjahr 
        AND blart IN p_blart   " is p_blart is select-option or parameter
        AND lifnr = lt_lifnr-lifnr
        AND xblnr IN s_xblnr
        AND RBSTAT NE '2'.  " Can be control by field too
endif.

Regards,

Suneel G

[Former Member]

Is p_blart parameter or select-option. You are using In operator in your select query for comparing it.

Also what is the type for yor it_lifnr internal table??

[Former Member]

hi

In the where clause the fields used should be in the order they are in the table rbkp and also use SORT and DELETE DUPLICATE as shown below.

i.e

IF NOT lt_lifnr[] IS INITIAL.

SORT lt_lifnr BY lifnr

DELETE ADJACENT DUPLICATES COMPARING LIFNR

SELECT * FROM rbkp INTO TABLE z_rbkp

FOR ALL ENTRIES IN lt_lifnr

where gjahr in s_gjahr AND

blart IN p_blart AND

xblnr IN s_xblnr AND

bukrs IN s_bukrs AND

lifnr = lt_lifnr-lifnr

Hope this works

Thanks and regards

Suraj S Nair

[matt]

Moved to correct forum. Please use a meaningful subject in future

[Former Member]

"Check s_bukrs first if you want that it should have some value on sel-screen.

IF NOT s_bukrs IS INITIAL.

"-----Include BUKRS along with LIFNR in below SELECT-----*

    SELECT bukrs lifnr FROM lfb1 INTO TABLE lt_lifnr
         WHERE bukrs IN s_bukrs
              AND lifnr IN s_lifnr.

ENDIF.

IF NOT lt_lifnr[] IS INITIAL.
  SORT lt_lifnr BY bukrs lifnr.	"All LIFNR of one BUKRS first and then LIFNR of next  BUKRS

"Use DELETE ADJACENT... if there are duplicate records for BUKRS/LIFNR Combination in lt_lifnr.

SELECT * FROM rbkp INTO TABLE z_rbkp
   FOR ALL ENTRIES IN lt_lifnr
   WHERE bukrs = lt_lifnr-bukrs	"Change s_bukrs (and hence the "IN" operator to "=")
      AND gjahr IN s_gjahr	"Check value of s_gjahr-low there should be 1 entry on sel-screen
      AND blart IN p_blart    "Check if it is parameter or select-options change "IN" to "=" appropriately
      AND lifnr = lt_lifnr-lifnr
      AND xblnr IN s_xblnr.
ENDIF.

"If above SELECT statement still takes time then make use of PACKAGE SIZE on First SELECT on table lfb1 (if the number of records from lfb1 are too high) and fetch data from rbkp based on that.

EXAMPLE:

IF NOT s_bukrs IS INITIAL.
  SELECT bukrs lifnr FROM lfb1 INTO TABLE lt_lifnr PACKAGE SIZE 50
         WHERE bukrs IN s_bukrs
              AND lifnr IN s_lifnr.

    IF NOT lt_lifnr IS INITIAL.
      SELECT * FROM rbkp INTO TABLE z_rbkp
        FOR ALL ENTRIES IN lt_lifnr
        WHERE bukrs = lt_lifnr-bukrs	"Change s_bukrs (and hence the "IN" operator to "=")
        AND gjahr IN s_gjahr	"Check value of s_gjahr-low there should be 1 entry on sel-screen
        AND blart IN p_blart    "Check if it is parameter or select-options change "IN" to "=" appropriately
        AND lifnr = lt_lifnr-lifnr
        AND xblnr IN s_xblnr.
    ENDIF.

"MOVE z_rbkp into another internal table
    z_rbkp1[] = z_rbkp[].

"Reinitialize both lt_lifnr and z_rbkp Internal Tables for fetching another set of data.
    REFRESH: lt_lifnr, z_rbkp.
  ENDSELECT.
ENDIF.

Edited by: Manuj Bhardwaj on May 26, 2009 10:48 PM

[Former Member]

PACKAGE SIZE can help reduce memory usage, but it will not speed up a SELECT.

Rob

[Former Member]

Hi Abap_Inter,

When you have many clauses as yours:

AND lifnr IN s_lifnr. <- s_lifnr have any data in it?

IF NOT lt_lifnr[] IS INITIAL.

SELECT * FROM rbkp INTO TABLE z_rbkp

FOR ALL ENTRIES IN lt_lifnr

WHERE bukrs IN s_bukrs <- s_bukrs contains data?

AND gjahr IN s_gjahr <- etc..

AND blart IN p_blart <- etc...

AND lifnr = lt_lifnr-lifnr

AND xblnr IN s_xblnr.

you can use Dynamic Select (and check if the For All Entries table to see if it has any rows first off).

See: for a start.

I hope that this helps,

Dan Perecky

[Former Member]

resolved

[Former Member]

resolved