SQL Examples

Select those MM tables which are language dependant (For example you want to translate the customizing in two languages)

report zbctcb96.

tables: dd03l, tadir.

data: counter type i value 1.

select * from tadir where pgmid eq 'R3TR' and

object eq 'TABL' and

devclass like 'M%'.

select * from dd03l where tabname eq tadir-obj_name and

( fieldname like 'SPRA%' or fieldname like 'LANG%' ).

write: / counter, dd03l-tabname, dd03l-fieldname.

add 1 to counter.

exit.

endselect.

endselect.

The TADIR contains the development objects. All the 'table' objects are selected which are in an MM development class (MM development classes begin with 'M').

The DD03L table contains the fields of the database tables. Just those tables are selected, which has a field beginning with either 'SPRA' or 'LANG'. ( 'language' is 'Sprache' in german).

Performance considerations in this example:

How frequently runs the report?

This report was used only once . I have tried to run it in dialog and finished without 'time out error'. Performance optimization is not necessary.

Is DD03L and TADIR buffered and how many records are in the tables?

You find it in /Development Workbench/ABAP 4 Dictionary, Table Display, /Goto/Technical settings

Table

Buffering

Expected size in records

DD03L

No buffering

310,000 - 24,000,000

TADIR

Buffering single records

92 000 - 370 000

Both are large tables with no or poor buffering. A good performance can be only expected when you use indexed fields of the tables.

Are the selection fields indexed?

The primary key fields are always indexed. Other fields are indexed through additional index files. From the Table Display, /Goto/Indexes.

Table

Index

Indexed fields

DD03L

DD03L_____5__X

TABNAME

AS4LOCAL

FIELDNAME

TADIR

TADIR__1

DEVCLASS

This means that both tadir-devclass and dd03l-fieldname are indexed. However it doesn't help, because in the WHERE clause the selection fields does not fully match the index fields.

Are there nested SELECT statements?

An example of nested SELECT statements:

select * from tstc.

select * from tstct where sprsl eq 'E' and tcode eq tstc-tcode.

[..]

endselect

[..]

endselect.

No. In nested SELECTs every ABAP command is executed n*m times, where n and m are the numer of rows in the first table and second table. In this example, after the SELECT statement an EXIT is used which quits after processing one record.

Bence Toth