avoid inner join

[Former Member]

Hi experts,

Iam using following inner join statement. But it is taking lot of time.

Could any one please tell me how can i combined two tables without inner join.

Can i use loop statement?

SELECT a~aufnr
         a~posnr
         a~etenr
         a~werks
         a~matnr
         a~j_3asize
         a~j_4krcat
         a~vdatu
         a~menge
         a~j_3aresm
         INTO TABLE t_j3abdsi
         FROM j_3abdsi AS a INNER JOIN mara AS b
         ON a~matnr = b~matnr
         WHERE b~mtart IN so_mtart AND
               a~matnr IN so_matnr AND
               a~werks IN so_werks AND
               a~vdatu IN so_vdatu.

thanks

p.

[Former Member]

Hi Priya,

As you have only 2 tables in the inner join, no need to avoid. Use fields in the where clause in the same order as they appear in database tables which will improve preformance.

Regards,

Satish

[naimesh_patel]

To avoid, you need to make them as a separate quries and you can use the FOR ALL ENTRIES.

data: begin of it_mara occurs 0,
      matnr type matnr,
      mtart type mtart,
      end  of it_mara.
      
select matnr mtart
       into table it_mara
       from  mara
       where mtart in so_mtart.
       
if it_mara[] is not initial.
SELECT aufnr
       posnr
       etenr
       werks
       matnr
       j_3asize
       j_4krcat
       vdatu
       menge
       j_3aresm
       INTO TABLE t_j3abdsi
       FROM j_3abdsi 
       for all entries in it_mara
       WHERE a~matnr IN it_mara-matnr AND
               a~werks IN so_werks AND
               a~vdatu IN so_vdatu.

endif.      

Regards,

Naimesh Patel

[Former Member]

Hi naimesh...

Thank you. Full points alloted...

[Former Member]

Hi Naimesh,

Your code does not take into account select-option so_matnr. Besides if the user enters values only in select options so_werks and so_vdatu and leave so_mtart blank your code will download the complete MARA table into the application server.

You should start with table t_j3abdsi. We are checking MARA only to see if the materials being considered are of the type entered in so_mtart. Starting with MARA could leave a possibility of ending up with the entire MARA table in the buffer (this could quite often lead to performance degradation and a dump) if the user does not enter anything in so_mtart or so_matnr. Besides if the select on t_j3abdsi picks just one material wouldn't it make more sense to check the validity of that one material. If so why do we need to read the complete mara table?

[former_member191735]

try to use index on both tables.

j_3abdsi is this a table?

[Former Member]

Hi Priya,

Your problem probably lies in the fact that your select statement is not picking an appropriate index for table t_j3abdsi. My system (version 4.7) does not have this table, so I cannot advice you on how to use an index. You will have to arrange the criteria in your where clause to pick the best index or create an index that suits your select. Again creating an index will increase your database size so you will have to weight your pros and cons. If you have an available index, try and make some of your select-options used on the where condition for table t_j3abdsi mandatory.

Your question however was to convert the code from a join to a for all entries. You can write a code like the one I am showing you.

TYPES: BEGIN OF ty_t_j3abdsi,
         matnr    TYPE t_j3abdsi-matnr   ,
         aufnr    TYPE t_j3abdsi-aufnr   ,
         posnr    TYPE t_j3abdsi-posnr   ,
         etenr    TYPE t_j3abdsi-etenr   ,
         werks    TYPE t_j3abdsi-werks   ,
         j_3asize TYPE t_j3abdsi-j_3asize,
         j_4krcat TYPE t_j3abdsi-j_4krcat,
         vdatu    TYPE t_j3abdsi-vdatu   ,
         menge    TYPE t_j3abdsi-menge   ,
         j_3aresm TYPE t_j3abdsi-j_3aresm,
         del(1)   TYPE c,
       END OF ty_t_j3abdsi,

       BEGIN OF ty_mara,
         matnr TYPE mara-matnr,
       END OF ty_mara.

DATA: w_index         TYPE                 sy-tabix    ,
      w_found(1)      TYPE                 c           ,
      w_t_j3abdsi     TYPE                 ty_t_j3abdsi,

      t_t_j3abdsi     TYPE        TABLE OF ty_t_j3abdsi,
      t_t_j3abdsi_tmp TYPE        TABLE OF ty_t_j3abdsi,
      t_mara          TYPE HASHED TABLE OF ty_mara
        WITH UNIQUE KEY matnr.

* You will have to adjust the where clause on this select to pick the best index
SELECT matnr
       aufnr
       posnr
       etenr
       werks
       j_3asize
       j_4krcat
       vdatu
       menge
       j_3aresm
  FROM j_3abdsi
  INTO TABLE t_t_j3abdsi
  WHERE matnr IN so_matnr
  AND   werks IN so_werks
  AND   vdatu IN so_vdatu.

IF sy-subrc EQ 0.

  t_t_j3abdsi_tmp[] = t_t_j3abdsi[].

  SORT t_t_j3abdsi_tmp BY matnr.

  DELETE ADJACENT DUPLICATES FROM t_t_j3abdsi_tmp COMPARING matnr.

  SELECT matnr
    FROM mara
    INTO TABLE t_mara
    FOR ALL ENTRIES IN t_t_j3abdsi_tmp
    WHERE matnr EQ t_t_j3abdsi_tmp-matnr
    AND mtart   IN so_mtart.

  LOOP AT t_t_j3abdsi INTO w_t_j3abdsi.

    w_index = sy-tabix.

    AT NEW matnr.
      READ TABLE t_mara WITH KEY matnr = w_t_j3abdsi-matnr
                                 TRANSPORTING NO FIELDS.
      IF sy-subrc EQ 0.
        w_found = 'X'.
      ELSE.
        CLEAR w_found.
      ENDIF.
    ENDAT.

    IF w_found IS INITIAL.
      w_t_j3abdsi-del = 'X'.
      MODIFY t_t_j3abdsi FROM w_t_j3abdsi INDEX w_index
        TRANSPORTING
          del.
    ENDIF.

  ENDLOOP.

  DELETE t_t_j3abdsi WHERE del EQ 'X'.

ENDIF.

[Former Member]

Priya - FOR ALL ENTRIES is generally slower than a JOIN. If you find it running more qiuickly, it may be due to some buffering (not SAP buffering) going on. Try executing the modified program tomorrow before running anything else to see if it is actually faster.

As said elsewhere, the problem is probably because of indexes.

Rob