Select with joins causes High Memory usage 650GB

[ekekakos]

I need desperately your proposal on the following.

I have the below big select

SELECT DISTINCT edocdata~bukrs, edocdata~gjahr, edocdata~belnr, edocdata~budat, "#EC CI_SEL_NESTED
             acdoca~racct, edocdata~xblnr, bkpf~bldat, edocdata~series, edocdata~numbr,
             edocdata~invoice_type, bseg~buzid, acdoca~koart, bseg~qsskz, skb1~altkt,
             acdoca~mwskz, aade_vat~vat_category_mycloud, aade_vat~vat_exemption_categ_aade,
             coalesce( CASE WHEN bseg~shkzg = 'H' THEN -1 * bseg~dmbtr
                            ELSE bseg~dmbtr END, acdoca~hsl ) AS hsl,
             bkpf~hwaer, acdoca~kunnr, acdoca~lifnr, acdoca~blart,
             CASE WHEN bseg~shkzg = 'H' THEN -1 * bseg~qbshb
                  ELSE bseg~qbshb
             END AS qbshb,
             bset~kbetr * @( CONV kbetr_tax( '0.10' ) ) AS kbetr,
             edocdata~stceg,edocdata~name1 AS name, edocdata~stras AS street, edocdata~house_num1, edocdata~city1,
             edocdata~post_code1, edocdata~country,
             edocdata~zedoc_type, edocdata~sd_status, edocdata~rev_document,
             CASE WHEN acdoca~koart = 'D' THEN kna1~adrnr
                  WHEN acdoca~koart = 'K' THEN lfa1~adrnr
                  ELSE ' '
             END AS adrnr,
             CASE WHEN bseg~buzei IS NULL THEN acdoca~docln
                  ELSE bseg~buzei
             END AS docln
     FROM zslgebk_edocdtgt AS edocdata
        INNER JOIN zslgebk_control AS control
          ON edocdata~bukrs = control~company_code
        INNER JOIN bkpf
          ON edocdata~bukrs = bkpf~bukrs AND
             edocdata~gjahr = bkpf~gjahr AND
             edocdata~belnr = bkpf~belnr
        INNER JOIN acdoca ##DB_FEATURE_MODE[TABLE_LEN_MAX1]
          ON edocdata~bukrs = acdoca~rbukrs AND
             edocdata~gjahr = acdoca~gjahr AND
             edocdata~belnr = acdoca~belnr AND
             acdoca~rldnr = control~ledger
        LEFT OUTER JOIN bseg
          ON acdoca~rbukrs = bseg~bukrs AND
             acdoca~gjahr = bseg~gjahr AND
             acdoca~belnr = bseg~belnr AND
             acdoca~buzei = bseg~buzei
        LEFT OUTER JOIN bset
          ON acdoca~rbukrs = bset~bukrs AND
             acdoca~gjahr = bset~gjahr AND
             acdoca~belnr = bset~belnr AND
             acdoca~racct = bset~hkont
        LEFT OUTER JOIN kna1
          ON acdoca~kunnr = kna1~kunnr
        LEFT OUTER JOIN lfa1                         "#EC CI_SEL_NESTED
          ON acdoca~lifnr = lfa1~lifnr
        LEFT OUTER JOIN skb1
          ON acdoca~rbukrs = skb1~bukrs AND
             acdoca~racct = skb1~saknr
        LEFT OUTER JOIN zslgebk_tax_vat AS aade_vat
          ON acdoca~rbukrs = aade_vat~company_code AND
             acdoca~mwskz = aade_vat~tax_code
      WHERE acdoca~rbukrs = @company_code
      ORDER BY edocdata~bukrs, edocdata~gjahr, edocdata~belnr, aade_vat~vat_category_mycloud,
               acdoca~mwskz, acdoca~koart, qbshb
      INTO CORRESPONDING FIELDS OF TABLE @it_web_service_data.SELECT DISTINCT edocdata~bukrs, edocdata~gjahr, edocdata~belnr, edocdata~budat, "#EC CI_SEL_NESTED
             acdoca~racct, edocdata~xblnr, bkpf~bldat, edocdata~series, edocdata~numbr,
             edocdata~invoice_type, bseg~buzid, acdoca~koart, bseg~qsskz, skb1~altkt,
             acdoca~mwskz, aade_vat~vat_category_mycloud, aade_vat~vat_exemption_categ_aade,
             coalesce( CASE WHEN bseg~shkzg = 'H' THEN -1 * bseg~dmbtr
                            ELSE bseg~dmbtr END, acdoca~hsl ) AS hsl,
             bkpf~hwaer, acdoca~kunnr, acdoca~lifnr, acdoca~blart,
             CASE WHEN bseg~shkzg = 'H' THEN -1 * bseg~qbshb
                  ELSE bseg~qbshb
             END AS qbshb,
             bset~kbetr * @( CONV kbetr_tax( '0.10' ) ) AS kbetr,
             edocdata~stceg,edocdata~name1 AS name, edocdata~stras AS street, edocdata~house_num1, edocdata~city1,
             edocdata~post_code1, edocdata~country,
             edocdata~zedoc_type, edocdata~sd_status, edocdata~rev_document,
             CASE WHEN acdoca~koart = 'D' THEN kna1~adrnr
                  WHEN acdoca~koart = 'K' THEN lfa1~adrnr
                  ELSE ' '
             END AS adrnr,
             CASE WHEN bseg~buzei IS NULL THEN acdoca~docln
                  ELSE bseg~buzei
             END AS docln
     FROM zslgebk_edocdtgt AS edocdata
        INNER JOIN zslgebk_control AS control
          ON edocdata~bukrs = control~company_code
        INNER JOIN bkpf
          ON edocdata~bukrs = bkpf~bukrs AND
             edocdata~gjahr = bkpf~gjahr AND
             edocdata~belnr = bkpf~belnr
        INNER JOIN acdoca ##DB_FEATURE_MODE[TABLE_LEN_MAX1]
          ON edocdata~bukrs = acdoca~rbukrs AND
             edocdata~gjahr = acdoca~gjahr AND
             edocdata~belnr = acdoca~belnr AND
             acdoca~rldnr = control~ledger
        LEFT OUTER JOIN bseg
          ON acdoca~rbukrs = bseg~bukrs AND
             acdoca~gjahr = bseg~gjahr AND
             acdoca~belnr = bseg~belnr AND
             acdoca~buzei = bseg~buzei
        LEFT OUTER JOIN bset
          ON acdoca~rbukrs = bset~bukrs AND
             acdoca~gjahr = bset~gjahr AND
             acdoca~belnr = bset~belnr AND
             acdoca~racct = bset~hkont
        LEFT OUTER JOIN kna1
          ON acdoca~kunnr = kna1~kunnr
        LEFT OUTER JOIN lfa1                         "#EC CI_SEL_NESTED
          ON acdoca~lifnr = lfa1~lifnr
        LEFT OUTER JOIN skb1
          ON acdoca~rbukrs = skb1~bukrs AND
             acdoca~racct = skb1~saknr
        LEFT OUTER JOIN zslgebk_tax_vat AS aade_vat
          ON acdoca~rbukrs = aade_vat~company_code AND
             acdoca~mwskz = aade_vat~tax_code
      WHERE acdoca~rbukrs = @company_code
      ORDER BY edocdata~bukrs, edocdata~gjahr, edocdata~belnr, aade_vat~vat_category_mycloud,
               acdoca~mwskz, acdoca~koart, qbshb
      INTO CORRESPONDING FIELDS OF TABLE @it_web_service_data.<br>

The Global Temporary Table zslgebk_edocdtgt is inserted from an ITAB and containing 150 or less records (no more). This Select uses approximately 650 GB.

I am using GT table because I didn't want to use the FOR ALL ENTRIES.

Can you suggest any solution to this problem? The system is SAP S/4HANA 1709.

Will be a CDS a solution?

Thanks

Elias

PS1. Let me show you how I fill the the GTT ZSLGEBK_EDOCDTGT

FIELD-SYMBOLS: <it_data>   TYPE STANDARD TABLE.
split_edocdata_to_smaller( cht_edocdata ).
     LOOP AT it_edocdata_small_tables INTO DATA(edocdata_small_table).
       ASSIGN edocdata_small_table->* TO <it_data>.
       INSERT zslgebk_edocdtgt FROM TABLE @<it_data>.<br>

The it_edocdata_small_tables is TYPE STANDARD TABLE OF REF TO data

And then follow the big SELECT. Is sth wrong with the insertion?

PS2. The same code and with 150 records in GTT ZSLGEBK_EDOCDTGT is working perfect and fills the ITAB IT_WEB_SERVICE_DATA with 959 records. Strange, really strange.

[Sandra_Rossi]

CDS is not a solution. It will be converted into SQL (database doesn't understand CDS).

If you extract 100 million lines, it's not abnormal to consume 650 GB.

How many lines do you extract? Is it 650 GB in database or in application server?

[abityildiz]

Hello Elias,
650 gb memory looks impossible for sap procesess.what is the inside in the zslgebk_edocdtgt?is it storoge for jpeg,media,big pdf size etc?

my humble suggestion,you should sparate your sql statement with each internal tables.
you can use to Sorted Table,Hashed Table.

Or;
If you reach to hana db,you can create a hana Stored Procedure/function for sql statement then call it to from xsjs.And you publish xsjs to rest service.

[matt]

Usually with JOINS and WHERE, the WHERE filtering is performed after the join. That can mean that many many records are selected before the filtering takes place.

Try adding it to the ON

        INNER JOIN acdoca ##DB_FEATURE_MODE[TABLE_LEN_MAX1]
          ON edocdata~bukrs = acdoca~rbukrs AND
             edocdata~gjahr = acdoca~gjahr AND
             edocdata~belnr = acdoca~belnr AND
             acdoca~rldnr = control~ledger AND
             acdoca~rbukrs = @company_code

[ekekakos]

The GTT ZSLGEBK_EDOCDTGT has 47 fields, the keys are:

MANDT/BUKRS/GJAHR/BELNR and the rest fields have data types chars and the biggest has 40 length. There is no PDF or any other similar field.

What I am receiving from BASIS is the below

[Sandra_Rossi]

Display the execution plan (part of SQL trace for instance) to know the logic of the database, to see how tables are accessed and how many rows are estimated to be extracted for each table.

[kiran_k8]

Most complex query that I had ever seen. How you will debug in case of any error ? 🙂

K.Kiran

[MartinSommer]

What happens when you run for one GJAHR only?

Can you eliminate DISTINCT or will you get duplicates without DISTINCT?

Can you eliminate ORDER BY? Your ZSLGEBK_EDOCDTGT is already sorted by primary key.

Also remove ELSE ' ' before END AS adrnr because then null values from the CASE distinction will be converted to type-dependent initial values meaning it should be the same result still.