Hi,
CHk this blog....
/people/rob.burbank/blog/2006/11/16/performance--what-will-kill-you-and-what-will-leave-you-with-only-a-flesh-wound
Here are some performance TIPS
LOOP AT with WHERE clause
Working with internal tables
For all entries
Tools for performance tuning
Using table buffering
LOOP AT with WHERE clause
If you use a LOOP AT statement with a WHERE clause, the table whole will be read through
and not only the entriers that satifies the WHERE clause. This can lead to performance
problems when a very large internal table is read many times with a WHERE clause.
The solution is to sort the table on the keyfields, use a READ statment to find
the first entry that satifies the key. Then you can start the loop here, and check for
changes in the keyfield to exit the loop.
Do not
loop at gi_mseg into g_mseg
where matnr = p_matnr and
werks = p_werks and
lgort = p_lgort and
sobkz = space,
endloop.
Instead use:
sort gi_mseg by matnr werks lgort.
data:
l_tabix_from like sy-tabix.
read table gi_mseg with key matnr = p_matnr
werks = p_werks
lgort = p_lgort binary search
into g_mseg.
check sy-tabix > 0.
move sy-tabix to l_tabix_from.
loop at gi_mseg into g_mseg from l_tabix_from.
if g_mseg-matnr <> p_matnr or
g_mseg-werks <> p_werks or
g_mseg-lgort <> p_lgort.
exit.
endif.
check g_mseg-sobkz = space.
..... do something ......
endloop.
For all entries
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.
Some steps that might make FOR ALL ENTRIES more efficient:
- Removing duplicates from the 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.
Tools for performance tuning
The runtime analysis (SE30)
SQL Trace (ST05)
Tips and Tricks tool
The performance database
Using table buffering
Using buffered tables improves the performance considerably. Note that in some cases a statement can not be used with a buffered table, so when using these staments the buffer will be bypassed. These staments are:
Select DISTINCT
ORDER BY / GROUP BY / HAVING clause
Any WHERE clasuse that contains a subquery or IS NULL expression
JOIN
A SELECT... FOR UPDATE
If you want to explicitly bypass the buffer, use the BYPASS BUFFER addition to the SELECT clause.
Regards,
Balaji
**Rewards if answers are useful
Bluesky