topic Re: Select querry (bypassing buffer) in Application Development and Automation Discussions

Select querry (bypassing buffer)

Former Member — Fri, 14 Mar 2008 05:38:08 GMT

Please tell me how does this BYPASSING BUFFER in the following codes work? & how is the 2nd codes without using Bypassing buffer different from the first?

SELECT SINGLE * FROM T100 INTO T100_WA

BYPASSING BUFFER

WHERE SPRSL = 'D'

AND ARBGB = '00'

AND MSGNR = '999'.

SELECT SINGLE * FROM T100 INTO T100_WA

WHERE SPRSL = 'D'

AND ARBGB = '00'

AND MSGNR = '999'.

Re: Select querry (bypassing buffer)

Former Member — Fri, 14 Mar 2008 05:47:01 GMT

Hi,

... BYPASSING BUFFER

Effect

This addition causes the SELECT statement to avoid the SAP buffering and to read directly from the database and not from the buffer on the application server.

Regards,

Mayank

Re: Select querry (bypassing buffer)

Former Member — Fri, 14 Mar 2008 05:47:40 GMT

Hi,

Using buffered tables improves the performance considerably. Note that in some cases a statement can not be used with a buffered table, so when using these statements the buffer will be bypassed. These statements are:

Select DISTINCT

ORDER BY / GROUP BY / HAVING clause

Any WHERE clause that contains a sub query or IS NULL expression

JOIN s

A SELECT... FOR UPDATE

If you wan t to explicitly bypass the buffer, use the BYPASS BUFFER addition to the SELECT clause.

Reward if helpful.

Regards,

Ramya

Re: Select querry (bypassing buffer)

Former Member — Fri, 14 Mar 2008 05:51:22 GMT

Hi,

In First code

It is reading the data from buffer which is in application server.So performance will improve.Because not accessing the database table.

In select code.

Accessing the table to get the data.Performance is low compare to first one. This can recognize when extracing the huge data.

Reduce the Database Load

Unlike application servers and presentation servers, there is only one database server in your system. You should therefore aim to reduce the database load as much as possible. You can use the following methods:

Buffer Tables on the Application Server

You can considerably reduce the time required to access data by buffering it in the application server table buffer. Reading a single entry from table T001 can take between 8 and 600 milliseconds, while reading it from the table buffer takes 0.2 - 1 milliseconds.

Whether a table can be buffered or not depends its technical attributes in the ABAP Dictionary. There are three buffering types:

Resident buffering (100%) The first time the table is accessed, its entire contents are loaded in the table buffer.

Generic buffering In this case, you need to specify a generic key (some of the key fields) in the technical settings of the table in the ABAP Dictionary. The table contents are then divided into generic areas. When you access data with one of the generic keys, the whole generic area is loaded into the table buffer. Client-specific tables are often buffered generically by client.

Partial buffering (single entry) Only single entries are read from the database and stored in the table buffer.

When you read from buffered tables, the following happens:

An ABAP program requests data from a buffered table.

The ABAP processor interprets the Open SQL statement. If the table is defined as a buffered table in the ABAP Dictionary, the ABAP processor checks in the local buffer on the application server to see if the table (or part of it) has already been buffered.

If the table has not yet been buffered, the request is passed on to the database. If the data exists in the buffer, it is sent to the program.

The database server passes the data to the application server, which places it in the table buffer.

The data is passed to the program.

When you change a buffered table, the following happens:

The database table is changed and the buffer on the application server is updated. The database interface logs the update statement in the table DDLOG. If the system has more than one application server, the buffer on the other servers is not updated at once.

All application servers periodically read the contents of table DDLOG, and delete the corresponding contents from their buffers where necessary. The granularity depends on the buffering type. The table buffers in a distributed system are generally synchronized every 60 seconds (parameter: rsdisp/bufreftime).

Within this period, users on non-synchronized application servers will read old data. The data is not recognized as obsolete until the next buffer synchronization. The next time it is accessed, it is re-read from the database.

You should buffer the following types of tables:

Tables that are read very frequently

Tables that are changed very infrequently

Relatively small tables (few lines, few columns, or short columns)

Tables where delayed update is acceptable.

Once you have buffered a table, take care not to use any Open SQL statements that bypass the buffer.

The SELECT statement bypasses the buffer when you use any of the following:

The BYPASSING BUFFER addition in the FROM clause

The DISTINCT addition in the SELECT clause

Aggregate expressions in the SELECT clause

Joins in the FROM clause

The IS NULL condition in the WHERE clause

Subqueries in the WHERE clause

The ORDER BY clause

The GROUP BY clause

The FOR UPDATE addition

Furthermore, all Native SQL statements bypass the buffer.

Avoid Reading Data Repeatedly

If you avoid reading the same data repeatedly, you both reduce the number of database accesses and reduce the load on the database. Furthermore, a "dirty read" may occur with database tables other than Oracle. This means that the second time you read data from a database table, it may be different from the data read the first time. To ensure that the data in your program is consistent, you should read it once only and then store it in an internal table.

Sort Data in Your ABAP Programs

The ORDER BY clause in the SELECT statement is not necessarily optimized by the database system or executed with the correct index. This can result in increased runtime costs. You should only use ORDER BY if the database sort uses the same index with which the table is read. To find out which index the system uses, use SQL Trace in the ABAP Workbench Performance Trace. If the indexes are not the same, it is more efficient to read the data into an internal table or extract and sort it in the ABAP program using the SORT statement.

Use Logical Databases

SAP supplies logical databases for all applications. A logical database is an ABAP program that decouples Open SQL statements from application programs. They are optimized for the best possible database performance. However, it is important that you use the right logical database. The hierarchy of the data you want to read must reflect the structure of the logical database, otherwise, they can have a negative effect on performance. For example, if you want to read data from a table right at the bottom of the hierarchy of the logical database, it has to read at least the key fields of all tables above it in the hierarchy. In this case, it is more efficient to use a SELECT statement.

Pls. reward if usefulll

Re: Select querry (bypassing buffer)

Former Member — Fri, 14 Mar 2008 05:54:29 GMT

hi,

Single-record buffering is recommended particularly for large tables in which only a few records are

accessed repeatedly with SELECT SINGLE. All the accesses to the table that do not use SELECT

SINGLE bypass the buffer and directly access the database.

If you access a record that was not yet buffered using SELECT SINGLE, there is a database access to

load the record. If the table does not contain a record with the specified key, this record is recorded in

the buffer as non-existent. This prevents a further database access if you make another access with the

same key.

Hope this helps, Do reward.