ABAP Select Query too slow

[former_member589026]

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

[Jelena]

This is not a standard table (not sure which solution /bic/ is for) and there is no information what kind of environment you have. There are DB tools available in SAP (maybe start with analyzing the execution path with SQL Trace?), work with your Basis admin / DBA on this.

[Sandra_Rossi]

Jelena is right, please attach the execution path/plan obtained via the SQL trace, that's the first thing to do!

[matt]

jelena.perfiljeva2 /bic/ namespace is BW.

[DoanManhQuynh · Accepted solution]

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

[former_member589026 · Accepted solution]

Hi Thanks for your suggestion. Can you let me know with an example of what you mean by Basic data? As you said, we're having a package size of 50K. /bic/AZACCRECDATA00300 is the Active table of another DSO, and the select query is written inside the end routine of Transformation from A00300- A00400 (which is a standard dso getting full update from 00300).

What we're doing is, For each package, we're doing some modifications for which we're scanning the entire Active table of Previous DSO(003) and finding the records which match the current package(RESULT PACKAGE) key fields. So, effectively, this select query runs for around 40 packages everyday(parallel processing-6). Hence the Query to the above is to find the matching records in the D003 for the current package that runs.

PS: From the basic data thing that you said, I've got an idea. On the above table (00300), Number of records with Document status "O" is 75000 out of 2,000,000. Will it improve performance if I do a select query on "O" first in the start routine and then do the for all entries only for that 75000 against 50000? or would it be fine, if I create a DSO before which has only those open records?(which i think is similar?)

[DoanManhQuynh · Accepted solution]

Swaminathan R:

your transfrom is: A00300- A00400, you need addition data from /bic/AZACCRECDATA00300 (differece DSO).

what i said in previous answer mean first do the transform A00300- A00400 (without routine), then create another transform from

/bic/AZACCRECDATA00300 - A00400, run them in that sequense and the data from /bic/AZACCRECDATA00300 will overwrite (with same key ) the first transform.

https://blogs.sap.com/2013/10/24/dso-overwrite-and-summation-function/

the ideal you said is get data in start routine then you can compare 2 table not for all entries, but if there is a condition to narrow your data why you have 2mil in the first place...

[former_member589026 · Accepted solution]

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!