topic Re: issue with the select query in Application Development and Automation Discussions

issue with the select query

Former Member — Tue, 04 Mar 2008 13:54:20 GMT

Hi,


DATA: BEGIN OF IT_MATERIAL OCCURS 0,
      IDNRK  LIKE  STPO-IDNRK,
      MATNR  LIKE  MAST-MATNR,
      END OF IT_MATERIAL.

FORM CHECK_SALESDATA_EXISTENCE .
  IF NOT IT_MATERIAL[] IS INITIAL.
    LOOP AT IT_MATERIAL.
      CLEAR VBAP.
      SELECT SINGLE * FROM VBAP WHERE MATNR EQ IT_MATERIAL-MATNR.
      IF SY-SUBRC NE 0.
        DELETE IT_MATERIAL WHERE IDNRK EQ IT_MATERIAL-IDNRK
                             AND MATNR EQ IT_MATERIAL-MATNR.
      ENDIF.
      CLEAR IT_MATERIAL.
    ENDLOOP.
  ENDIF.
ENDFORM.                    " CHECK_SALESDATA_EXISTENCE

The above is the subroutine in the program which check whether sales data exists for a material or not. But the problem is the select statement which is written in the loop is taking so much of time because of this the program is going into short dump "Time Exceed Limit".

Please suggest how can i modifiy the above statemnt so that the performance of the program increases.

<REMOVED BY MODERATOR>

Thanks.

Edited by: Alvaro Tejada Galindo on Mar 4, 2008 10:37 AM

Re: issue with the select query

Former Member — Tue, 04 Mar 2008 14:01:38 GMT

well key fields of VBAP are VBELN and POSNR.

your selection criteria is MATNR. means you make a total unqualified select on VBAP. no wonder it takes a hell of a lot of time.

either create an index for MATNR, or try to make a more qualified select at least try to have a VBELN

Re: issue with the select query

JozsefSzikszai — Tue, 04 Mar 2008 14:02:39 GMT

hi Ramya,

SELECT in LOOP is basically 'forbidden', i.e. not really suggested because of bad performance. You can use FOR ALL ENTRIES instead (if you want to make selection based on internal table):

IF NOT IT_MATERIAL[] IS INITIAL.

SELECT SINGLE * FROM VBAP

*INTO it_vbap

FOR ALL ENTRIES IN IT_MATERIAL*

WHERE MATNR EQ IT_MATERIAL-MATNR.

and make your deletion here based on the selected entries...

hope this helps

Re: issue with the select query

Former Member — Tue, 04 Mar 2008 14:06:04 GMT

use for all entries instead of writing select inside loop.. endloop ..

FORM CHECK_SALESDATA_EXISTENCE .

IF NOT IT_MATERIAL[] IS INITIAL.

SELECT * FROM VBAP into it_vbap

for all entries in IT_MATERIAL

WHERE MATNR EQ IT_MATERIAL-MATNR.

ENDIF.

Loop at it_material.

read table it_vbap with key matnr = IT_MATERIAL-MATNR.

if sy-subrc <> 0.

DELETE IT_MATERIAL WHERE IDNRK

EQ IT_MATERIAL-IDNRK AND MATNR EQ

IT_MATERIAL-MATNR.

ENDIF.

CLEAR IT_MATERIAL.

ENDLOOP.

ENDFORM.

Re: issue with the select query

Former Member — Tue, 04 Mar 2008 14:07:01 GMT

Hai,

U can use this :

IF NOT IT_MATERIAL[] IS INITIAL.

SELECT *

FROM VBAP

INTO it_vbap

FOR ALL ENTRIES IN it_material

WHERE MATNR = IT_MATERIAL-MATNR

AND idnrk <> IT_MATERIAL-idnrk.

ENDIF.

Hope this helps..

Neeraj

Re: issue with the select query

Former Member — Tue, 04 Mar 2008 14:08:08 GMT

Hai Ramya,

U can use the following code.

IF NOT IT_MATERIAL[] IS INITIAL.

SELECT *

FROM VBAP

INTO it_vbap

FOR ALL ENTRIES IN it_material

WHERE MATNR <> IT_MATERIAL-MATNR

AND idnrk <> IT_MATERIAL-idnrk.

ENDIF.

Hope this helps..

Neeraj

Re: issue with the select query

Former Member — Tue, 04 Mar 2008 14:08:42 GMT

Florian - it's generally a bad idea to create an index on a standard SAP table - particularly to speed up a single SELECT.

In this case, there is a secondary index table VAPMA which gives sales orders by material. That is what should be used in this case.

SELECTs in loops are things to be avoided, but again, not the real problem.

Rob

Re: issue with the select query

rahulkavuri — Tue, 04 Mar 2008 14:11:22 GMT

report YJJC_FB02
       no standard page heading line-size 255.

DATA: BEGIN OF IT_MATERIAL OCCURS 0,
IDNRK LIKE STPO-IDNRK,
MATNR LIKE MAST-MATNR,
END OF IT_MATERIAL.

*&---------------------------------------------------------------------*
*&      Form  CHECK_SALESDATA_EXISTENCE
*&---------------------------------------------------------------------*
*       text
*----------------------------------------------------------------------*
FORM CHECK_SALESDATA_EXISTENCE .
  IF NOT IT_MATERIAL[] IS INITIAL.

select * from VBAP into TABLE it_vbap FOR ALL ENTRIES IN IT_MATERIAL 
                                     WHERE matnr = IT_MATERIAL-MATNR

    LOOP AT IT_MATERIAL.
      READ TABLE it_vbap with KEY MATNR = IT_MATERIAL-matnr.
      IF sy-subrc <> 0.
        delete IT_MATERIAL INDEX sy-tabix.
      ENDIF.
    ENDLOOP.
  ENDIF.
ENDFORM. " CHECK_SALESDATA_EXISTENCE

Here goes the refined code for you.. this will improve the performance.. dont forget to delcare IT_VBAP TYPE TABLE OF VBAP WITH HEADER LINE.

Re: issue with the select query

Former Member — Tue, 04 Mar 2008 14:11:48 GMT

SELECT F1

INTO TABLE itab

FROM VBAP

FOR ALL ENTRIES IN IT_MATERIAL

where MATNR EQ IT_MATERIAL-MATNR.

SORT: i_itab BY matnr,

IT_MATERIAL BY matnr.

LOOP AT IT_MATERIAL INTO wa_MATERIAL.

v_tabix = sy-tabix.

READ TABLE itab INTO wa_itab WITH KEY matnr = wa_itab-matnr

TRANSPORTING NO FIELDS.

IF sy-subrc <> 0.

DELETE IT_MATERIAL INDEX v_tabix.

ENDIF.

ENDLOOP.

Try this , it may work..

Thanks & regards,

Harsha

Re: issue with the select query

Former Member — Tue, 04 Mar 2008 15:34:03 GMT

The program deletes multiple records inside a loop. This isn't good practice. The following should work:

REPORT ztest LINE-SIZE 80 MESSAGE-ID 00.

TABLES: vapma.

DATA: BEGIN OF it_material OCCURS 0,
  matnr LIKE mast-matnr,
  idnrk LIKE stpo-idnrk,
END OF it_material.

PERFORM check_salesdata_existence.

*&---------------------------------------------------------------------*
*&      Form  CHECK_SALESDATA_EXISTENCE
*&---------------------------------------------------------------------*
*       text
*----------------------------------------------------------------------*
FORM check_salesdata_existence .

  DATA: delete.

  SORT it_material BY matnr.

  LOOP AT it_material.
    AT NEW matnr.
      SELECT SINGLE * FROM vapma
        WHERE matnr EQ it_material-matnr.
      IF sy-subrc <> 0.
        delete = 'X'.
      ELSE.
        CLEAR delete.
      ENDIF.
    ENDAT.
    IF delete = 'X'.
      DELETE it_material.
    ENDIF.
  ENDLOOP.

ENDFORM. " CHECK_SALESDATA_EXISTENCE

Note that I have changed the filed order in it_material so that AT NEW will work correctly.

Rob