06-24-2010 7:57 AM
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
06-24-2010 9:31 AM
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
04-18-2014 1:45 PM
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