ABAP Select query performance slow (MSSQL)

[cmuriyadan]

I have written a select query on a table with for all entries. It looks something like this:

SELECT * FROM TABLE_A INTO TABLE LT_TABLE

FOR ALL ENTRIES IN IT_INPUT_TABLE

WHERE FIELD_A = IT_INPUT_TABLE-FIELD_A

AND FIELD_B = IT_INPUT_TABLE-FIELD_B

1.There is a secondary index on table TABLE_A on the fields FIELD_A and FIELD_B

2. I am checking if IT_INPUT_TABLE is not initial and IT_INPUT_TABLE is sorted by fields FIELD_A and FIELD_B. Duplicate entries comparing FIELD_A and FIELD_B are deleted

3. IT_INPUT_TABLE will have 1000 records at the max which may fetch around 5000-20000 records from TABLE_A

4. There are multiple processes running this query at the same time

The issue:

After running this query for nearly 60-70 times (3 parallel processes each querying 20 times. Each time the data is different as we are splitting the data and querying. We cannot query everything together as there are nearly 15 million records in TABLE_A) the performance of the select query starts degrading. Initially it would table about 10 seconds, then 50, after few processes 100 and then eventually close to 700 seconds.

Please help!!

[cmuriyadan]

I have written a select query on a table with for all entries. It looks something like this:

SELECT * FROM TABLE_A INTO TABLE LT_TABLE

FOR ALL ENTRIES IN IT_INPUT_TABLE

WHERE FIELD_A = IT_INPUT_TABLE-FIELD_A

AND FIELD_B = IT_INPUT_TABLE-FIELD_B

1.There is a secondary index on table TABLE_A on the fields FIELD_A and FIELD_B

2. I am checking if IT_INPUT_TABLE is not initial and IT_INPUT_TABLE is sorted by fields FIELD_A and FIELD_B. Duplicate entries comparing FIELD_A and FIELD_B are deleted

3. IT_INPUT_TABLE will have 1000 records at the max which may fetch around 5000-20000 records from TABLE_A

4. There are multiple processes running this query at the same time

The issue:

After running this query for nearly 60-70 times (3 parallel processes each querying 20 times. Each time the data is different as we are splitting the data and querying. We cannot query everything together as there are nearly 15 million records in TABLE_A) the performance of the select query starts degrading. Initially it would table about 10 seconds, then 50, after few processes 100 and then eventually close to 700 seconds.

Please help!!

[SimoneMilesi]

If you are accessing standard tables, please, share the name, it could help us in giving you some more focused answers.

i do not understand your points 3 and 4: are you running the same report with parallel prcessing? why? And if the IT_INPUT_TABLE is always the same, why you got 15k records of difference?

Your 60-70 are a workbench test or a real businness case (i'm trying to picture which kind of process could require this approach)?

From the explain you gave and for what i can see, it seems you are using a lot of resources without freeing them, expecially memory space.

Did you check the report / query / whatelse with SAT transaction?

[matt]

In the vast majority of cases an INNER JOIN is better performing than FOR ALL ENTRIES.

If you can't do this (e.g. routine in a BI transformation), then search in support.sap.com for the note that tells you which database hints to use with FAE.

As a general tip - only select the fields you need. Select * can kill performance all by itself.

[cmuriyadan]

Thanks for the inputs. The issue is resolved. I guess I had not provided details of the issue earlier. I am explaining below the exact issue and resolution:

1. Issue happened only in Test system. In Development system the code worked fine. In test system the query performance was good earlier.

2. There was a change to the database table and that was sent in a transport to the Test system. However, it looks like the index was not properly adjusted due to which the query performance was slow.

Resolution:

We re-transported the whole table (using SAP transport) to the test system which led to the secondary index adjustment and the query performance got fixed automatically.

Thank you for all your help.

Regards,

Charles