Inefficient Usage of SELECT ... ORDER BY - possible solutions

[kroslaniec]

We need to put all custom reports through the ONAPSIS tool. We need to resolve all problems with 'very high' status. One of them is Inefficient Usage of SELECT ... ORDER BY. I've got several questions, one of them is how to solve that without de-optimalizing the program. Example below:

SELECT * FROM vtbfinko

WHERE bukrs EQ wa_bukrs

AND rfha EQ wa_rfha

AND rfhazu EQ wa_rfhazu

AND rkondgr EQ wa_rkondgr

AND sbktyp EQ '20'

ORDER BY rkond.

EXIT.

ENDSELECT.

One of the possible resolution which I found is this one:

SELECT * FROM vtbfinko

WHERE bukrs EQ wa_bukrs

AND rfha EQ wa_rfha

AND rfhazu EQ wa_rfhazu

AND rkondgr EQ wa_rkondgr

AND sbktyp EQ '20'

INTO TABLE @DATA(itab_vtbfinko)

SORT itab_vtbfinko BY rkond.

LOOP AT itab_vtbfinko INTO DATA(wa_vtbfinko).

MOVE-CORRESPONDING wa_vtbfinko to vtbfinko.

EXIT.

ENDLOOP.

My problem is that I cannot debug it and I don't know the business logic behind this statement. My question is if it's the most optimal solution or we should think of something else? Also, how does the ORDER BY works in the first statement? Is the ORDER BY made on the end of statement with all the rows or first it's sorting the table and then search for the first element? Because in first example there's only one row and there's nothing to sort.

I also tried option with UP TO 1 ROWS and ORDER BY but it's still shows the same problem.

[FredericGirod]

Onapsis is a security tool, not a development control tool, this is the job of ATC for example.

The SELECT .... EXIT. ENDSELECT is a performance issu, it should be solved by the UP TO 1 ROWS you have tested.

Onapsis for all the issue, propose a solution, an explanation. You don't have access to the full report ?

[alexandra_belova]

In case of SELECT…ENDSELECT all the required data is transferred from database table to application server level, it is sorted afterwards according to ORDER BY instruction and the first record is taken. Both constructions you provided SELECT…ENDSELECT and SELECT…INTO TABLE + SORT work similar from perfomance point of view. So, you are not de-optimizing the program replacing one with the other.

Thinking of a more optimal solution, I would suggest pushing down sorting to database level, as you mentioned

SELECT * FROM vtbfinko

WHERE bukrs EQ wa_bukrs

AND rfha EQ wa_rfha

AND rfhazu EQ wa_rfhazu

AND rkondgr EQ wa_rkondgr

AND sbktyp EQ '20'

ORDER BY rkond

INTO @DATA(itab_vtbfinko)

UP TO 1 ROWS.

It will not transfer useless data from DB level to application server, but only 1 row that is really needed.

[kroslaniec]

This solution also was my primary choice, but it is still leaving us with the Inefficient Usage of SELECT ... ORDER BY message in ONAPSIS, that's why I used one with LOOP. So the question is if we want to optimalize the code or remove all the warnings from ONAPSIS.

[Sandra_Rossi]

krzysztof.roslaniec You may open a ticket at ONAPSIS so that they don't send a warning for that, or explain clearly what problem they find with the statement.