topic Re: regarding nested selects........... in Application Development and Automation Discussions

regarding nested selects...........

Former Member — Mon, 11 Aug 2008 11:10:45 GMT

Hi friends,

i want to use one query in that i have to use neseted selects.

can any one tel me example with considering performane also.

regards,

madhu.

Re: regarding nested selects...........

Former Member — Mon, 11 Aug 2008 11:14:37 GMT

Hi madhu,

SELECT tabname

INTO ls_dd02l

FROM dd02l

WHERE tabname LIKE c_feld

AND buffered IS NOT NULL.

SELECT tabname fieldname keyflag position

INTO CORRESPONDING FIELDS OF ls_dd03l

FROM dd03l

WHERE tabname = ls_dd02l-tabname

AND fieldname IN ('MANDT', 'CLIENT', 'CLNT')

AND keyflag = 'X'.

IF ( sy-subrc EQ 0 ).

wa-tabname = ls_dd03l-tabname.

wa-fieldname = ls_dd03l-fieldname.

wa-keyflag = ls_dd03l-keyflag.

wa-position = ls_dd03l-position.

APPEND wa TO itab.

ENDIF.

ENDSELECT.

There are no practical limits to nested SELECTs, but as other repliers have already pointed out they quickly become a performance hog. I have stopped using them for years, and when I teach ABAP performance classes I also quote them as one of the top "don'ts". With nested selects, the number of calls to the DBMS can easily become enormous, putting the database server under stress.

A good alternative - the one I favour most myself - is to use SELECT INTO TABLE for the"outer" select, followed by appropriate SELECT FOR ALL ENTRIES statements to handle the inner levels.

ABAP joins are another alternative. When properly written, a join will be faster than a nested select. The problem is however that very often joins are not properly written. SELECT statements should always respect the KISS principle (keep it simple, stupid) and with a join it is very easy to violate that rule. A join over 2 tables should probably be OK, but with 3 or more tables you want to be very careful. If you really want to code a complex join, I recommend that you also code the same logic with different means (INTO TABLE/FOR ALL ENTRIES or even nested selects) and then compare that with the join.

regards,

Jayan.

Re: regarding nested selects...........

former_member585060 — Mon, 11 Aug 2008 11:15:12 GMT

Hi,

Instead of Nested selects use like this

SELECT * FROM SFLIGHT AS F INTO SFLIGHT_WA

WHERE SEATSOCC < F~SEATSMAX

AND EXISTS ( SELECT * FROM SPFLI

WHERE CARRID = F~CARRID

AND CONNID = F~CONNID

AND CITYFROM = 'FRANKFURT'

AND CITYTO = 'NEW YORK' )

AND FLDATE BETWEEN '19990101' AND '19990331'.

ENDSELECT.

Go to SE30 and glick on Tips and Tricks

Look at SQL interface.

Regards,

Krishna

Re: regarding nested selects...........

former_member787646 — Mon, 11 Aug 2008 11:19:57 GMT

Instead of using NESTED SQL statements use FOR ALL ENTRIES as far as performance is concerned.

Hope it helps.

Murthy

Re: regarding nested selects...........

Former Member — Mon, 11 Aug 2008 13:34:25 GMT

Nested selects should be avoided,

+ If all tables are not buffered. If they are buffered, then the nested selects are the best solution!

+ Joins should be the best alternative. But the performance of the joins should be checked (as everything) by the SQL trace. And maybe youe first version is not optimal.

+ For all entries can be an option, I would actually only recommend it, if the join does not work.

+ the above mentioned subselect is a very special option, which can not always be used. But if it is applicable, then it can be the best solution.

Siegfried

Re: regarding nested selects...........

Former Member — Thu, 21 Aug 2008 11:04:18 GMT

Hi,

Nested select can be avoided by using two internal table and merging those two internal tables into third itab.

ex.

select field into table itab1 from ekko

where ebeln in s_ebeln.

select fields into table itab2 from ekpo

for all entries in itab1 where

ebeln = itab1-ebeln.

loop at itab1 into wa_itab1.

move-corresponding wa-tab1 to wa-final.

read table itab2 with key ebeln = it_header binary search transporting no fields.

loop at itab2 into wa_itab2 from sy-tabix.

if itab2-ebeln <> itab1-ebeln.

exit.

endif.

move-corresponding wa-tab2 to wa-final.

append wa-final to itab_final .

endloop.

This will avoid nested select and processing will be fast according to performance tuning.

Edited by: Shobhit Bansal on Aug 21, 2008 1:05 PM

Re: regarding nested selects...........

Former Member — Thu, 21 Aug 2008 11:13:54 GMT

check the link you find a example:

With luck,

Pritam.