05-08-2006 6:01 PM
hii
can any body tell me about the optimization between for all entries and joins . performance criteria between the both
05-08-2006 6:27 PM
For all entries and Joins used to avoid the nested select statements which are very time consuming.
Performance wise between for all entries and join,
If the range table(main table) is small then go for for all entries otherwise use join.
Thanks,
Vamshi
05-08-2006 6:08 PM
Hi
Abap programers and most of the dba's that support abap programmers , are familiar with the abap clause "for all entries".
Most of the web pages I visited recently, discuss 3 major drawbacks of the "for all entries" clause :
1. duplicate rows are automatically removed
2. if the itab used in the clause is empty , all the rows in the source table will be selected .
3. performance degradation when using the clause on big tables.
In this post I'd like to shed some light on the third issue,
Specifically , i'll discuss the use of the "for all entries" clause as a means to join tables in the abap code instead of in db2.
Select * from mara
For all entries in itab
Where matnr = itab-matnr.
If the actual source of the material list (represented here by itab) is actually another database table , like :
select matnr from mseg
into corresponding fields of table itab
where .
Then you could have used one sql statement that joins both tables.
Select t1.*
From mara t1, mseg t2
Where t1.matnr = t2.matnr
And T2 ..
So what are the drawbacks of using the "for all entires" instead of a join ?
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 itab 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. 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.
Also check out
http://www.thespot4sap.com/articles/SAPABAPPerformanceTuning_ForAllEntries.asp
http://www.thespot4sap.com/articles/SAPABAPPerformanceTuning_InnerJoinStatement.asp
http://www.sswug.org/see/28439
http://web.mit.edu/ist/org/admincomputing/dev/abap_review_check_list.htm
http://www.sapgenie.com/abap/performance.htm
Hope thisll give you idea!!
<b>P.S award the points.!!! !!!</b>
Good luck
Thanks
Saquib Khan
"Some are wise and some are otherwise"
05-08-2006 6:08 PM
Hi,
FOR ALL ENTRIES can be used to avoid the SELECTS WITH IN A LOOP.
Joins - Joins to the extent of 2-3 tables is good. If you have a requirement to join more than it might not be an effecient join.
Hope this answers your question.
Regards,
Ravi
Note - Please mark the helpful answers
05-08-2006 6:13 PM
HI,
<b>For all entries</b>
The for all entries creates a where clause, where all the entries in the driver table are combined with OR. If the number of entries in the driver table is larger than rsdb/max_blocking_factor, several similar SQL statements are executed to limit the length of the WHERE clause.
The plus
Large amount of data
Mixing processing and reading of data
Fast internal reprocessing of data
Fast
The Minus
Difficult to program/understand
Memory could be critical (use FREE or PACKAGE size)
Some steps that might make FOR ALL ENTRIES more efficient:
Removing duplicates from the driver table
Sorting the driver table
If possible, convert the data in the driver table to ranges so a BETWEEN statement is used instead of and OR statement:
FOR ALL ENTRIES IN i_tab
WHERE mykey >= i_tab-low and
mykey <= i_tab-high.
<b>Select using JOINS</b>
The plus
Very large amount of data
Similar to Nested selects - when the accesses are planned by the programmer
In some cases the fastest
Not so memory critical
The minus
Very difficult to program/understand
Mixing processing and reading of data not possible
Check these Links too...
http://www.thespot4sap.com/Articles/SAPABAPPerformanceTuning_ForAllEntries.asp
http://www.sappoint.com/faq/faqsql.pdf
http://web.mit.edu/ist/org/admincomputing/dev/abap_review_check_list.htm
Regards,
Santosh
05-08-2006 6:27 PM
For all entries and Joins used to avoid the nested select statements which are very time consuming.
Performance wise between for all entries and join,
If the range table(main table) is small then go for for all entries otherwise use join.
Thanks,
Vamshi