topic Re: ABAP & DB interactions in Application Development and Automation Discussions

ABAP & DB interactions

Former Member — Mon, 11 Jul 2005 05:55:28 GMT

I am trying to figure out how the R/3 system works under the hood as far as databases go.

More so on reading the help files i located places where there is mention of multiple line updates being faster than single line updates (i.e. the statement UPDATE statement with or without the use of internal tables. )

My question is how is this faster and what is the R/3 system doing in either of the cases.

What is it doing to process the updates especially the ones with internal tables being used.

How many network trips would it make to the database in case this internal table has 20 lines.

If given that the underlying DB is Oracle 8/8i then does Abap use the Collections capablity.

Re: ABAP & DB interactions

jayanthi_jayaraman — Mon, 11 Jul 2005 06:28:08 GMT

Hi,

To know the database retrival,just create two example programs and then compare those in ST05(give trace on-Then activate your program.Then trace off.Then List Trace) and SE30 transaction.

Re: ABAP & DB interactions

Former Member — Mon, 11 Jul 2005 07:46:36 GMT

Hi Gerson,

When you want to update only one record into the database, it doesn't make any difference whether you use an internal table or a normal structure. That, I think is obvious.

Now when you need to update 20 entries to a table, all these entries will be sent to the database server in one go. which means that there is only one cycle between the Application Server and the Database Server.

But if you put the UPDATE statement in a LOOP over the internal table, each time a connection will have to be opened (which is called a cursor) and the single record will be sent to the database server. So, there will have to be as many round trips (between the AS and the DS) as there are number of records in the internal table.

Regards,

Anand Mandalika.

Re: ABAP & DB interactions

Former Member — Mon, 11 Jul 2005 08:01:36 GMT

My question now is how is this then managed at the Database.

How does the sending of 20 records in one go translate into them being updated at the database server?

More specifically i am looking for the low level mechanism in the light of the fact that the AS is DB vendor neutral and my mention of PL/SQL collections prior

was in this context.

The way i understand it

1) The AS would do the 20 record update present in the internal table by itself opening up a cursor with the DB

as against

2) the explicitly coded loop wherein a cursor would be .

opened & closed each time for the 20 records.

I welcome corrections to my understanding of the concept.

Re: ABAP & DB interactions

Former Member — Mon, 11 Jul 2005 08:32:05 GMT

Hi Gerson,

You are right in stating that the Application Server is independent of the underlying database. But there's one point here that we need to see - The cursor would be opened not just when there's an UPDATE, but also when there's a SELECT. The curson, is actually a channel that exists for data to flow between the AS and the DS.

In case of an UPDATE from an internal table, the data will all go from as to DS in a single cursor. After the data reaches the database server, how the actual update has happens is something that we need not worry about (but I think it happens in serial). We are only talking about centralized databases here (no distributed databases).

Once the data reaches the database server, then the application server need not bother about <i>how</i> the update is actually going to be handled by the DS. What it only needs to know is whether the operation succeeded or not. For this reason, I'm not sure whether some particular database(s) is(are) going to handle these updates in a non-serial fashion.

So in summary, the advantage is that there will be reduced number of network trips and the cursor (which is actually a very important resource on the AS) is utilized optimally.

Get back if you have further questions. We can probably discuss more on this if need be.

Regards,

Anand Mandalika.

Re: ABAP & DB interactions

Former Member — Tue, 12 Jul 2005 04:38:35 GMT

Ignore This Reply

Message was edited by: Gerson D'lima

Re: ABAP & DB interactions

Former Member — Tue, 12 Jul 2005 05:33:31 GMT

Thanks Anand

When you say data would be transported in a single cursor i would actually like to understand how.

I am curious to know the underlying transport mechanism given that we are using Oracle as the DB.

From the statement you made i understand that this will be transported using pl/sql collections to the DB.

The other question that comes to my mind is how then can we achieve error reporting for each of the records udpated using the multiple update statements via internal tables.

1) Is this possible ?

Pardon my references to Oracle all the time becoz this is the DB i am most comfortable with.

Re: ABAP & DB interactions

Former Member — Tue, 12 Jul 2005 05:43:34 GMT

Hi Gerson,

As you have rightly pointed out that the App Server is database vendor independent, how the data is physically transferred between the app server and the db server may be implemented somewhat differently for each database.

Let me try further to explain in a database independent way -

When you start up an R/3 System, each application server registers its work processes with the database layer, and receives a dedicated channel for each of them. While the system is running, each work process is a user (client) of the database system (server). You cannot change the work process registration while the system is running. Neither can you reassign a database channel from one work process to another.

The physical connection between the application server and the database server can be TCP/IP based.

And to understand how the system works, you do not need to know about any particular database. You only need to know the concepts of RDBMS. For this reason, I'm unable to confirm whether PL/SQL collections or some other mechanism is used internally.

When you do the update via an internal table, it is not possible to know which records have failed and which records were successfully updated. You will only know whether or not all the records were successfully updated and the number of records that have failed, if any.

Regards,

Anand Mandalika.