Buffuring

[Former Member]

Hi Everyone,

I am in performance tunning team . The following select query is fetching fata from data base instead of buffer even then the table is fully buffered .pLEASE SUGGEST SOME WAYS

SELECT SINGLE * FROM crmc_erms_cat_as "#EC NEEDED

INTO lv_asp_buf-asp "#EC NEEDED

WHERE (lv_wc). "#EC NEEDED

[Former Member]

Hi

To the Best of my knowledge we can not interfere in this case, this is handled by the DB interface only.

Please go through the BUFFER SYNCHRONIZATION concept for more info.

when the buffer is out of sync with the DB then only it goes to DB otherwise it gets it from Buffer only.

Contact your BASIS for any further settings they might have in this regard

CHeers

Ram

[Former Member]

first you must execute is at least twice, and the second one can use the buffer.

Then check SQl -trace, if it really goes to the database and not to the buffer then it appears there.

And last but not least, provide at least an example of the actual WHERE-condition, see detail in SQL trace.

Check ST10 about status of the table, mabe it is too large to be in the buffer or too often invalidated.

Siegfried

[Former Member]

Hi Arun,

In order to use buffering, you have to check that in your where-clause you are using all key-fields specified in SE11 on your table in order to use the buffer!

Please have a look to what is contained in lv_wc and check that you have the first key fields.

Best regards!

Samuel

[Former Member]

Hi arun purushothaman,

The following statements do not take advantage of any SAP table buffering. What

this means is that even if a table is buffered (either resident/full, generic, or partial),

executing one of the above statements against that table will bypass the buffer and

go directly to the version of the table stored in the database in order to resolve the

statement.

SQL Statements Bypassing Buffer

1. SELECT ...BYPASSING BUFFER

2. Any SELECT from a VIEW (except a projection view).

3. SELECT FOR UPDATE...

4. Aggregate functions (e.g. COUNT, MIN, MAX, SUM, AVG).

5. SELECT DISTINCT...

6. WHERE...IS NOT NULL

7. ORDER BY (other than primary key)

8. GROUP BY or HAVING

9. Sub queries

10. Joins

11.Any native SQL Statement (EXEC SQL)

Regards,

José

Moderator message - See Thomas's remark below.

Edited by: Rob Burbank on Sep 24, 2009 10:41 AM

[ThomasZloch]

https://wiki.sdn.sap.com/wiki/display/ABAP/Performance+Tuning?showChildren=false

Towards the bottom. You should quote your source as to not look like a copy/paster.

Thomas

[Former Member]

which is an uncomplete list: the most complete list I know is the following.

I have added 1,2,3,710, 11,16, and 17

1. Any select which does not contain all buffered fields with equal conditions,

i.e if single record then complete key is need, if generic then generic key,

nothing need if fully buffered

2. Programmed IN-List with more than one condition, i.e. field1 IN ( 'a', 'b' ...)

3. SELECT from single-record buffer without key word SINGLE does not

work before Release 7.02/7.20

4. SELECT DISTINCT...

5. ORDER BY (other than primary key)

6. Aggregate functions (e.g. COUNT, MIN, MAX, SUM, AVG)

and also GROUP BY or HAVING

7. FOR ALL ENTRIES together with single record buffer

8. Sub queries

9. Any Join or View which is based on join

10. SELECT ... WHERE buf_field = other_field

(can be rewritten as self-join)

11. CLIENT-SPECIFIED but not client condition provided

12. WHERE...IS NOT NULL

13.Any native SQL Statement (EXEC SQL)

14. SELECT ...BYPASSING BUFFER

15. SELECT FOR UPDATE...

16. Any table change (INSERT, UPDATE, DELETE, MODIFY)

17. First execution which loads the buffer

The last one is added for completeness, if there was never an execution which loaded the buffer, then

it can not be used. This is often forgotten. Refers to measurement not to coding.

Siegfried

[Former Member]

Hi,

Please check that you are using all key fields in where clause, if not then create a secondary index for the fields in the selection criteria.

[Former Member]

> Please check that you are using all key fields in where clause, if not then create a secondary index for

> the fields in the selection

in relation with buffers, is this nonsense

[Former Member]

Check the buffering type. If it is generic type then check the no of key fields and make sure that your query qualifies for buffering.

Regards,

Prashant