NonLocal Nested Reading DB OP (SELECT) found

[raj2]

Hi All,

I get errors in SCI testing.

Error: NonLocal Nested Reading DB OP (SELECT) found

How to avoid these type of errors in programming level

Regards,

Raj

[Ruthiel]

Hello!

You should be doing several database reading inside a loop.

Let me paste you the description of the error:

Appl. Comp. Check / Check Class / Message Code

BC-ABA-LA / CL_CI_TEST_DB_OPS_IN_LOOPS / DBREAD

Details of Analysis

• NonLocal Nested Reading DB OP (SELECT) found
• LOOP
• NEW
• Call Method GET_LOCATION
• WHILE
• SELECT /SCWM/LAGP
• Finding can be suppressed with pseudo comment "#EC CI_SEL_NESTED or "#EC CI_SROFC_NESTED
• Effort: 50
• Calls: 2
• Nr Loops: 2
• Severity: 2
• Table: /SCWM/LAGP

Code Inspector

Find database operations in nested loops across modularization units

This check searches for database operations that are nested inside any kind of loop (e.g. LOOP-loop, DO-loop, ...). If in the loop there is a call to a modularization unit (PERFORM, CALL FUNCTION, CALL METHOD), then this call is tracked and it will be analyzed, whether there is a database operation in the called unit. This way, several levels of the call-stack are analyzed (for runtime reasons, there is an upper limit for the call-stack depth).

What you can do:
If a database operation is executed inside a loop for several entries of the internal table that is processed, please check whether it is possible to read or write the needed database lines with one database statement instead. For example, it could be possible to replace the SELECT inside the LOOP with a SELECT ... FROM dbtab FOR ALL ENTRIES IN itab.

General Parameters:

• Only search for these tables: with this parameter you can restrict the search to a set of database tables.
• Minimum table size category: with this parameter you can restrict the search on those database operations that access database tables with a size category that is equal or larger than the parameter. The size category is maintained in the technical settings of a database table in the ABAP dictionary (SE11).
• Call-stack depth for analysis: this value determines how many levels of the call-stack will be analyzed below the loop statement. The default value is 3, the maximum value is 10.
• Exclude buffered tables: here it is possible to exclude accesses to buffered tables.
• Analyze only local procedures: here it is possible to restrict the evaluation to local procedures, which saves evaluation time.
• Restrict to object set: Only calls in procedures being contained in the object set of the inspection are analyzed
• Analyze reading db operations: analyze reading database operations inside a loop, i.e. SELECT, OPEN CURSOR, FETCH, CLOSE, and the obsolete ones READ TABLE dbtab and LOOP AT dbtab.
• Analyze writing db operations: analyze writing database operations inside a loop, i.e. DELETE, INSERT, UPDATE, and MODIFY.
• Analyze EXEC SQL statements: analyze exec sql statements inside a loop.
• Maximal Number of stacks shown: If there are several findings with a different stack (i.e. a different way to get to this finding), only show some of them.

Effort Parameters (determine the effort to correct a problematic statement)

Effort for db op in loop: with this parameter you determine the effort to remove a loop surrounding a database statement. The default value is 10.

Effort for each call: with this parameter you determine the effort to remove a loop surrounding a procedure call that contains a database statement. The default value is 20.

The check delivers the following result messages. The pseudo comments to suppress the messages are indicated in brackets:

• DBREAD_LOC A reading database operation was found inside a local loop ("#EC CI_SEL_NESTED or "#EC CI_SROFC_NESTED)
• DBREAD A reading database operation was found inside a non local loop. ("#EC CI_SEL_NESTED or "#EC CI_SROFC_NESTED)
• DBREAD_S A reading database operation was found as only statement inside a loop, this is easy to be transformed into an array operation. ("#EC CI_SEL_NESTED or "#EC CI_SROFC_NESTED)
• DBWRT_LOC A writing database operation was found inside a local loop. ("#EC CI_IMUD_NESTED)
• DBWRITE A writing database operation was found inside a non local loop. ("#EC CI_IMUD_NESTED)
• DBWRITE_S A writing database operation was found as only statement inside a loop, this is easy to be transformed into an array operation. ("#EC CI_IMUD_NESTED)
• EXEC_LOC An exec sql operation was found inside a local loop. ("#EC CI_EXEC_SQL_NESTED)
• EXEC An exec sql operation was found inside a non local loop. ("#EC CI_EXEC_SQL_NESTED)

If you have switched on to get detail information for the result,

you find the additional informations:

• Table Name Name of the database table concerned
• Nr Loops Number of loops around the database operation
• Nr Calls Number of procedure calls around the database operation
• Effort The effort to correct this problem.
  It is calculated as follows: Effort = Effort for db op in loop + Effort for each call * number of calls
• Severity The severity of this problem is a measure for the performance loss due to this problem.
  It is calculated: Severity = number of loops