performance drawbacks of SAP logical databases

hello all,
recently I have been asked to review a few sap standard logical databases from a performance perspective. ( transaction se36).

so far, I have found a few basic performance flaws in the LDB's implementation :1. selecting all tables columns.
2. predefined join order
3. selecting non-relevant rows.

selecting all columns of a table.LDB's, at least the automatically generated ones, usually select all columns of each database table they must access.
this does not adhere to most programming guide lines, and has been a cause for concern to some developers,

fortunately, the overhead for selecting columns your end user doesn't really need are usually insignificant, ( at least while you don't need to sort the data)
that's because usually the most significant part of a database query is finding the rows you need in the database hay barn.

predefined join order & selecting non-relevant rowsin most of the LDB's I've reviewed, the joining of data from several sources(tables) is done manually/implicitly in the abap code.
first table A's data is selected, then rows of table B are selected ( and matched ) …and onto table n.
This means that the join order of the tables is predefined, regardless of the data actually requested at run time by the user.
had the programmer coded a "complex" sql statement like :
select columns
from a join b join c …
where ..join conditions…
and a.column in (user input)
and b.column in (user input)
and c.column in (user input)

the database would have been able to choose between several different join methods and joining orders in order to achieve the most efficient join.
that usually means starting the join with the table who will return the least number of rows ( this largely depends on the users input) and then making the best of the available indexes and so forth.

another benefit of using "comlex" join sql statments is that your abap program will not fetch data rows that are not actually needed.
whereas, the standard LDB program's logic may forces it to fetch rows from table A even though they may later be filtered out after joining to table B and applying its filter predicates (where conditions).

fetching rows you don't need can be can be significant from a performance perspective: cpu , network transfer , memory consumption(allocation) on the sap application server - > all of which effect the transactions elapsed time.

omer brandis"he who doesn't win the easy points,
is dooming himself to fight hard for all the others"

Disclaimer: Blog contents express the viewpoints of their independent authors and
are not reviewed for correctness or accuracy by
Toolbox for IT. Any opinions, comments, solutions or other commentary
expressed by blog authors are not endorsed or recommended by
Toolbox for IT
or any vendor. If you feel a blog entry is inappropriate,
click here to notify
Toolbox for IT.