Using data base hints in abap reports

If you want to force the database optimizer to choose a particular execution path, use database hints. In R/3 Releases before 4.5A, database hints can only be used in the respective database-specific SQL dialects (native SQL in the parentheses EXEC SQL. … ENDEXEC.).

As of R/3 Release 4.5A, you can use database hints directly from OPEN SQL. To do this, add the addition %_HINTS DBMSDBHINT’ to the end of the SQL statement. Under DBMS, enter the applicable database system (for example, ORACLE, INFORMIX). Under DBHINT, enter the relevant database-specific hint.

If the hint cannot be interpreted by the database system, it is treated as a comment and does not affect the execution of database statements.

A database hint should be incorporated into an SQL statement only as a last option (regardless of whether it is formulated in native SQL or OPEN SQL). Database hints are database-specific. If you replace the DBMS, you must change all the SQL statements that have database hints (and document your changes).

Database hints ignore the database optimizer's strategy of dynamically making selections according to table and index growth and the selectivity of fields. Therefore, an index that is good today could be bad tomorrow.

Reducing the records to be transfered from SAP Dictionary

To reduce the number of data records to be transferred, for each SQL statement you must specify a WHERE clause that is as selective as possible. A SELECT statement without a WHERE condition is an indication of a design error in the program. You must ensure that the number of selected records remains constant with time, especially when selecting on transaction tables (for example, BKPF, BSEG, COBK, COEP, LIPK, MKPF, VBAK, VBAP).


To reduce the amount of data to be transferred, do not formulate conditions for selecting data using a CHECK statement within a SELECT … ENDSELECT. Instead, formulate conditions as part of a WHERE clause.

Always specify all known conditions in the WHERE clause. Without one, the DBMS cannot use an index to optimize a statement.

To make reusing SQL statements possible, and thus to optimally utilize the DB SQL cache, always adhere to the order of fields specified in the ABAP Dictionary when formulating SELECT clauses and WHERE clauses.


Reducing the columns to be transfered in a program

If you use SELECT *, it is often the case that more data than necessary is transferred from the database server to the application server. If you require the contents of only a few columns, always list the table fields individually in a field list. If the number of fields can be reduced by more than half, creating a field list makes sense.

In this context, the variant SELECT .. INTO TABLE itab PACKAGE SIZE n ENDSELECT is important. PACKAGE SIZE n means that the data is transferred to the internal table itab in packages of n records.

If you need different projections on a database table at different places in a program, read all the required fields at once from the database, buffer them in an internal table, and then distribute the data within the program.

59

related post

sap index design in data base

COMMUNICATION PART ONE SAP ABAP

COMMUNICATION PART TWOSAP ABAP

COMMUNICATION PART THREESAP ABAP

COMMUNICATION PART FOURSAP ABAP

COMMUNICATION PART FIVESAP ABAP

No comments:

Post a Comment