Experiment on performance series

Part I: SELECT SINGLE vs FOR ALL ENTRIES

Part II: SELECT SINGLE vs FOR ALL ENTRIES, secondary index access

I believe our previous test on SELECT SINGLE vs FOR ALL ENTRIES did not surprise many of you because I see a tendency towards FOR ALL ENTRIES in programs I encounter. In previous scenario, we were able to access the search table with primary key but this may not be the case every time. Let’s see what happens if we could not access the search table with primary key, but with secondary index.

 

Disclaimer
This article shares result of an experiment and does not include any official SAP recomendation.

Execution time comparison of SELECT SINGLE in LOOP versus SELECT … FOR ALL ENTRIES and READ TABLE in LOOP, with secondary index

Our scenario differs a little from previous test:

  • Data set quantity is just 500 records because we are not accessing with primary key, otherwise we might have to wait a while to run the tests.
  • Our access key corresponds to a secondary index of database table.
  • We assume we won’t come across same search key second time.

SELECT SINGLE in LOOP

Below program simply selects some data set from table DD03L, loops through it and searches database table with SELECT SINGLE. I will run this program one time for 100.000 (hundred thousand) records to compare execution time with previous test, and three times for 500 records to compare with FOR ALL ENTRIES test, again for data sets from the beginning, from the middle and near the end of database table.

First, execution time result for hundred thousand records:

SELECT SINGLE vs FOR ALL ENTRIES
Record interval: 1-100.000 / Execution time for SEARCH method: 100,9 seconds

It seems that performance difference is notable for primary key access against secondary index access. Let’s see execution time results for 500 records from the beginning, middle and the end of the table:

SELECT SINGLE vs FOR ALL ENTRIES
Record interval: 1-500 / Execution time for SEARCH method: 0,2 seconds
SELECT SINGLE vs FOR ALL ENTRIES
Record interval: 3.000.001-3.000.500 / Execution time for SEARCH method: 0,3 seconds
SELECT SINGLE vs FOR ALL ENTRIES
Record interval: 6.000.001-6.000.500 / Execution time for SEARCH method: 0,4 seconds

That is blazing fast. 500 SELECT SINGLEs take almost no time with secondary index access (and most probably with primary key access).

SELECT … FOR ALL ENTRIES and READ TABLE in LOOP

Below program is pretty much like the previous one but this time we select the result set with FOR ALL ENTRIES and search on the internal table with READ TABLE in LOOP. This time we don’t use a hashed table because we can’t exactly be sure that our search key is unique.

After running three times:

SELECT SINGLE vs FOR ALL ENTRIES
Record interval: 1-500 / Execution time for SEARCH method: 80,2 seconds
SELECT SINGLE vs FOR ALL ENTRIES
Record interval: 3.000.001-3.000.500 / Execution time for SEARCH method: 135,6 seconds
SELECT SINGLE vs FOR ALL ENTRIES
Record interval: 6.000.001-6.000.500 / Execution time for SEARCH method: 257,9 seconds

Result is somewhat surprising. Previous test’s winner is big time loser of this test. Plus the placement of data also causes a difference. This might well be because of the data retrieval routine but result is still decisive.

Conclusion

This test tells us that we should not use  FOR ALL ENTRIES with a non-primary key access. It has a significant processing time even with a record count as low as 500.

Leave a Reply