Application Development Discussions
Join the discussions or start your own on all things application development, including tools and APIs, programming models, and keeping your skills sharp.
cancel
Showing results for 
Search instead for 
Did you mean: 

Performance of select query with inner join and for all entries

Former Member
0 Kudos

Hi Experts,

Please suggest me about the performance of the following select query. Its taking more time when i have checked in st05.




SELECT a~shpmnum_ob b~capa_fixed
      FROM /lot/pl_tour_h AS a
      INNER JOIN /lot/vo_lara_st AS b
      ON b~losp_id EQ a~res_losp_id
      INTO TABLE l_i_truck1
      FOR ALL ENTRIES IN l_i_shipment1
      WHERE a~shpmnum_ob EQ l_i_shipment1-tknum
        AND b~uom        EQ c_pal.


Thanks in advance

G.S.Naidu

2 REPLIES 2

ravi_lanjewar
Contributor
0 Kudos

Hi,

Before posting the problem search in SDN for your problem.

Mean while you can do

1) check table is empty (ie. l_i_shipment1 )

2) check identical read using st05 and if found remove it.

3) Break up the SQL first read data from table /lot/pl_tour_h without using join and used the for all entries, After then read data from table /lot/vo_lara_st.

Rgds

Ravi Lanjewar

octavian_nicolae
Employee
Employee
0 Kudos

Hi,

Generally performance of joins together with for all entries is bad. If you cannot avoid it, you can try using DB Hints as described in note 1662726.

Even though the note is for HANA DB, FM RSDU_CREATE_HINT_FAE can be used independent of DB. On HANA DB performance improvement is huge (i achieved 62 seconds using DB Hints compared to 1656 seconds using for all entries). On Oracle DB the same code initially run in 99 seconds with for all entries and with DB Hints in 82 seconds.

Sample code from SAP Note below:

Original statement:

SELECT COL1 COL2 COL3 COL4 COL5

  FROM TAB1

  INTO CORRESPONDING FIELDS OF TABLE LT_RESULT

  FOR ALL ENTRIES IN LT_SOURCE_TMP

  WHERE COL3 = LT_SOURCE_TMP-COL3

  AND   COL4 = LT_SOURCE_TMP-COL4

  AND   COL5 = LT_SOURCE_TMP-COL5


Revision:

DATA: L_T_TABLNM TYPE RSDU_T_TABLNM,

      L_LINES TYPE I,

      L_HINT TYPE RSDU_HINT.


APPEND 'TAB1' TO L_T_TABLNM.

L_LINES = LINES( LT_SOURCE_TMP ).


CALL FUNCTION 'RSDU_CREATE_HINT_FAE'

  EXPORTING

    I_T_TABLNM   = L_T_TABLNM

    I_FAE_FIELDS = 3

    I_FAE_LINES  = L_LINES

    I_EQUI_JOIN  = RS_C_TRUE

  IMPORTING

    E_HINT       = L_HINT

  EXCEPTIONS

    OTHERS       = 0.


SELECT COL1 COL2 COL3 COL4 COL5

  FROM TAB1

  INTO CORRESPONDING FIELDS OF TABLE LT_RESULT

  FOR ALL ENTRIES IN LT_SOURCE_TMP

  WHERE COL3 = LT_SOURCE_TMP-COL3

  AND   COL4 = LT_SOURCE_TMP-COL4

  AND   COL5 = LT_SOURCE_TMP-COL5

          %_HINTS ADABAS  L_HINT.

Best regards,

Octavian