Join between a DB table and an internal table?

[Former Member]

Hi,

I am in the process of optimizing my code. In one place, I have already SELECTed a subset of the rather large DD table DD03L into a much smaller internal table for use in a loop. A bit further on, I need to query DD table DD05P for data on relations and again I first create a much smaller internal table for use in the loop - this second one I have to join with DD03L because I need another field.

My question is: Since I already have an internal table DD03L_int which is much smaller than DD03L, can I not join the DB table DD05P with this and thus save on performance? Or is there an alternative, like using FAE or something?

Thanks a lot!

Best regards,

Sapperdapper

[Former Member]

Hi,

I am in the process of optimizing my code. In one place, I have already SELECTed a subset of the rather large DD table DD03L into a much smaller internal table for use in a loop. A bit further on, I need to query DD table DD05P for data on relations and again I first create a much smaller internal table for use in the loop - this second one I have to join with DD03L because I need another field.

My question is: Since I already have an internal table DD03L_int which is much smaller than DD03L, can I not join the DB table DD05P with this and thus save on performance? Or is there an alternative, like using FAE or something?

Thanks a lot!

Best regards,

Sapperdapper

[amy_king]

Hi Friedrich,

You cannot use JOIN between a DB table and an internal table; the keyword is for use only with DB queries. Rather than read each table individually into internal tables, why not read both at once using a JOIN? It sounds like you are JOINing the tables at some point anyway.

SELECT ...
    FROM DD03L
    JOIN DD05P on DD05P~field = DD03L~field
    ...

If this approach doesn't work for your requirement, please give more detail on what fields you are reading from each table/view and what result set you are trying to create.

Cheers,

Amy

[Former Member]

Hi Amy,

yes, that might indeed be a solution. I'm not sure, I would have to test the difference using a runtime_analysis.

I first create a table DD03L_int with TABNAME, FIELDNAME and attributes INTTYPE, INTLEN and DECIMALS. I need all those to dynamically create an internal table with the fields according to user parameters with all the correct attributes so I can fill in data from DB tables.

I then query DD05P for info on relations between all the involved tables which is why I create DD05P_int. I need the field KEYFLAG from table DD03L to make sure I get only one relation between two tables, the one that's using the keyfields.

Creating one big internal table containing the fields I need from both earlier on seems a good idea, I'll try.

Best regards,

Sapperdapper

P.S.: That leads me to another question - what are the rules rgd. Joins in ABAP? I need more entries from DD03L than I need from DD05P - so I have to use DD03L as the primary table and DD05P as the secondary to avoid losing records, right?

P.P.S.: Hmm, something must have gone wrong there - I used to get approx. 90k records from either table into that internal table and now I get over a million.

[Former Member]

Hi,

sorry, I miscounted last time. Unfortunately, when I want to test creating one combined internal table with all the fields I need, our system breaks down with a memory error. So I guess I will have to go with the old solution although it might be less optimal, I don't know.

Best regards,

Sapperdapper

[Former Member]

Hi,

I think now that this would actually not improve overall performance - unless that combined table would be smaller than the original, but it doesn't seem so. In any case, the SELECT command creating an internal table from each of the DD tables will be executed only once, but the internal table will subsequently be used in a LOOP that will run n times - depending on the number of tables and fields the user wants to process, that is not my choice.

In an example with 10 tables à 50 fields,

- the frst LOOP - on DD03L_int - will run 5k times while

- the second LOOP - on DD05P - will run at the most 90 times, probably less.

=> If I can make DD03L smaller by just a few thousand records, that would be a win, but by joining with DD05P, I cannot make it smaller in a sensible way.

Do you agree with this? I am open for different views and I always appreciate help.

Thanks!

Best regards,

Sapperdapper

[amy_king]

Hi Friedrich,

If you are getting a memory issue it is likely because you are hitting a SQL statement size limit on your database. Use FOR ALL ENTRIES as Dinesh has suggested. FOR ALL ENTRIES tells the database to read the data in segments.

Cheers,

Amy

[Former Member]

In my case, two Tables I want to join have the different data type char 32 and raw 16 and I can't make changes in Table.

SPAN { font-family: "Courier New"; font-size: 10pt; color: #000000; background: #FFFFFF; }

[retired_member]

https://help.sap.com/doc/abapdocu_753_index_htm/7.53/en-US/index.htm?file=abapselect_itab.htm

[former_member214709]

Dear Friedrich ,

It is better if you select all the relevant data from one table into ITAB1 and then select the data from another table based on FOR ALL ENTRIES IN ITAB1 into ITAB2.

Regards

Dinesh

[Former Member]

Hi,

that would have been my next question, whether that is possible. From a certain point on - as soon as the user is done with the GUI and has selected everything - there will be an internal table with all the fields I need which will be considerably less than is in DD03L - maybe about 100, not more in all probability. So then I can maybe do a new SELECT from DD03L with the FOR ALL ENTRIES option and in any case I can add that option to the SELECT from DD05P.

Thanks a lot!

Best regards,

Sapperdapper

[Former Member]

Hi,

I still cannot see why you won't be able to join your tables? You seem to only need a couple of thousand entries in both tables... So an INNER JOIN should perfectly suits your needs... And moreover you won't need to process your result set furthermore. Are you sure your join condition was correctly set? as well as your where clause? From my point of vue, the FAE addition should be the last option... but can ideed, sometimes, be faster for huge tables (but then we are talking about millions of records, not thousand).

Cheers,

Manu.

[Former Member]

Hi Manu,

well, joining with DD05P is difficult because SE11 does not tell me which are the keyfields of this table - I assume it is TABNAME and FIELDNAME, but then in some cases, I have only one field in FIELDNAME - the same in two records - but two different ones in FORKEY, which is correct. So I would have to join twice which I tried, but then I hit upon some other difficulties further on - that is when I realized it would not do any good to my overall performance.

Joining the two is not the issue, I would get that fixed eventually. The issue is filtering - my program is supposed to be quite generic, so I cannot exclude too much. DD05P is quite small - I can get that to less than 100k records - but that one deals only with tables. DD03L, containing all the fields, is naturally much bigger.

It will be different when we put all the parts together: Right at the beginning, I have to do a SELECT on DD03L once which gives me quite a lot of records, but lateron when I have a (quite small) internal table with the tables and fields selected by the user as input, I can use that to do the SELECT from DD03L accordingly and get only a small subset; Same with DD05P.

Best regards,

Sapperdapper

[former_member214709]

Dear Friedrich ,

SELECT  statement using FOR ALL ENTRIES would be convenient and in this case you need to equate the fields which are common with the table whose entries are compared using FOR ALL ENTRIES and the table from which you want to Select the data.

EX-

SELECT *

FROM EKPO

FOR ALL ENTRIES IN TABLE ITAB

WHERE EBELN = ITAB-EBEL.

Regards

Dinesh

Amy: Thanks for support

[Former Member]

Hi all,

thanks a lot for all the help! Using this trick I have been able to reduce the data-volume of querying DD03L by about 90% - I now query only fields for the tables I have offered to the user to begin with, which is all I need - resulting in an overall runtime reduction of (currently) 75% 😉

Many thanks again!

Best regards,

Sapperdapper