topic Re: Performance in Application Development and Automation Discussions

Performance

Former Member — Fri, 13 Jan 2006 00:29:14 GMT

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.

Re: Performance

RichHeilman — Fri, 13 Jan 2006 00:37:51 GMT

You might try using the FOR ALL ENTRIES extension of the SELECT statement.

REgards,

Rich Heilman

Re: Performance

Former Member — Fri, 13 Jan 2006 00:39:01 GMT

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.

Re: Performance

RichHeilman — Fri, 13 Jan 2006 00:44:16 GMT

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

Re: Performance

former_member186741 — Fri, 13 Jan 2006 01:07:20 GMT

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.

Re: Performance

Former Member — Thu, 19 Jan 2006 19:19:02 GMT

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.