Efficient Database

Efficient Database
Programming with ABAP

Learning Objectives

As a result of this workshop, you will
be able to:
Explain the communication between database and
application server
Analyze bottlenecks in database programming
Understand how table buffers and indices work
Use Open SQL for efficient database access

© 2004 SAP AG, ABAP210 2

Agenda

WAS and Database ArchitectureABAP Open SQL Overview
How to Identify Expensive SQL
Rules for Better SQL Programming
Theory and
Hands-On

Summary

© 2004 SAP AG, ABAP210 3

Performance of Business Transactions

General rule:
The performance of a business transaction is primarily
determined by its DB accesses.

DB
Application
GUI

© 2004 SAP AG, ABAP210 4

WAS and Database Architecture

Data transferbetween DB server
and application server(s)

DB
WP

DB Server

User
communication

WP

Application Server(s)

Frontend(s) / Presentation Server(s)

© 2004 SAP AG, ABAP210 5

local
data

DB
cache

WAS and Database Architecture
App Server
memory
consumption

Table Buffer
App Server
CPU consumption
R/3 work process

R/3 work process

R/3 work process

DB workprocess

DB work process

DB work process

LAN
communication

DB CPU
consumption
DB memory
consumption

Database cache
Physical I/O

Operating system
Database files
© 2004 SAP AG, ABAP210 6

Database
Service
processes

Table Buffering: Types

Single-record buffering
(all key fields)

Generic buffering
(one key field)
data

Full buffering
(no key fields)

key1key2

key3

key1

key2

key3

key1

key2

key3

key1

key2

key3

001

A

2

001

A

2

001

A

2

001

A

2

001

A

4

001

A

4

001

A

4

001

A

4

001

B

1

001

B

1

001

B

1

001

B

1

001

B

3

001

B

3

001

B

3

001

B

3

001

B

5

001

B

5

001B

5

001

B

5

002

A

1

002

A

1

002

A

1

002

A

1

002

A

3

002

A

3

002

A

3

002

A

3

002

A

6

002

A

6

002

A

6

002

A

6

002

A

8

002

A

8

002

A

8

002

A

8

002

B

1

002

B

1

002

B

1

002

B

1

002

B

2

002

B

2002

B

2

002

B

2

002

B

3

002

B

3

002

B

3

002

B

3

002

C

0

002

C

0

002

C

0

002

C

0

002

C

1

002

C

1

002

C

1

002

C

1

002

D

5

002

D

5

002

D

5

002

D

5

003

A

2

003

A

2

003

A

2

003

A

2

003

A

3

003

A3

003

A

3

003

A

3

003

A

6

003

A

6

003

A

6

003

A

6

003

B

2

003

B

2

003

B

2

003

B

2

003

B

4

003

B

4

003

B

4

003

B

4

003

C

5

003

C

5

003

C

5

003

C

5

003

D

2

003

D

2

003

D

2

003

D

2

003

D

6003

D

6

003

D

6

003

D

6

003

D

8

003

D

8

003

D

8

003

D

8

© 2004 SAP AG, ABAP210 7

data

Generic buffering
(two key fields)

data

data

Table Buffering: Invalidation Policy

Local Buffer
Local Buffer
Full Buffering:
Full Buffering:
In case of a Workarea-Update, the corresponding row is updated in
In case of aWorkarea-Update, the corresponding row is updated in
the buffer.
the buffer.
Otherwise, the entire buffer of table t tis invalidated.
Otherwise, the entire buffer of table is invalidated.
Generic Buffering:
Generic Buffering:
In case of a Workarea-Update, the corresponding row is updated
In case of a Workarea-Update, the corresponding row is updated
in the buffer. If there is a change in only...