n You can use aggregate functions (COUNT, SUM, MAX, MIN, AVG) to perform calculations using the DBMS. Only use aggregate functions if they significantly reduce the quantity of data records to be transferred.

n Aggregate functions are usually used used with a GROUP BY clause. SQL statements that contain aggregate functions bypass R/3 table buffers. (Note that this can be a disadvantage if tables are buffered.)

n Field values are processed differently, depending on whether they are in ABAP or at database level. The database uses a different method than ABAP for rounding data (for example, when converting F to I). The database recognizes the value NULL. ABAP does not recognize the value NULL.

For the aggregate function AVG, use data type F (number with floating point) for the target field. For the aggregate function SUM, make the data type of the target field longer than the data type of the source field, to allow for overflow.

Using Having Clause in a sql query:

n To apply a logical condition to the groups defined in the GROUP BY clause, use HAVING with a SELECT statement. The database determines the resulting quantity, that is, the data quantity to be transferred depends on the selectivity of the HAVING clause.

n Using HAVING clauses is similar to using aggregate functions. HAVING clauses create an extra load on the database. Therefore, only use HAVING clauses if they significantly reduce the quantity of data records to be transferred. Before you use a HAVING clause, check whether the condition can be specified in the SELECT clause instead.

Using Array Fetch:

n For SELECT .. ENDSELECT and ARRAY FETCH, the same number of data records must be transferred from the database server to the application server. Since data records are transferred in 32 KB blocks, the transfer load (that is, the number of fetches) is the same for both variants.

n However, an ARRAY FETCH is preferable to a SELECT ... ENDSELECT loop, because data records are transferred per record from the database interface to the ABAP program in a SELECT .. ENDSELECT. Whereas, in an ARRAY FETCH, the data records are transferred to the ABAP program in a block.

Using up to n rows in sql query:

n There are two ways to read a fixed number of data records. You can transfer the data records to the application server using SELECT … ENDSELECT and then determine if the required number of rows has been read. However, using SELECT … UP TO n ROWS is preferable, because only the requested number of data records are transferred from the database to the application.

n In the second version, the UP TO n ROWS addition ensures that only the required data quantity is read from the database and transferred to the database interface.

68

RELATED POST

ACCESSING INDIVIDUAL TABLES IN SAP ABAP


LESSON 9 ALE IDOC CHANGE POINTERS

LESSON 10 SENDING IDOC VIA STANDARD R3 SYSTEM

LESSON 11 IDOC OUTBOUND TEIGGER PART TWO

LESSON 12 IDOC OUTBOUND TRIGGER PART ONE

LESSON 13 ABAP IDOC INBOUND BASIC TOOLS

SELECT FOR ALL ENTRIES:

With SELECT FOR ALL ENTRIES, you can divide SELECT statements into loops. This variant enables you to create a JOIN between an internal table and a database table. In the example above, only the records from table KKNA1 are read that have customer numbers in the internal table g_itab_vvbak.

The following problems occur with SELECT FOR ALL ENTRIES:

You cannot have an empty driver table. If the driver table is empty, selection is not limited. Note that if the WHERE clause contains conditions that do not refer to the internal driver table, these conditions are not evaluated either.

Duplicate table entries must be deleted from the internal driver table before the SELECT FOR ALL ENTRIES is executed. If they are not deleted, identical data is read unnecessarily from the database.

If SELECT FOR ALL ENTRIES is used, SQL statements are divided into several SELECT statements by the database interface. The individual SELECT statements are processed on the database and the results are buffered in the database interface. The database interface eliminates duplicate data records from the resulting set, and transfers the overall results to ABAP.

Each SELECT statement processed on the database is executed as a concatenation of individual accesses (for example, index range scans or index unique scans). Concatenation is performed over the entries in the internal driver table. Since SELECT FOR ALL ENTRIES can become very complex, you should avoid combining them with SELECT-OPTIONS or RANGES tables.

Exactly how the WHERE clauses of individual SELECT statements are executed by the database depends on the type of database. The link is generated, for example, using an OR operation as IN list.

USING RANGE:

You can use RANGES tables in ABAP. You can define RANGES tables with the ABAP statements SELECT-OPTIONS and RANGES. These two statements implicitly define an internal table with the fields SIGN, OPTION , LOW and HIGH (see R/3 documentation). The end user at the selection screen dynamically fills a RANGES table defined with SELECT OPTION. The program determines how the RANGES table is filled.

The database interface converts the rows in a RANGES table so that they can be read by the DBMS, and links them with OR. The SQL statement produced is then transferred to the database.

The RANGES table can contain rows with complex expressions (for example, BT => BETWEEN, CP => LIKE). The SQL statement that results from a RANGES table can therefore be complex and difficult for the DBMS to process.

If the program determines how RANGES tables are filled, you must limit the number of entries. If the RANGES table is too large, a complex database statement is generated when the OR or IN list is evaluated. This statement may take a long time to be evaluated on the database. The maximum possible length of the database statement depends on the database (for example, with ORACLE 32 bit version, the maximum is 32 KB). If the maximum length is exceeded, an ABAP dump may be generated

USING AGGREGATE FUNCTIONS:

You can use aggregate functions (COUNT, SUM, MAX, MIN, AVG) to perform calculations using the DBMS. Only use aggregate functions if they significantly reduce the quantity of data records to be transferred.

Aggregate functions are usually used used with a GROUP BY clause. SQL statements that contain aggregate functions bypass R/3 table buffers. (Note that this can be a disadvantage if tables are buffered.)

Field values are processed differently, depending on whether they are in ABAP or at database level. The database uses a different method than ABAP for rounding data (for example, when converting F to I). The database recognizes the value NULL. ABAP does not recognize the value NULL.

For the aggregate function AVG, use data type F (number with floating point) for the target field. For the aggregate function SUM, make the data type of the target field longer than the data type of the source field, to allow for overflow.

65
RELATED POST

USING DATA HINTS IN ABAP SQL QUARY

The links day wise are

SAP SCRIPTS PART 1

SAP SCRIPTS PART 2

SAP SCRIPTS PART 3

SAP SCRIPTS PART 4

SAP SCRIPTS PART 5

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.

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

SAP SCRIPTS PART 6

SAP SCRIPTS PART 7

SAP SCRIPTS PART 8

SAP SCRIPTS PART 9

SAP SCRIPTS PART 10

SAP SCRIPTS PART 11

SAP SCRIPTS PART 12

SAP SCRIPTS PART 13

MISSING CRITICAL OPERATORS IN WHERE CLAUSE

If fields in the WHERE clause are specified with operators NOT or <>, these WHERE conditions cannot be used for a search over a database index. You should therefore formulate SQL statements positively wherever possible.

If a positive formulation cannot be used, for example because the IN list would be too long, you should still specify the WHERE condition with NOT, in order to reduce the amount of data to be transferred. An index search will not be performed, but the amount of data returned will be smaller.

On ORACLE databases, a WHERE condition with BETWEEN is evaluated together with the costs for reading 5 % of all index blocks and table blocks, regardless of the size of the BETWEEN interval. If the BETWEEN interval is small enough, you can replace SELECT field list WHERE field BETWEEN value1 AND value5 by SELECT field list WHERE field IN (value1, value2, value3, value4, value5) or by SELECT field list WHERE field IN ranges table.

On ORACLE databases, a WHERE condition with LIKE, > or < field =" ‘999’.">

A field specified with LIKE can narrow the selection of database indexes and be used for a search over a database index only if it does not begin with the wildcard character ‘_’ or ‘%’. In ABAP, the wildcard ‘+’ is used for any character, and ‘*’ is used for any character string. For database accesses, however, the characters ‘_’ and ‘%’ are used.

WHERE conditions with critical operators that are linked to the WHERE clause with OR can be particularly problematic. Since BETWEEN is calculated as costing 5% and LIKE is calculated as costing 10%, the cost-based database optimizer opts for a full table scan after a certain number of corresponding WHERE conditions, instead of using an index.

SORTING

If you want the result of an SQL statement to be returned in a sorted order, use an ORDER BY clause on the database, or use the ABAP statement SORT itab in the program.

Since the database is a central resource and is therefore not scalable, sort in ABAP wherever this is possible. However, if the same database index can be used for sorting and selecting data records (according to the WHERE clause), you can also sort on the database.

CHANGING THE INDEX DESIGN

If you establish that an expensive SQL statement is triggered by an SAP program, first search for R/3 Notes in SAPNet (in a full text search, specify the name of the table or view, as well as the word 'performance'). If you cannot find an applicable R/3 Note, open a problem message in SAPNet.

In your own programs, if you find expensive SQL statements on SAP standard tables with transaction data, avoid creating secondary indexes wherever possible. Transaction data tables usually grow linearly with their indexes. Therefore, a search using a secondary index becomes less effective as time goes on. You should look for alternative methods of access, for example, using index tables or matchcode tables.

Before you create a secondary index to optimize an SQL statement, check whether you can adapt the SQL statement to use a secondary index that already exists. Before you change an index, you must perform a selectivity and interference analysis.

In some cases, deleting a secondary index makes sense. For example, if a secondary index is not used, or if a secondary index is a subset of another index, updating it causes unnecessary work for the database. Before you delete an index, you must perform an interference analysis.

As a rule, avoid secondary indexes over SAP basis tables (for example, tables NAST, D010, D020, or tables beginning with DD).

RELATED POST

ABAP DATA BASE ACCESS PROBLEMS PART TWO
Outbound process overview
Outbound process via message control
EDI outbound process
EDI inbound process overview
Inbound process via function module
Inbound process via work flow
EDI subsystem part one and two

OLD TABLE INDEX STATISTICS

To ensure that the cost-based database optimizer functions properly, the table and index statistics must be updated regularly. The database optimizer uses these statistics as a basis for selecting the access strategy used to execute a particular SQL statement.

The database administrator must update the table and index statistics regularly. To find out when the last update was performed, from the SAP standard menu choose Tools >> CCMS >> DB administration >> DBA Planning Calendar, or use Transaction DB13. You must ensure that the last update was not performed more than a week ago, and that it was performed successfully (refer to the log).

For making changes to index design, we recommend that roles and responsibilities are distributed as follows. The ABAP programmer designs the database indexes to support SQL statements. In a production system or test system, only the database administrator should be responsible for creating database indexes. The database administrator must verify whether the programmer's index design is correct.

When database indexes are created, the table and index statistics must be updated for the relevant tables. If the index design is correct, you can transport the database index to the production system at a time of low system activity, and then update the table and index statistics in the production system.

MISSING WHERE CONDITIONS

The way in which the database optimizer selects and uses database indexes depends on, among other things, the WHERE conditions specified in the SQL statement. The order of the WHERE conditions in the SQL statement is not important (note that this is not the case with the order of the fields in the database index).

The index search string is concatenated over the fields that are specified without gaps and with '=' in the WHERE condition. The index blocks to be checked are are selected using the index search string.

Using the addition CLIENT SPECIFIED incorrectly can lead to ineffective use of a database index. If you use CLIENT SPECIFIED, specify a WHERE condition for MANDT.

33
RELATED POST

PROBLEMS WITH DATA BASE ACCESS PART ONE
EDI Subsystem architecture and mapping
EDI basic components configuration part one and two
How to set RFC destination part two and part one
Defining a port for message control
EDI Outbound process
Inbound process
Partner profiles configuration part one and two
Overview of outbound parameters part one and two
EDI outbound parameters for message control

Optimizing SQL statements only makes sense if the R/3 System and database system are correctly configured. This is the responsibility of the R/3 System administrator and the database administrator respectively. Technical problems may have an effect on the whole system (for example, wrongly set parameters, communication problems, old table and index statistics), or may only affect SQL statements on certain tables (for example, missing or fragmented indexes).

To avoid technical problems:

ŸFollow the R/3 installation instructions and refer to R/3 Notes

ŸImplement the recommendations given by the SAP standard services GoingLive and EarlyWatch

Communication problems may occur between the application server and database server. Parameters on the database interface may be wrongly set, and thus also cause problems (for example, incorrect value for rsdb/max_blocking_factor, see recommendations for SELECT FOR ALL ENTRIES).

On some databases (for example, Oracle and Informix), fragmented indexes may occur. The term fragmented indexes means that index blocks are less than 50% full. Fragmented indexes can occur if a large number of table records are deleted and then a large number of table records are inserted. If you discover that an index is suitable for an SQL statement, but that executing the SQL statement causes heavy database system load, your database administrator should check whether a fragmented index (database-specific) is causing the problem.

The R/3 Workload monitor gives you an overview of the general state of your R/3 System. To view the initial screen of the R/3 Workload monitor, from the SAP standard menu choose Tools >> CCMS >> Control/Monitoring >> Performance menu >> Workload >> Analysis, or use Transaction ST03.

From this screen, choose Performance database. In the dialog box that appears, first select the server to be checked, then the time period (for example, previous weeks) and the examination period (for example, previous week).

If the average database response time per transaction step is greater than 600ms, the cause is very probably a fundamental R/3 or database problem. If this is the case, the results of the SQL performance trace are not reliable. Your R/3 System administrator or database administrator should therefore first solve the problem, and then you can repeat the analysis.

Missing indexes

Database reorganization or deliberately deleting database objects at the operating system level can cause inconsistencies between the indexes in the ABAP Dictionary and the indexes on the database.

You can find these inconsistencies by using the database monitor. To view the initial screen of the database monitor, from the SAP standard menu choose Tools >> Administration >> Monitor >> Performance >> Database >> Activity >> Detail analysis menu >> State on disk, or use Transaction DB02. For an overview of indexes that are missing in the database or not found in the ABAP Dictionary, choose Missing indexes.

If you find indexes that are missing on the database, inform your database administrator. Database indexes can be activated from this screen or from the ABAP Dictionary, and thus created on the database. Database indexes that exist on the database but not in the ABAP Dictionary can be created in the ABAP Dictionary. They are transferred to the database when they are activated. The index that already exists on the database is then overwritten. After you have changed the index design for a table, you must create new index statistics for the changed indexes.

Only make changes to database indexes at times of low system activity, because the database locks the database table while the indexes are being created.

RELATED POST

DATA BASE JOINS PART TWO
EDI inbound parameter views
Partner profile maintenance
Message control Configuration
Message control architecture part one and two
EDI output types part one and two
Message control and control table
Working of message control part one and two
Message control set up
Work flow management
Using work flow