07-29-2017 8:25 PM
Do queries with SQL expressions bypass the SAP buffer?
As table TCURX is fully buffered, I would expect this query to use the SAP buffer:
SELECT SINGLE currdec INTO @number FROM tcurx
WHERE currkey EQ @currency.
IF sy-subrc = 0.
shift = 2 - number.
ENDIF.
but what about this one?
SELECT SINGLE ( 2 - currdec ) INTO @shift FROM tcurx
WHERE currkey EQ @iv_currency.
If this query is buffered, does it means the SQL expression is evaluated by the database interface and not calculated in the database system?
JNN
07-29-2017 10:31 PM
Hi
No, SQL Expressions are (mostly) passed to the database, see: https://help.sap.com/doc/abapdocu_750_index_htm/7.50/en-US/index.htm?file=abapsql_expr.htm
eg. this is done with buffer:
SELECT SINGLE 'EUR5' AS currkey, currdec as currdec FROM tcurx
WHERE currkey EQ 'EUR5'
INTO @DATA(curr_decimals_w_curr).
this is done on database:
SELECT SINGLE 'EUR5' AS currkey, currdec * 1 as currdec FROM tcurx
WHERE currkey EQ 'EUR5'
INTO @DATA(curr_decimals_w_curr).
You can check the buffer usage easily with transaction ST10.
There are also some other restrictions for buffering: https://help.sap.com/doc/abapdocu_750_index_htm/7.50/en-US/index.htm?file=abenbuffer_restrictions.ht...
07-29-2017 10:31 PM
Hi
No, SQL Expressions are (mostly) passed to the database, see: https://help.sap.com/doc/abapdocu_750_index_htm/7.50/en-US/index.htm?file=abapsql_expr.htm
eg. this is done with buffer:
SELECT SINGLE 'EUR5' AS currkey, currdec as currdec FROM tcurx
WHERE currkey EQ 'EUR5'
INTO @DATA(curr_decimals_w_curr).
this is done on database:
SELECT SINGLE 'EUR5' AS currkey, currdec * 1 as currdec FROM tcurx
WHERE currkey EQ 'EUR5'
INTO @DATA(curr_decimals_w_curr).
You can check the buffer usage easily with transaction ST10.
There are also some other restrictions for buffering: https://help.sap.com/doc/abapdocu_750_index_htm/7.50/en-US/index.htm?file=abenbuffer_restrictions.ht...
07-31-2017 11:29 PM
Hello Dominik,
thanks for your feedback.
I could enable Buffer Trace in transaction ST05 and filter for object TCURX to evaluate buffer usage:
The SAP buffer access is at least 50x faster than DB access (caveat: buffer creation time not included).
I can only display cummulated TCURX table statistics in transaction In ST10. Am I missing something?
I think the SAP Buffer - Restrictions section should clearly state that SQL expressions bypass the SAP buffer most of the time.
best regards,
JNN
08-01-2017 6:29 AM
For 7.52 the documentation "SAP Buffer - Restrictions" was rewritten and it says:
"In the SELECT list, only columns, host variables, and host expressions can be used. No other SQL expressions or aggregate expressions can be used."