Performance Problem with Nested Select

[Former Member]

I have an urgent performance question, I run a nested select like this one

  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.
    ENDSELECT.

It is taking about 20sec, which is much too long.

How can I improve the performance.

Points rewarded!

S.B.

[Former Member]

HI,

Try using for all entries.

It may work.

[ThomasZloch]

Hi Siegfried,

did somebody hijack your account? It's not you asking this, is it?

Please discard if not helpful.

Cheers

Thomas

[Former Member]

Hi Siegfried Boes,

we should never user SELECT statement inside another SELECT statement.

it is always better to fetch all the data in to INTERNAL TABLES and then process the internal tables.

it is not recommeded to use SELECT ENDSELECT statement..

Because it is like loop statement SELECT ENDSELECT hits the database many times.

so it is not good programming if we hit the database many times.

that is the reason we have the concept of INTERNAL TABLES first fetch all the data in to the internal tables at once. and then process the internal table for required results.

I think u can use INNER JOINS for this case. Using inner join fetch data from both the tables and store it in internal table.

Best regards,

raam

[Former Member]

Hi Siegfried,

Use JOIN between DD02L and DD03L instead of nested selects.

Check this code.

  SELECT a~tabname
         b~fieldname
	 b~keyflag
	 b~position
    FROM dd02l as a INNER JOIN dd03l as b
      ON a~tabname = b~tabname
     INTO TABLE itab
   WHERE a~tabname LIKE c_feld
     AND buffered IS NOT NULL
     AND fieldname IN ('MANDT', 'CLIENT', 'CLNT')
     AND keyflag   = 'X'.

Thanks,

Vinay

[Former Member]

and what is the best and how much better?

[Former Member]

Hi Siegfried,

As Vinay said u can use INNER JOIN to get the data from both database tables

or u can use FOR ALL ENTRIES statement.

You can use the option FOR ALL ENTRIES to replace nested select loops by operations on internal tables. This can significantly improve the performance for large sets of selected data.

The above statement is from Library. Please check this link

http://help.sap.com/saphelp_nw04/helpdata/en/fc/eb3a1f358411d1829f0000e829fbfe/content.htm

Tabular Conditions sub heading

SELECT a~tabname
       b~fieldname
       b~keyflag
       b~position
    FROM dd02l as a INNER JOIN dd03l as b
      ON a~tabname = b~tabname
     INTO TABLE itab
   WHERE a~tabname LIKE c_feld
     AND buffered IS NOT NULL
     AND fieldname IN ('MANDT', 'CLIENT', 'CLNT')
     AND keyflag   = 'X'.

OR

SELECT tabname
       INTO TABLE lt_dd02l
       FROM dd02l
       WHERE tabname LIKE c_feld
       AND   buffered IS NOT NULL.

 IF NOT lt_dd02l IS INITIAL.

      SELECT tabname fieldname keyflag position
             INTO CORRESPONDING FIELDS OF TABLE itab
             FROM dd03l
             FOR ALL ENTRIES IN lt_dd02l
             WHERE tabname   = lt_dd02l-tabname
             AND   fieldname IN ('MANDT', 'CLIENT', 'CLNT')
             AND   keyflag   = 'X'.
ENDIF.

Please check this link

/people/rob.burbank/blog/2007/03/19/joins-vs-for-all-entries--which-performs-better

You have to look at the primary keys..

MANDT
STLTY
STLNR
STLAL
STLKN
STASZ

MANDT
STLTY
STLNR
STLKN
STPOZ

You need the stlty for both tables stas and stpo

The on condition between stas and stpo is obvious, will help a
bit.

It would be much better, if you could add it to the WHERE Condition.
Do you know it?

If not, then as the BOM categories are not so many, 

Try 

select distinct stlty
from stas.

and add the values to you where condition as

AND s1-stlty in ( ....... )

This should improve the performance.

I don't kknow whether the BOM Categories can change in future.
instead of a fixed in, you could add a subselect from a customizing table.

I am quite confident that the solution I proposed will be much faster than the FOR ALL ENTRIES.

But with more complicated joins you really have to try it.

There is no general rule about the number of tables in a join, there are perfectly running joins with 5 tables
and there are porblems with 2 tables.

You must analyze the indexes, it it is clear which index will support the accesses then it will work fine.

The FOR ALL ENTRIES is by construction much slower!

Best regards,

raam