-
FOR ALL ENTRIES
的效率问题
FOR ALL ENTRIES vs DB2 JOIN
All abap programers and
most of the dba's that support abap programmers
are
familiar
with
the
abap
clause
all
entries
Most
of
the
web
pages
I visited recently, discuss 3 major
drawbacks of the
clause:
1. duplicate rows are automatically
removed
2. if the itab used in the
clause is empty , all the rows in the source
table will be selected .
3.
performance degradation when using the clause on
big tables.
In
this
post
I'd
like
to
shed
some
light
on
the
third
issue.
Specifically
i'll discuss
the use of the
tables in the abap code
instead of in db2.
Say for
example you have the following abap code:
Select * from mara
For all
entries in itab
Where matnr = itab-
matnr.
If the actual
source of the material list (represented here by
itab) is
actually another database
table, like:
select matnr from mseg
into corresponding fields of table itab
where ?
Then
you could have used one sql statement that joins
both tables.
Select t1.*
From mara t1, mseg t2
Where
=
And T2?.
So
what
are
the
drawbacks
of
using
the
all
entires
instead
of
a
join
?
At
run
time
,
in
order
to
fulfill
the
all
entries
request,
the
abap
engine
will
generate
several
sql
statements
(for
detailed
information
on
this refer to note
48230).
Regardless of which method the
engine uses
(union all,
records,
the
abap
engine
will
break
the
itab
into
parts,
and
rerun
an
sql
statement
several
times
in
a
loop.
This
rerun
of
the
same
sql
statement
,
each
time
with
different
host
values,
is
a
source
of
resource
waste
because
it may lead to re-
reading of data pages.
returing
to
the
above
example
,
lets
say
that
our
itab
contains
500
records
and
that
the
abap
engine
will
be
forced
to
run
the
following
sql
statement
50 times with a
list of 10 values each time.
Select *
from mara
Where matnr in ( ...)
Db2
will
be
able
to
perform
this
sql
statement
cheaply
all
50
times,
using
one
of
sap
standard
indexes
that
contain
the
matnr
column.
But
in
actuality,
if you consider
the wider picture (all 50 executions of the
statement),
you will see that some of
the data pages, especially the root and
middle-tire index pages have been re-
read each execution.
Even
though
db2
has
mechanisms
like
buffer
pools
and
sequential
detection
to try to minimize the i/o cost of such
cases, those mechanisms can only
minimize the actual
i/o
operations
,
not the cpu
cost of re-reading them
once
they
are
in
memory.
Had
you
coded
the
join,
db2
would
have
known
that
you
actually
need
500
rows
from
mara,
it
would
have
been
able
to
use
other
access
methods, and potentially consume less getpages i/o
and cpu.
In other words ,
when you use the
coding a join , you
are depriving the database of important
information
needed
to
select
the
best
access
path
for
your
application.
Moreover,
you
are
depriving
your
DBA
of
the
same
vital
information.
When
the
DBA
monitors
& tunes the system, he (or she) is less
likely to recognize this kind of
resource waste. The DBA will see a
simple statement that uses an index ,
he is less likely to realize that this
statement is executed in a loop
unnecessarily.
In
conclusion
I
suggest
to
twice
before
using
the
all
entries
clause and to
evaluate the use of database views as a means to:
a. simplify sql
b. simplify
abap code
c. get around open sql
limitations.
Omer Brandis
DB2 DBA & SAP Basis professional (and
all around nice guy)
omerb@
另外，附上
NOTE
48230
Summary
Symptom
Performance
problems with the open SQL statement
ENTRIES ...
Other
terms
FOR_ALL_ENTRIES
Reason and Prerequisites
The
open
SQL
statement
...
FOR
ALL
ENTRIES
...
is
an
ABAP-specific
enhancement of
the SQL standard. This variant of the SELECT
statement
allows the ABAP programmer
to join an
internal program
table
with one or
several
database tables. (For a detailed description of
that statement
type please refer to the
corresponding ABAP documentation.)
Since there is no analogous statement
in the SQL standard, the open SQL
statement has to be mapped from the
database interface of the ABAP
environment to one or several
semantically equivalent SELECT statements
which can be processed by the DB
platform. Several profile parameters
allow a definition of how the database
interface should carry out this
mapping
with regard to the database. This note describes
the parameters
that can be used to
control the
and their effect.
Solution
The
parameters mentioned in this note have
considerable effects on most
of the
critical database commands and influence the
performance of the
whole system to a
great extent. For this reason, before changing the
parameters described in this note, a
detailed problem analysis by
experienced
SAP
consultants
or
the
support
team
is
required.
Please
note
in
particular that changing the parameters may often
solve a local
performance
problem
but
it
may
also
cause
a
still
bigger
problem
to
occur
at another place. For this reason,
prior to changing the profile
parameters
-
which
has
a
global
effect
on
all
statements
-
you
should
check
first whether the performance problem
might be caused by one or two
positions
in the corresponding application which can be
corrected by a
local change of the
critical SQL statements.
The following profile parameters are
available:
rsdb/prefer_join (ab Release 7.0)
If
you
set
this
parameter
to
the
SELECT
...
FOR
ALL
ENTRIES
is implemented using
a join.
Note that
this
variant is only supported
by
the DB6 (DB2 UDB) and MS SQL Server
database platforms.
rsdb/prefer_union_all
You can override this parameter using
rsdb/prefer_join = 1.
The following
remarks relate to rsdb/prefer_join = 0.