Lesson 30 Programming Data Retrieval

PROGRAMMING DATA RETRIEVAL:


Whenever a logical database cannot supply your program with all necessary data, you must program database access directly into the program itself. This can be done using either Open SQL or Native SQL statements.Open SQL statements offer several advantages. These include being able to program independent of your underlying database, access to a syntax check, and the use of a local SAP buffer.

Native SQL statements are bound into a program using
EXEC SQL [PERFORMING form.
.
ENDEXEC

Pay attention to the following when programming Native SQL:
Try not to use update operations (INSERT, DELETE, UPDATE)
Group EXEC SQL statements together (in an include) in order to be able to alter them centrally for different database systems
Restrict yourself to Standard SQL 

In order to optimize performance, choose your SQL statements carefully when accessing several (dependent) tables at a time.
To insure optimal database performance:

Follow these general rules:
Keep the amount of selected data as small as possible (use WHERE conditions, for
example)

Keep data transfer between the application server and the database to a minimum (use field lists, for example)

Reduce the number of database inquiries if possible (use table joins instead of nested SELECT statements, for example)

Reduce search size (this optimizes your database index)

Minimize database server load (use SAP buffers, for example).

Always subject programs containing SQL statements to an SQL trace. Which processing sequence is chosen by the Optimizer? Are indices used? If so, are the right ones used?

Is a FULL TABLE SCAN performed? Based on the results of this analysis, you should reprogram your SQL statements (WHERE) conditions, create a database index, or buffer the tables better. To start the SQL trace, use menu path GDA-1.

You can create database views in the ABAP Dictionary. Views (aggregate objects) are application specific and allow you to work with multiple database tables. The link is mapped in an INNER JOIN LOGIC (see slide on INNER JOIN).

From Release 4.0 you can buffer database views. You can then read from views using the SAP buffer on the relevant application server. The same rules apply when buffering views as when buffering tables.

Database view advantages:

Central maintenance
Accessible to all users
Only one SELECT statement is required in the program
One disadvantage of the view is its low flexibility.

In a join, the tables (base tables) are combined to form one results table. The join conditions are applied to this results table. The resulting composite for an inner join logic contains only those records for which matching records exist in each base table.

Join conditions are not limited to key fields.

If columns from two tables have the same name, then you have to ensure that the field labels are unique by prefixing the table name or a table alias.

A table join is generally the most efficient way to read from the database. The database is responsible for deciding which table is read first and which index is used (DB Optimizer).

At LEFT OUTER JOIN, results tables can also contain entries from the designated left hand table without the presence of corresponding data records (join conditions) from the table on the right. These table fields are filled by the database with null values and are then initialized according to ABAP type.

It makes sense to use a LEFT OUTER JOIN when data from the table on the left is needed for which there are no corresponding entries in the table on the right.

The following limitations apply for the Left Outer Join:

you can only have a table or a view to the right of the JOIN operator, you cannot have another join statement
Only AND can be used as a logical operator in an ON condition.
every comparison in the ON condition must contain a field from the table on the right.
if the FROM clause contains an Outer Join, then all ON conditions must contain at least one 'true' JOIN condition (a condition that contains a field from tab1 and a field from tab2).

FOR ALL ENTRIES works with a database in a quantity-oriented manner. Initially all data is collected in an internal table. Make sure that this table contains at least one entry (query sy-subrc or DESCRIBE), otherwise the subsequent transaction will be carried out without any restrictions).

SELECT...FOR ALL ENTRIES IN is treated like a SELECT statement with an external OR condition. The system only selects those table entries that meet the logical condition .
Using FOR ALL ENTRIES is recommended when data is not being read from the database, that is, it is already available in the program, for example, if the user has input the data. Otherwise a join is recommended.

The easiest technical option for reading from multiple (dependent) tables is to use nested SELECT statements. The biggest disadvantage of this method is that for every data record contained in the external loop a SELECT statement is run using the database. This leads to a considerably worse performance in client/server systems.