Hi gaurav,
There are three ways to check for performance issue.
1. st05 - Using st05 you can get the total number of sql ( and sap table buffer) request and the number of rows fetched. When you open the trace, don't filter it by application name, that way you can also see the work done by rfc calls and update processes if such were called by your application.
2. st04 - Then using st04 you can get the logical i/o count at the database level.
3. se30 - Using se30 you can check whether the previous database metrics reflect most of the actual work done by your application. If most of the runtime is spent at the abap or system level, you will have to investigate further using other abap tools.
Drawbacks of For all Entries
At run time , in order to fulfill the "for all entries " request, the abap engine will generate several sql statements (for detailed information on this refer to note 48230). Regardless of which method the engine uses (union all, "or" or "in" predicates) If the itab is bigger then a few records, the abap engine will break the itab into parts, and rerun an sql statement several times in a loop. This rerun of the same sql statement , each time with different host values, is a source of resource waste because it may lead to re-reading of data pages.
returing to the above example , lets say that our internal table contains 500 records and that the abap engine will be forced to run the following sql statement 50 times with a list of 10 values each time.
for example:
Select * from mara
Where matnr in ( ...)
Db2 will be able to perform this sql statement cheaply all 50 times, using one of sap standard indexes that contain the matnr column. But in actuality, if you consider the wider picture (all 50 executions of the statement), you will see that some of the data pages, especially the root and middle-tire index pages have been re-read each execution.
Even though db2 has mechanisms like buffer pools and sequential detection to try to minimize the i/o cost of such cases, those mechanisms can only minimize the actual i/o operations , not the cpu cost of re-reading them once they are in memory. Had you coded the join, db2 would have known that you actually need 500 rows from mara, it would have been able to use other access methods, and potentially consume less getpages i/o and cpu.
In other words , when you use the "for all entries " clause instead of coding a join , you are depriving the database of important information needed to select the best access path for your application. Moreover, you are depriving your DBA of the same vital information. When the DBA monitors & tunes the system, he (or she) is less likely to recognize this kind of resource waste. The DBA will see a simple statement that uses an index , he is less likely to realize that this statement is executed in a loop unnecessarily.
In conclusion I suggest to "think twice" before using the "for all entries" clause and to evaluate the use of database views as a means to:
a. simplify sql
b. simplify abap code
c. get around open sql limitations.
And performance degradation when using the FOR ALL ENTRIES Inside loop statement with SELECT clause on big tables.
Thanks and Regards
Bluesky
