Is there an elegant way to get the relevant entry from an internal table based on from & to date?

[BaerbelWinkler]

I'm currently working on performance improvements in a program which relies heavily on SELECT ... ENDSELECTs to get data from Z-tables based on - apart from the main key field(s) - valid from (key) and to (non-key) fields. These SELECTs are (obviously!) bad as they happen within a loop for thousands of entries and I'd like to improve this by switching to first getting the data into internal tables and then reading those as needed. I however cannot just get the entries which for example fit sy-datum as the relevant date can also come from table content like the invoice date in VBRK-FKDAT. So, I don't know beforehand which of potentially multiple entries for the main key fields is of interest once the date comes into play.

For the SELECTs this is of course easily possible by WHERE-options like these:

 SELECT * FROM  ztable
          INTO  ls_ident
         WHERE zz_kunnr_zr = p_kunnr
           AND   datab <= v_fkdat
           AND   datbi >= v_fkdat.
...
ENDSELECT.

I'm wondering if there's now an option to do something comparable for an internal table short of looping through it with a WHERE-clause? I looked through the "new" syntax available since NW 740 SP08 (e.g. here) but didn't really find anything which could do the trick. Have I missed it or isn't there one (or at least not an easy/elegant one)?

Thanks much and Cheers

Bärbel

[MateuszAdamus]

Hello 8b889d0e8e6f4ed39f6c58e35664518f

Won't LOOP .. WHERE condition do the trick?

Also, make internal table have a primary/secondary key with the fields, it will help for performance.

Kind regards,
Mateusz

Edit: another idea, if LOOP is not the solution, would be to DELETE the items that do not fit the criteria provided in the SELECT. Basically, DELETE .. WHERE with the inversed SELECT WHERE condition. Then all you have in the internal table are the records that fit the SELECT WHERE criteria and you can use these.

[BaerbelWinkler]

Hi Mateusz,

thanks for your quick reply!

Possibly a stupid question, but I'll ask anyway:

If I define the internal table as sorted with the key fields, will this then ensure that the loop across the internal table with a where clause only reads the applicable entry, meaning that it won't always read through the whole internal table as it does with a standard table (or simple internal table where nothing is specified)? The program I'm trying to improve was originally written in 2000 and changed several times since then but never with improving performance in mind, so in some instances it now takes up to 6 hours for one batch-run.

Cheers

Bärbel

[MateuszAdamus]

Hi Bärbel

Yes, it does, if you use the correct fields of the key (all of them or at least starting from the left-hand side of the key). If you have secondary keys then you can specify which one should be used for the LOOP, to make sure that the performance is correct.

https://help.sap.com/doc/abapdocu_752_index_htm/7.52/en-US/abenkeyname.htm

Kind regards,
Mateusz

[kjyothiraditya]

Adding to what Mateusz mentioned, if I just need 1 entry then you can use LOOP... WHERE with EXIT.

[geert-janklaps]

Hi,

If you have for example the VBRK data in an internal table you could try using a FOR ALL ENTRIES clause in the where condition. Also maybe have a look at limiting the select statement to the fields used by the report instead of using SELECT * .

SELECT * FROM  ztable
          INTO TABLE  @lt_ident
          FOR ALL ENTRIES IN @lt_vbrk
          WHERE zz_kunnr_zr = @p_kunnr
            AND datab <= @lt_vbrk-fkdat
            AND datbi >= @lt_vbrk-fkdat.

Looking at this blog, FOR ALL ENTRIES still seems to be one of the most performant options.

Best regards,

Geert-Jan Klaps

[BaerbelWinkler]

8d8214c7f9734f45be69f95cc0d5aeee

Thanks Geert-Jan,

yes, I want to get the entries into an itab and won't even need the FAE in most cases as the Z-Tables at most have a few thousand entries with just a few fields (<10). The internal table will however contain multiple entries for different valid-dates for the main keys and I'm wondering how I can then best read the internal table.

Cheers

Bärbel

[geert-janklaps]

Hi 8b889d0e8e6f4ed39f6c58e35664518f,

Maybe if you have a lot of date ranges you need to process, it might be an option to take the lowest date and the highest date and get the full range and do a single select statement.

Another thing that might work is creating a range table and filling the range table with all date ranges needed and perform the select with the range table.

Best regards,

Geert-Jan Klaps

[BaerbelWinkler]

8d8214c7f9734f45be69f95cc0d5aeee

Hi Geert,

I'm not really worried about the initial SELECT to get the data from the Z-table into the internal table. I'm wondering about how to then get the correct entry from it efficiently if I have to look for one where the date fits within the date-range. Mateusz & Mahesh have provided two suggestions I'm going to try-out (time permitting).

Thanks & Cheers

Bärbel

[maheshpalavalli]

You can use "Filter" from the new ABAP system. But he table has to be sorted with a key. I don't think this will improve the performance, just posting this as you have mentioned checking the new syntax.

DATA:
  vicncn_sort     TYPE STANDARD TABLE OF vicncn WITH NON-UNIQUE SORTED KEY test1 COMPONENTS recnbeg recnendabs.

SELECT *
  FROM vicncn
  INTO TABLE @vicncn_sort.

DATA(begdate) = CONV datum( '20140317' ).
DATA(enddate) = CONV datum( '20160316' ).

DATA(vicncn_filter) = FILTER #( vicncn_sort USING KEY test1 WHERE recnbeg GE begdate AND recnendabs LE enddate ).