topic SQL Expression and SAP buffer in Application Development and Automation Discussions

SQL Expression and SAP buffer

nomssi — Sat, 29 Jul 2017 19:25:53 GMT

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

Re: SQL Expression and SAP buffer

Domi — Sat, 29 Jul 2017 21:31:50 GMT

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.htm

Re: SQL Expression and SAP buffer

nomssi — Mon, 31 Jul 2017 22:29:26 GMT

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

Re: SQL Expression and SAP buffer

retired_member — Tue, 01 Aug 2017 05:29:42 GMT

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."