In my program, I need to perform a select on the CDPOS cluster table. However, this process is very inefficient and, depending on the amount of data, it can take several minutes or simply hit a timeout. In a test I conducted, retrieving 35 IDs took 1 minute and 15 seconds, and in a second test with 90 IDs, the program timed out.

Current process: First, I perform a select on the EKKO table with purchase orders filtered by the date provided by the user. Then, I filter the IDs obtained from the EKKO table in the CDHDR table. The remaining IDs from the CDHDR table are then used in the select on the CDPOS table. Finally, I use a check to filter non-key fields.
Note: I filter by IDs because the CDPOS table does not have date fields.

The SELECT in which I am having performance issues:

IF lt_chunk_cdhdr is not INITIAL.


    SELECT OBJECTID
           CHANGENR
           FNAME
           CHNGIND
           VALUE_NEW
           VALUE_OLD
      FROM CDPOS
      INTO TABLE lt_temp_cdpos
      FOR ALL ENTRIES IN lt_chunk_cdhdr
      WHERE OBJECTID = lt_chunk_cdhdr-objectid.

    LOOP AT lt_temp_cdpos INTO ls_cdpos.
      CHECK ls_cdpos-fname = 'FRGKE' AND ls_cdpos-value_new = '2'
         OR ls_cdpos-fname = 'MWSKZ'
         OR ls_cdpos-fname = 'KEY' AND ls_cdpos-chngind = 'I'
         OR ls_cdpos-fname = 'BRTWR'
         OR ls_cdpos-fname = 'NETWR'
         OR ls_cdpos-fname = 'ZTERM'
         OR ls_cdpos-fname = 'INCO1'.

      APPEND ls_cdpos TO lt_cdpos.
    ENDLOOP.
ENDIF.

I tried to improve the efficiency of the select using tips I found on the internet. One of these tips was to use only key fields in the WHERE clause when working with cluster tables and then use the CHECK command to filter the retrieved data again. However, even after following this advice, the efficiency is still insufficient. If the number of IDs to be filtered is close to 80, the program does not complete execution.

“Cluster table: the exact opposite occurs: due to the storage method of these tables, the WHERE clause should contain only the key fields, and the other fields should be checked using the CHECK command. The database cannot process cluster tables as it processes transparent tables. Forcing the database to decompress and check fields (in the case of a select with non-key fields in the WHERE clause) is less efficient, in most cases, than qualifying with key fields only and leaving the CHECK for non-key fields after they have been returned.”
Reference (in Portuguese): https://marcolin.wordpress.com/2010/04/08/grandes-viloes-da-performance-sap/

Another attempt I made was to limit the search period to 31 days. However, depending on the month and the number of purchase orders, this can still cause a timeout.