Is "Joins & For all entries" in same SQL Query Possible?

[Former Member]

Hi all Professional,

Can we use "Inner Joins" and "For All Entries In" in the same SQL Query. if possible then pls clarify this query.

Here I am using three Transparent Table and fetching data from them.

SELECT a_{bukrs a}belnr a_{hkont a}xref2 a_{shkzg a}wrbtr a_{gsber a}zfbdt a_{zterm a}mwskz a_{sgtxt a}xref1 a_{gjahr a}buzei

b_{kunnr b}werks b_{menge b}meins b_{matnr b}koart

c_{bukrs c}belnr c_{blart c}bldat c_{budat c}xblnr c_{gjahr c}stgrd c_{stblg c}stblg c~xreversal

INTO CORRESPONDING FIELDS OF TABLE it_bsid FROM ( ( bsid AS a

INNER JOIN acctit AS b ON a_{bukrs = b}bukrs )

INNER JOIN bkpf AS c ON c_{bukrs = a}bukrs

AND c_{belnr = a}belnr

AND c_{gjahr = a}gjahr )

FOR ALL ENTRIES IN it_bkpf

WHERE

a~belnr EQ it_bkpf-belnr

AND a~gjahr EQ it_bkpf-gjahr

AND a~bukrs EQ it_bkpf-bukrs

AND a~gsber IN so_bus.

After executing this query, I'm getting Dump Error.

Error analysis

When the program was running, it was established that more

memory was needed than the operating system is able to provide savely.

To avoid a system crash, you must prevent this

situation.

-

---

Last error logged in SAP kernel

-

---

Component............ "EM"

Place................ "SAP-Server Development_DVL_01 on host Development (wp

2)"

Version.............. 37

Error code........... 7

Error text........... "Warning: EM-Memory exhausted: Workprocess gets PRIV "

Description.......... " "

System call.......... " "

Module............... "emxx.c"

Line................. 1886

Pls resolve, if anybody knows.

Thanks

Devinder

[ThomasZloch]

Yes you can use joins and FAE in the same statement. The dump comes because you are reading to much data into internal memory.

Make sure the FAE driver table is not empty and search for keywords like "package processing", there were many discussions in these forums already.

Thomas

[former_member418469]

Hi,

I saw your code, try to do the following:

1. In where conditon fields are not in order, so use all the key fields in order as per table bsid

2. Try to use all key fields for better performance, if not able to use any key field put condition as a~kunnr ne space

3. Don't use corresponding fields, instead of it use multiple internal tables, combine them together

4. Add if not it_bkpf[] isd initial before select query.

5. Instead of using joins and select all entries, split it into different internal tables then combine together

[octavian_nicolae]

Hi,

During testing i notice that splitting into multiple selects does improve performance. But the best performance I achieved using DB Hints instead of splitting the select statements.

Generally performance of joins together with for all entries is bad.

However if you will look into SAP note 1662726 you will notice that this issue (bad performance in using join and for all entries together) has been addressed.

Even though the note is for HANA DB, FM RSDU_CREATE_HINT_FAE can be used independent of DB.

On HANA DB performance improvement is huge (i achieved 62 seconds using DB Hints compared to 1656 seconds using for all entries). On Oracle DB the same code initially run in 99 seconds with for all entries and with DB Hints in 82 seconds for ~ 1.000.000 records and ~660 seconds compared to 1349 seconds for ~8.000.000 records..

Sample code from SAP Note below:

Original statement:

SELECT COL1 COL2 COL3 COL4 COL5

  FROM TAB1

  INTO CORRESPONDING FIELDS OF TABLE LT_RESULT

  FOR ALL ENTRIES IN LT_SOURCE_TMP

  WHERE COL3 = LT_SOURCE_TMP-COL3

  AND   COL4 = LT_SOURCE_TMP-COL4

  AND   COL5 = LT_SOURCE_TMP-COL5

Revision:

DATA: L_T_TABLNM TYPE RSDU_T_TABLNM,

      L_LINES TYPE I,

      L_HINT TYPE RSDU_HINT.

APPEND 'TAB1' TO L_T_TABLNM.

L_LINES = LINES( LT_SOURCE_TMP ).

CALL FUNCTION 'RSDU_CREATE_HINT_FAE'

  EXPORTING

    I_T_TABLNM   = L_T_TABLNM

    I_FAE_FIELDS = 3

    I_FAE_LINES  = L_LINES

    I_EQUI_JOIN  = RS_C_TRUE

  IMPORTING

    E_HINT       = L_HINT

  EXCEPTIONS

    OTHERS       = 0.

SELECT COL1 COL2 COL3 COL4 COL5

  FROM TAB1

  INTO CORRESPONDING FIELDS OF TABLE LT_RESULT

  FOR ALL ENTRIES IN LT_SOURCE_TMP

  WHERE COL3 = LT_SOURCE_TMP-COL3

  AND   COL4 = LT_SOURCE_TMP-COL4

  AND   COL5 = LT_SOURCE_TMP-COL5

          %_HINTS ADABAS  L_HINT.

Best regards,

Octavian

[former_member194613]

> 1. In where conditon fields are not in order, so use all the key fields in order as per table bsid

> 2. Try to use all key fields for better performance, if not able to use any key field put condition as a~kunnr ne space

> 3. Don't use corresponding fields, instead of it use multiple internal tables, combine them together

> 4. Add if not it_bkpf[] isd initial before select query.

> 5. Instead of using joins and select all entries, split it into different internal tables then combine together

please give only recommendation, if you know what you are talking about, I would only support No. 4

3 and 5 are highly dangerous for beginners becasuse the will lead to nested internal tables with nonlinear coding.

2 is nonsense and 1 has not the mentioned effect but is only recommended to save space in the cursor cache.

[Former Member]

Answer Still Pending?

Edited by: Devinder Pawha on Apr 8, 2010 11:33 AM

[ThomasZloch]

I gave you an answer already. Did you understand it?

I'm quite sure that the reply by "harryibm" did not solve your problem.

Thomas

[former_member192616]

Hi,

as already said, check that the driver table is not empty.

Package processing for FAE might not help, consider that before the packages are applied, the

FAE has to be executed completely in order to remove the duplicates.

The resultset of an FAE is either buffered in an internally created intermediate table in the database interface or in the target table directly, it depends on the circumstances. The package addition requires an indermediate table.

In your case the corresponding fields addition could also lead to the

creation of this table in the database interface. (If you select MORE fields than the ones defined in it_bsid

we can not use the target table but have to create an intermediate table).

Change your statement and use INTO TABLE OR DOUBLECHECK that you select only fields that are defined

in your internal table it_bsid and don't forget to check the driver table before executing the FAE.

if lines( it_bkpf ) > 0.

SELECT a~bukrs a~belnr a~hkont a~xref2 a~shkzg a~wrbtr a~gsber a~zfbdt a~zterm a~mwskz 
a~sgtxt a~xref1 a~gjahr a~buzei
b~kunnr b~werks b~menge b~meins b~matnr b~koart
c~bukrs c~belnr c~blart c~bldat c~budat c~xblnr c~gjahr c~stgrd c~stblg c~stblg c~xreversal
INTO TABLE it_bisid FROM ( ( bsid AS a
INNER JOIN acctit AS b ON a~bukrs = b~bukrs )
INNER JOIN bkpf AS c ON c~bukrs = a~bukrs
AND c~belnr = a~belnr
AND c~gjahr = a~gjahr )
FOR ALL ENTRIES IN it_bkpf
WHERE
a~belnr EQ it_bkpf-belnr
AND a~gjahr EQ it_bkpf-gjahr
AND a~bukrs EQ it_bkpf-bukrs
AND a~gsber IN so_bus.

endif.

If it still dumps after this changes the maximum available memory for your user context might be too less to process the FAE with your big driver table, contact your basis then.

hth,

Hermann

[former_member194613]

Thomas gave the answer, the rest was irrelevant

... and there are points given ... that's nonsense. The longest answer does not have to be the best.