Optimization of select with FOR ALL ENTRIES on SAP HANA database
Generally open SQL DB Hints are not recommended in programs and needs to be replaced with equivalent statements while migrating to HANA DB.
But if the performance of “Select for all entries “ is poor in programs then SAP HANA Specific DB Hints may help optimize the code.
If the select Query is as below
SELECT VBELN POSNR MATNR
FROM VBAK
INTO CORRESPONDING FIELDS OF TABLE LT_RESULT
FOR ALL ENTRIES IN LT_VBAK
WHERE VBELN = LT_VBAK-VBELN.
If the performance of the above statement is poor, the equivalent statement with DB Hints that can be checked would be
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_VBAK ).
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 VBELN POSNR MATNR
FROM VBAK
INTO CORRESPONDING FIELDS OF TABLE LT_RESULT
FOR ALL ENTRIES IN LT_VBAK
WHERE VBELN = LT_VBAK-VBELN
%_HINTS ADABAS L_HINT.
Hmm... this piece looks a bit unfinished.
You might consider adding that the hints you use in OpenSQL are different than DB hints, as they really affect the SQL statement that gets created by the DBSL instead of the SQL execution by the DB engine.
Also, in the context of this article, it would make sense to explain what FOR ALL ENTRIES does and what the specific problem with it could be on DB level.
Why is the performance sometimes bad? What does the hint change to fix that?
And what would be a good situation to use the hint?
This could be a much better article and there are information (SAP notes/KBA/Documentation) available that you can use and reference in order to improve it.