topic Re: Nested select in Application Development and Automation Discussions

Nested select

Former Member — Fri, 14 Apr 2006 06:10:01 GMT

One more thing

jus read this

'Nested select should be avoided and used only as a last resort when no other data retrieval method is possible'

can anybody plz give me the example where we can be forced to use nested select statements??

Thanks once again

Re: Nested select

Former Member — Fri, 14 Apr 2006 06:12:58 GMT

Hi Rashmi,

ABAP programming has eveolved to such an extent that there will be no practical example to demonstrate the inevitable usage of nested selects.

Any code using a nested select can be converted into a better code(In terms of efficiency and performance).

We can tell you how to avoid a nested select if you want to.

Regards,

Ravi

Re: Nested select

Former Member — Fri, 14 Apr 2006 06:16:04 GMT

Rashmi,

Ravi is absoultely right, In the current situation coding can be done in such a way that NESTED SELECTS are avoided all the time.

Regards,

Ravi

Re: Nested select

Former Member — Fri, 14 Apr 2006 06:18:30 GMT

IN Other words, instead of using nested selects

you can use select into table option,

and later use nested loop statements which do not hit the database layer and much faster than nested selects.

Eg:

loop at itab.

select *

from mara

into mara

where matnr = itab-matnr.

select *

from makt

into makt

where matnr = mara-matnr.

endselect.

endloop.

can be changed to

if not itab[] is initial.

select *

from mara

into table it_mara

for all entries in itab

where matnr = itab-matnr.

if not it_mara[] is initial.

select *

from makt

into table it_makt

for all entries in it_mara

where matnr = it_mara-matnr.

endif.

REgards,

Ravi

Re: Nested select

Former Member — Fri, 14 Apr 2006 06:18:53 GMT

Thanks guyz

Re: Nested select

Former Member — Fri, 14 Apr 2006 08:31:42 GMT

Call Transaction SE30 and click on Tips&Tricks, there are examples and expkanationas and also the possibility to compare the runtime of codings.

Matthias

Re: Nested select

Former Member — Fri, 14 Apr 2006 16:53:53 GMT

The avoidance of nested selects, while desirable, is far from the most important thing to consider when tuning. Consider the following example:


REPORT ztest LINE-SIZE 80 MESSAGE-ID 00.

TABLES: bkpf, bseg.

DATA: BEGIN OF bkpf_int OCCURS 0.
        INCLUDE STRUCTURE bkpf.
DATA: END   OF bkpf_int.

DATA: BEGIN OF bseg_int OCCURS 0.
        INCLUDE STRUCTURE bseg.
DATA: END   OF bseg_int.

DATA: start TYPE i,
      end   TYPE i,
      dif   TYPE i.

SELECT-OPTIONS: s_bukrs FOR bkpf-bukrs,
                s_belnr FOR bkpf-belnr,
                s_gjahr FOR bkpf-gjahr.

SELECT * FROM bkpf
  WHERE bukrs IN s_bukrs
    AND belnr IN s_belnr
    AND gjahr IN s_gjahr.
  SELECT * FROM bseg
    WHERE bukrs = bkpf-bukrs
      AND belnr = bkpf-belnr
      AND gjahr = bkpf-gjahr.
  ENDSELECT.
ENDSELECT.

SELECT * FROM bkpf
INTO TABLE bkpf_int
  WHERE bukrs IN s_bukrs
    AND belnr IN s_belnr
    AND gjahr IN s_gjahr.

CHECK NOT bkpf_int[] IS INITIAL.
SELECT * FROM bseg
  FOR ALL ENTRIES IN bkpf_int
  WHERE bukrs = bkpf_int-bukrs
    AND belnr = bkpf_int-belnr
    AND gjahr = bkpf_int-gjahr.
ENDSELECT.

GET RUN TIME FIELD start.
SELECT * FROM bkpf
  WHERE bukrs IN s_bukrs
    AND belnr IN s_belnr
    AND gjahr IN s_gjahr.
  SELECT * FROM bseg
    WHERE bukrs = bkpf-bukrs
      AND belnr = bkpf-belnr
      AND gjahr = bkpf-gjahr.
  ENDSELECT.
ENDSELECT.
GET RUN TIME FIELD end.
dif = end - start.
WRITE: /001 'Time for nested select', dif, 'microseconds'.

REFRESH bkpf_int.
GET RUN TIME FIELD start.
SELECT * FROM bkpf
INTO TABLE bkpf_int
  WHERE bukrs IN s_bukrs
    AND belnr IN s_belnr
    AND gjahr IN s_gjahr.
SELECT * FROM bseg
  FOR ALL ENTRIES IN bkpf_int
  WHERE bukrs = bkpf_int-bukrs
    AND belnr = bkpf_int-belnr
    AND gjahr = bkpf_int-gjahr.
ENDSELECT.
GET RUN TIME FIELD end.
dif = end - start.
WRITE: /001 'Time for all entries select', dif, 'microseconds'.

When I ran the above code, the 'for all entries' select saved about 25% over the nested select.

If you have a report that has performance problems, nobody is gong to thank you for saving 1/4 of the execution time.

In my experience, there are only two techniques that drastically improve performance:

1) Make sure you use an index effectively in select statements.

2) Avoid nested loops over large tables.

Either of these two techniques can cut run times by a factor of 20 or 30; much better than 1/4.

Rob

Re: Nested select

Former Member — Sat, 15 Apr 2006 06:29:46 GMT

Thanks Rob

I got it