We have the following SQL.
INTO CORRESPONDING FIELDS OF TABLE lt_vdbepi_key
FROM ( vdbeki AS vdbeki INNER JOIN vdbepi AS vdbepi
ON vdbekimandt = vdbepimandt
AND vdbekibukrs = vdbepibukrs
AND vdbekirbelkpfd = vdbepirbelkpfd )
FOR ALL ENTRIES IN ut_loan_bp_key
WHERE vdbeki~bukrs EQ uv_bukrs
AND vdbeki~ranl EQ ut_loan_bp_key-ranl
AND vdbeki~sstorno EQ space.
if we don't no Key Value, it means this contract doesn't have Postings
IF lt_vdbepi_key IS NOT INITIAL.
FROM bsad INTO CORRESPONDING FIELDS OF TABLE ct_bsad
FOR ALL ENTRIES IN lt_vdbepi_key
WHERE bukrs EQ uv_bukrs
AND vertn IN s_ranl
AND ( ( gjahr EQ lt_vdbepi_key-rebzj " direct link
AND belnr EQ lt_vdbepi_key-rebzg
AND buzei EQ lt_vdbepi_key-rebzz )
OR ( rebzg EQ lt_vdbepi_key-rebzg " indirect link
AND rebzj EQ lt_vdbepi_key-rebzj
AND rebzz EQ lt_vdbepi_key-rebzz ) ).
When we run this program the first time, it'll take 10 min to finish. But it'll take only 1 second when we try this in the second time. We tried to use ST05 and found that it is using indexes already.
We believe that it should be the cache issue becuase we'll see this problem after a few hours when we run this program again. We already talked to our BASIS but they said the system is in the best status. If they tune the system for this SQL, we might have other issues.
Do you have any suggestions that we can tune this SQL?
Note: FAE just have 20+ records max in the internal table.
Thanks in advance,