Question Re: Poor performance in API API_SALES_ORDER_SRV GET method - order reading in Technology Q&A

Poor performance in API API_SALES_ORDER_SRV GET method - order reading

rosenpetrov70 — Thu, 07 Nov 2024 10:57:00 GMT

Hello,

in S4 hana 107 we have an issue with very poor performance during a single sales order reading via GET method of API API_SALES_ORDER_SRV.
We are trying to read a sales order header, all order items and schedule line information related to sales order items.
The issue seems to be in selecting data from a view A_SALESORDERSCHEDULELINE which contains about 700 million records.

Example:
/sap/opu/odata/sap/API_SALES_ORDER_SRV/A_SalesOrder('XYZ')?$expand=to_Item,to_Item/to_ScheduleLine

Test 1: OK.
Reading a sales order with only one item - takes a miliseconds - no performance issue here.

Test 2: Performance issue.
Reading a sales order with two items takes about 90 seconds.
If tha sales order has more than two items the performance is far more worse.

Details from debugging:

Performance issue is only here - data selection from A_SALESORDERSCHEDULELINE:

this code is executed:
CL_SADL_SQL_EXECUTOR->SELECT
...
ELSEIF <clauses>-offset = 0 OR <clauses>-offset > 0 AND <clauses>-order_by IS INITIAL.
SELECT (<clauses>-select) FROM (<clauses>-from) "#EC CI_DYNTAB
WHERE (<clauses>-where) "#EC CI_DYNWHERE
GROUP BY (<clauses>-group_by)
HAVING (<clauses>-having)
ORDER BY (<clauses>-order_by)
%_HINTS HDB @db_hints "#EC CI_HINTS
INTO CORRESPONDING FIELDS OF TABLE @ET_data
EXTENDED RESULT @LO_extended_result
UP TO @<clauses>-limit ROWS
CONNECTION (lv_dbcon)
%_native_end( hdb = @<clauses>-locale, otherwise = `` ).
...

FROM
<clauses>-from = A_SALESORDERSCHEDULELINE AS A_SALESORDERSCHEDULELINE

WHERE
<clauses>-where =
( A_SALESORDERSCHEDULELINE~SALESORDER, A_SALESORDERSCHEDULELINE~SALESORDERITEM ) IN ( ( 'XYZ', '000010' ), ( 'XYZ', '000020' ) )

*Hint:
In the debugger if we replace the original where clause which is:
( A_SALESORDERSCHEDULELINE~SALESORDER, A_SALESORDERSCHEDULELINE~SALESORDERITEM ) IN ( ( 'XYZ', '000010' ), ( 'XYZ', '000020' ) )
with this one:
( A_SALESORDERSCHEDULELINE~SALESORDER = 'XYZ' AND A_SALESORDERSCHEDULELINE~SALESORDERITEM IN ( '000010', '000020' ) )
then the select statement is executed immidiatelly and the there is no performance issue !!
This is all a standard SAP code and we do not have a clear idea how to solve this problem.

Please, propose a solution.

Thank you,
Rosen Petrov

Re: Poor performance in API API_SALES_ORDER_SRV GET method - order reading

rosenpetrov70 — Thu, 14 Nov 2024 12:43:02 GMT

solution:

Re: Poor performance in API API_SALES_ORDER_SRV GET metho...

rosenpetrov70 — Thu, 14 Nov 2024 12:46:04 GMT

Switch Off Language-Specific Sorting to Improve Performance

SADL runtime uses language-specific alphabetical sorting as the default sort strategy. If sort criteria are defined, the records in the result are sorted according to the alphabet of current user's language.

Prerequisites

This feature is available since SAP NetWeaver 7.40 SP08.

Context

The disadvantage of using language-specific sorting is that the database might need much more time to execute the query, even with factor 10 or more. To protect performance, you can switch the language-specific sorting off, if it is not needed. It is recommended that you use language-specific sorting only if:

$orderby is defined on a text field or
$filter with operators 'gt', 'ge', 'lt' or 'le' is defined on a text field.

The text field is a database field that contains language-specific texts. In all other cases you can switch the language-specific sorting off by calling the following method:

io_query_options->set_locale_disabled( abap_true ).

Re: Poor performance in API API_SALES_ORDER_SRV GET metho...

akuller — Fri, 29 Nov 2024 14:58:34 GMT

Hi @rosenpetrov70 somewhere i have a gap in my understanding. are we talking about the standard coding in api_sales_order or about the call or own service, because of the extension?

Re: Poor performance in API API_SALES_ORDER_SRV GET metho...

rosenpetrov70 — Mon, 02 Dec 2024 10:06:17 GMT

Yes, the standard service

Re: Poor performance in API API_SALES_ORDER_SRV GET metho...

i010691 — Fri, 16 May 2025 19:06:04 GMT

How do we disable the language-specific sorting in the standard service? How can I call the method set_locale_disabled in the GET call?