Secondary Index on Select Statment

[former_member392636]

SELECT * FROM faglflexa
                INTO CORRESPONDING FIELDS OF TABLE lt_faglflexa
                FOR ALL ENTRIES IN lt_faglflext
                WHERE ryear  =  iv_ryear AND    " 2019
                      rldnr  IN it_rldnr AND    " 0L
                      rbukrs =  lt_faglflext-rbukrs AND
                      racct  =  lt_faglflext-racct  AND
                      prctr  =  lt_faglflext-prctr  AND
                      rfarea =  lt_faglflext-rfarea AND
                      poper  IN lt_poper_range.  " 02
for fagleflext 11,216 entries, this select statement is taking more than 50 min to retrieve entries. This has to be tuned up. However, I have one secondary index on faglflexa i.e 
RCLNT
RLDNR
RBUKRS
RACCT
RYEAR
POPER
If I do use the above mentioned field sequence in my select query, would it be call as secondary index and speed the performance.  or is there any new ABAP 7.4 syntax, which I can use, please help.

Regards,
Himansu

[raymond_giuseppi]

Did you execute a SQL trace with ST05 to analyze the access path. But don't expect too much performance as long as you use a FOR ALL ENTRIES clause. Can you consider using a JOIN or a subquery to remove this clause?

[matt]

Seconded. Ditch the FOR ALL ENTRIES, use a JOIN.

[former_member392636]

I changed the querry, and my small doubt is in allready created secondary index on fagleflexa, there is RCLNT is listed, If I use secondary index without rclnt and use other listed fields, would it work as secondary index. 




SELECT a~ryear,  a~docnr, a~rldnr, a~rbukrs, a~docln, a~rmvct, a~racct, a~prctr,
       a~rfarea, a~rassc, a~hsl,   a~poper,  a~budat, a~buzei, a~bschl
               INTO TABLE    @t_faglflexa
               FROM          faglflexa   AS a
               INNER JOIN    faglflext   AS b                 ##DB_FEATURE_MODE[TABLE_LEN_MAX1]


                                          ON ( a~rldnr  = b~rldnr   AND  a~rbukrs = b~rbukrs AND a~racct  = b~racct
                                          AND  a~prctr  = b~prctr   AND  a~ryear  = b~ryear  AND  poper    = '02' )
                WHERE ( a~rldnr  = b~rldnr  AND  a~rbukrs = b~rbukrs
                 AND    a~racct  = b~racct  AND  a~ryear  = b~ryear   AND  poper    = '02' ).


Secondary fields on FAGLFLEXA
RCLNT
RLDNR
RBUKRS
RACCT
RYEAR
POPER

[DoanManhQuynh]

I used to get performance issues with New GL table too. Its not only about index or key, its also about table size (may have million records..). What i did first is check how does it take to run tcode FAGLL03 with same conditions, if it the same maybe you dont have much chance to boost it. anw, you may check function group: FAGL_ITEMS_SELECT, there some function module and you can see how the sql built there.