Skip to Content
-1

ABAP Select Query too slow

Hi All,

We use the below select query inside our end routine.

The table /bic/AZACCRECDATA00300 contains around 2 Million records and we've activated parallel processing in our routine, which runs 5 jobs in parallel for DTP. Each time when the routine is executed, the select query takes around 4-6 hours to load. We have to modify the key field combinations, use inner join instead of FAE and changed the order of the select and where fields, however, it doesn't seem to imrpve. Please help us to understand where could it have gone wrong.


SELECT comp_code
Document_Date
Reference_Document_Number
/bic/zcclaim
Account_Document_Number
fiscper
fiscvarnt
Item_Number
FI_DSBITEM
fiscyear
Debit_Credit_Local Currenty
Debit_Credit_Document_Currency
Local_Currency
Document_Currency
Clear_Date
Clearing_Document_number
FI_Document_Status
Accounting_Document_Type
Posting_Date
/bic/zinv_ref FROM /bic/AZACCRECDATA00300 INTO TABLE lt_claim_complex FOR ALL ENTRIES IN RESULT_PACKAGE WHERE
comp_code = RESULT_PACKAGE-comp_code AND
FISCPER = RESULT_PACKAGE-FISCPER AND
FISCVARNT = RESULT_PACKAGE-FISCVARNT AND
Item_Number = RESULT_PACKAGE-Item_Number AND
fiscyear = RESULT_PACKAGE-fiscyear AND
FI_Document_Status = 'O' AND
/bic/Zinvoice_reference = RESULT_PACKAGE-ac_doc_no.

Below are the indexes on : /bic/AZACCRECDATA00300. created by Basis team for the above query. All are non unique indexes/Index (on all database systems.)
Index 1: comp-code,fiscyear ,FI_Document_Status,/bic/Zinvoice_reference
Index 2: comp-code
Index 3: comp-code,FISCPER, fiscvarnt,Item_Number,fiscyear

Add a comment
10|10000 characters needed characters exceeded

Related questions

1 Answer

  • Best Answer
    Posted on Mar 14, 2019 at 12:40 AM

    What is your package size? if its 50k -> 100k or more, its mean you for all entries of 50k+ against 2mil, which i must say wont be happy ending. if you using hana db, you may try to push that logic to hana view instead.

    I just wonder is it required to do the query in the routine, you may load basic data in first DTP then load /bic/AZACCRECDATA00300 in second DTP with same key, it will overwrite the data of first DTP and you get the same result.

    anw, its depend on your requirement. check this doc for more tips:

    https://help.sap.com/doc/saphelp_nw70ehp2/7.02.16/en-US/47/e8c56ecd313c86e10000000a42189c/content.htm?no_cache=true

    Add a comment
    10|10000 characters needed characters exceeded

    • Although the problem was handled differently (i.e. to restrict the amount of records that are coming into the info provider in the first place, since the client said they only wanted specific set of records), this answer was very valuable as I could propose this as an option and have also gave me a new perspective to handle this scenario. Thanks!