Optimizer Query

[Former Member]

Hello experts, i hope you can help me to improve this query:

This is my data entry screen

SELECTION-SCREEN BEGIN OF BLOCK b01 WITH FRAME TITLE text-b01.
PARAMETERS: p_werks TYPE t001l-werks OBLIGATORY DEFAULT 'PLJA'.

SELECT-OPTIONS: s_budat FOR mkpf-budat OBLIGATORY DEFAULT sy-datum,
                 s_aufnr FOR aufk-aufnr,
                 s_matnr FOR mara-matnr,
                 s_auart FOR aufk-auart,  "1@A - HCO
                 s_dispo FOR  afko-dispo. "1@A - HCO

SELECTION-SCREEN END OF BLOCK b01.

Here is the delay time

SELECT rsnum rspos matnr werks lgort bdmng meins shkzg stlnr ausch
             INTO TABLE ti_resb
             FROM resb FOR ALL ENTRIES IN ti_aufm2
            WHERE aufnr EQ ti_aufm2-aufnr

Also believed as the field index AUFNR and so speed up the query, but get this message "Index does not exist in MaxDB database system", the query is slow.

I hope your help please to optimize this query.

Thanks

Best regards

[Former Member]

Hi

You need to create the index in DB physically b trx SE14, but the table AFKO has the field RSNUM so perhaps you don't need the index

Max

[Arun_Prabhu_K]

Hello Brujo.

1) May be the index would not have been transported to PRD.

    Check->SE11->RESB->Indexes->Version management.

2) Also check if the internal table is not initial before using FOR ALL ENTRIES in select statement     because if it is initial, it will fetch all records.

Regards.

[Former Member]

Hi,

If the table on which the For All Entries IN clause is based is empty, all rows are selected into the destination table. Hence it is advisable to check before-hand that the first table is not empty.

If the table on which the For All Entries IN clause is based is very large, the performance will go down instead of improving. Hence attempt should be made to keep the table size to a moderate level

• check whether secondary index is there in the table
• goto se11->resb->index
• pls check this thread http://scn.sap.com/thread/316650
• If Second index is created then

create secondary indexes very rarely in actual project

pls check this thread

http://scn.sap.com/thread/2051166

[Former Member]

Hi ,

Apart from Index , You can also check on the unique records Vs Duplicate records in table ti_aufm2.

If you have multiple duplicated records , It would cause a performace drain.

Sort the table ti_aufm2 with key and delete the duplicate records before going for all entries selection.

You need to check on the index quality as well with the help of BASIS team.

Thanks,

AJ.