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 DBMS ‘DBHINT’ 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.
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
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