I have an internal table like:
TYPES: BEGIN OF gty_min_jobs,
orgeh TYPE zgerpt_rnk_min-orgeh1,
mnsty TYPE zgerpt_rnk_min-mnsty,
mshort TYPE zgerpt_rnk_min-mshort,
position TYPE hrp1001-sobid,
job TYPE hrp1001-sobid,
job_grade TYPE hrp9003-jobgr,
scheme TYPE hrp9003-pg,
END OF gty_min_jobs.
DATA: gt_min_jobs TYPE TABLE OF gty_min_jobs,
gwa_min_jobs TYPE gty_min_jobs.
I need to populate this table from 3 different database tables: zgerpt_rnk_min, hrp1001 and hrp9003.
I need to:
SELECT orgeh msty mshort FROM zgerpt_rnk_min INTO gt_table1.
SELECT position FROM hrp1001 INTO gt_table2
FOR ALL ENTRIES IN gt_table1
WHERE objid = gt_table1-orgeh AND
otype = 'O' AND
sclas = 'S' AND
begda LE p_keydt AND
endda GE p_keydt AND
plvar ='01' AND
istat = '1' AND
objid IN (pnpobjid).
SELECT job FROM hrp1001 INTO gt_table3
FOR ALL ENTRIES IN gt_table2
WHERE objid = gt_table2-position AND
otype = 'S' AND
sclas = 'C' AND
begda LE p_keydt AND
endda GE p_keydt.
SELECT job_grade scheme FROM hrp9003 INTO gt_table4
FOR ALL ENTRIES IN gt_table3
WHERE objid = gt_table3-job AND
begda LE p_keydt AND
endda GE p_keydt.
Combining my data into my internal table using various loops. What is the most efficient way to do this? Is it possible to select all the required data in one go using an inner join? If so how would I incorporate all the WHERE conditions? Appreciate any pointers!
What the most efficient way to do something is, is not always entirely obvious. Transaction SAT
(or SE30
in older systems) is an invaluable tool. Here you can test the runtime of your programs, to see which statements are more efficient, and also to see where bottlenecks in your program is. The also have a few very good tips and tricks about performance:
Another transaction you could use to measure your performance is ST05
.
Creating a view may be your best bet as VWEGERT suggested. Handling the fact that you are selecting from the same table (HRP1001) twice would probably be easier in a view.
For argument's sake, let's try to combine your select statements into a single select:
The first 2 select statements would combine as follows:
SELECT t1~orgeh t1~msty t1~mshort t2~position
INTO gt_my_combined_table
FROM zgerpt_rnk_min as t1
JOIN hrp1001 as t2
ON t1~orgeh = t2~objid
WHERE t2~otype = 'O' AND
t2~sclas = 'S' AND
t2~begda LE p_keydt AND
t2~endda GE p_keydt AND
t2~plvar ='01' AND
t2~istat = '1' AND
t2~objid IN (pnpobjid).
Now adding in your 3rd and 4th selects:
SELECT t1~orgeh t1~msty t1~mshort t2~position t3~job t4~job_grade t4~scheme
INTO gt_my_combined_table
FROM zgerpt_rnk_min as t1
JOIN hrp1001 as t2
ON t1~orgeh = t2~objid
JOIN hrp1001 as t3
ON t2~position = t3~objid
JOIN hrp9003
ON t3~job = t4~objid
WHERE t2~otype = 'O' AND
t2~sclas = 'S' AND
t2~begda LE p_keydt AND
t2~endda GE p_keydt AND
t2~plvar ='01' AND
t2~istat = '1' AND
t2~objid IN (pnpobjid) AND
t3~otype = 'S' AND
t3~sclas = 'C' AND
t3~begda LE p_keydt AND
t3~endda GE p_keydt AND
t4~begda LE p_keydt AND
t4~endda GE p_keydt.
My issue with this grotesque select statement would be that the database might actually get confused in which key or index to use, resulting in a sequential scan that would be very inefficient. I'm also a little bit uncomfortable in joining on the same table twice (although the compiler seems to be happy with it).