01-13-2006 12:29 AM
Hi,
I am retrieving data from three tables using several loops .All the three tables contains
millions of records. Is there anyway to rewrite this logic so that I can increase the performance?
My code is:
SELECT FIELD1 FIELD2 FIELD3
FROM ZTABLE INTO TABLE I_TAB1
WHERE FIELD1 = 01
AND FIELD2 = 'P'
LOOP AT I_TAB1.
SELECT FIELD1 FIELD2 FIELD3
FROM DFKKCR INTO TABLE I_TAB2
WHERE FIELD1 = 'TEXT'.
AND FIELD2 = I_TAB1-FIELD2
AND FIELD3 = I_TAB1-FIELD3.
LOOP AT I_TAB2.
SELECT FIELD1 FIELD2
FROM DPAYH INTO TABLE I_TAB3
WHERE FIELD1 = I_TAB2-FIELD1
AND FIELD2 = I_TAB2-FIELD2.
LOOP AT I_TAB3.
SELECT FIELD1 FIELD2
FROM DPAYP INTO TABLE I_TAB4
WHERE FIELD1 = I_TAB3-FIELD1
AND FIELD2 = I_TAB3-FIELD2.
ENDLOOP.
ENDLOOP.
ENDLOOP.
01-13-2006 1:07 AM
I think it can be simplified: performance may suffer if field1 and field2 are not indexes on the tables.
.............
SELECT FIELD1 FIELD2 FIELD3
FROM ZTABLE INTO TABLE I_TAB1
WHERE FIELD1 = 01
AND FIELD2 = 'P'
SELECT FIELD3
FROM DFKKCR INTO TABLE I_TAB2
for all entries in i_tab1
WHERE FIELD1 = 'TEXT'.
AND FIELD2 = 'P'
AND FIELD3 = I_TAB1-FIELD3.
DPAYH select is redundant since all it will retrun is
a table of entries with field1 = 'TEXT' and field2 = 'P'
*SELECT FIELD1 FIELD2
*FROM DPAYH INTO TABLE I_TAB3
*WHERE FIELD1 = I_TAB2-FIELD1
*AND FIELD2 = I_TAB2-FIELD2.
DPAYP select also redundant
*SELECT FIELD1 FIELD2
*FROM DPAYP INTO TABLE I_TAB4
*WHERE FIELD1 = I_TAB3-FIELD1
*AND FIELD2 = I_TAB3-FIELD2.
01-13-2006 12:37 AM
01-13-2006 12:44 AM
Maybe something like this.
select field1 field2 field3
from ztable into table i_tab1
where field1 = 01
and field2 = 'P'.
<b>check not i_tab1[] is initial.</b>
select field1 field2 field3
from dfkkcr into table i_tab2
for all entries in itab1
where field2 = i_tab1-field2
and field3 = i_tab1-field3
and field1 = 'TEXT'.
<b>check not i_tab2[] is initial.</b>
select field1 field2
from dpayh into table i_tab3
for all entries in i_itab2
where field1 = i_tab2-field1
and field2 = i_tab2-field2.
<b>check not i_tab3[] is initial.</b>
select field1 field2
from dpayp into table i_tab4
for all entries in i_itab3
where field1 = i_tab3-field1
and field2 = i_tab3-field2.
Make sure that you check that the previous internal table has data.
Regards,
Rich Heilman
01-13-2006 12:39 AM
SELECT FIELD1 FIELD2 FIELD3
FROM ZTABLE INTO TABLE I_TAB1
WHERE FIELD1 = 01
AND FIELD2 = 'P'
SELECT FIELD1 FIELD2 FIELD3
FROM DFKKCR INTO TABLE I_TAB2
FOR ALL ENTRIES IN I_TAB1
WHERE FIELD2 = I_TAB1-FIELD2 AND
FIELD3 = I_TAB1-FIELD3 AND
FIELD1 = 'TEXT'.
SELECT FIELD1 FIELD2
FROM DPAYH INTO TABLE I_TAB3
FOR ALL ENTRIES IN I_TAB2
WHERE FIELD1 = I_TAB2-FIELD1
AND FIELD2 = I_TAB2-FIELD2.
SELECT FIELD1 FIELD2
FROM DPAYP INTO TABLE I_TAB4
FOR ALL ENTRIES IN I_TAB3
WHERE FIELD1 = I_TAB3-FIELD1
AND FIELD2 = I_TAB3-FIELD2.
01-13-2006 1:07 AM
I think it can be simplified: performance may suffer if field1 and field2 are not indexes on the tables.
.............
SELECT FIELD1 FIELD2 FIELD3
FROM ZTABLE INTO TABLE I_TAB1
WHERE FIELD1 = 01
AND FIELD2 = 'P'
SELECT FIELD3
FROM DFKKCR INTO TABLE I_TAB2
for all entries in i_tab1
WHERE FIELD1 = 'TEXT'.
AND FIELD2 = 'P'
AND FIELD3 = I_TAB1-FIELD3.
DPAYH select is redundant since all it will retrun is
a table of entries with field1 = 'TEXT' and field2 = 'P'
*SELECT FIELD1 FIELD2
*FROM DPAYH INTO TABLE I_TAB3
*WHERE FIELD1 = I_TAB2-FIELD1
*AND FIELD2 = I_TAB2-FIELD2.
DPAYP select also redundant
*SELECT FIELD1 FIELD2
*FROM DPAYP INTO TABLE I_TAB4
*WHERE FIELD1 = I_TAB3-FIELD1
*AND FIELD2 = I_TAB3-FIELD2.
01-19-2006 7:19 PM
Thanks Friends. I did use some of your logic.
I am closing this thread and awarding the points.
Thanks for everyone for sharing the ideas.