topic Optimizer Query in Application Development and Automation Discussions

Optimizer Query

Former Member — Mon, 23 Sep 2013 16:04:31 GMT

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

Re: Optimizer Query

Former Member — Mon, 23 Sep 2013 16:37:03 GMT

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

Re: Optimizer Query

Arun_Prabhu_K — Tue, 24 Sep 2013 03:08:56 GMT

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.

Re: Optimizer Query

Former Member — Tue, 24 Sep 2013 04:57:57 GMT

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

Re: Optimizer Query

Former Member — Tue, 24 Sep 2013 06:21:06 GMT

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.