To optimize expensive select queries ...

[former_member192432]

Hi,

I am optimization for report in sap abap.

Below query for EKPO , EKBE and BKPF is taking much time , i tried but i did not find any alternate subindexes or any other solution . please any body suggest me how optimize it .. Is there any other way to fetch the data or what i can do..

SELECT ebeln ebelp pstyp bednr mtart mwskz FROM ekpo " Addition of bednr mtart mwskz by venkat on 21/9/2009

INTO TABLE t_ekpo

FOR ALL ENTRIES IN t_ekko

WHERE ebeln = t_ekko-ebeln AND

loekz = ' ' AND

pstyp IN r_pstyp.

-

---

SELECT ebeln ebelp belnr gjahr lfbnr SHKZG menge FROM ekbe

INTO TABLE t_pohis

FOR ALL ENTRIES IN t_mseg

WHERE ebeln = t_mseg-ebeln AND

ebelp = t_mseg-ebelp AND

gjahr = t_mseg-mjahr AND

bewtp = 'Q' AND

lfbnr = t_mseg-lfbnr .

-

---

SELECT belnr bukrs gjahr awkey blart FROM bkpf

INTO TABLE t_bkpf

WHERE bukrs IN s_bukrs AND

gjahr IN r_year.

Regards

chetan

Moderator message - Please see before posting - post locked

Edited by: Rob Burbank on Oct 8, 2009 9:24 AM

[former_member192432]

Hi,

I am optimization for report in sap abap.

Below query for EKPO , EKBE and BKPF is taking much time , i tried but i did not find any alternate subindexes or any other solution . please any body suggest me how optimize it .. Is there any other way to fetch the data or what i can do..

SELECT ebeln ebelp pstyp bednr mtart mwskz FROM ekpo " Addition of bednr mtart mwskz by venkat on 21/9/2009

INTO TABLE t_ekpo

FOR ALL ENTRIES IN t_ekko

WHERE ebeln = t_ekko-ebeln AND

loekz = ' ' AND

pstyp IN r_pstyp.

-

---

SELECT ebeln ebelp belnr gjahr lfbnr SHKZG menge FROM ekbe

INTO TABLE t_pohis

FOR ALL ENTRIES IN t_mseg

WHERE ebeln = t_mseg-ebeln AND

ebelp = t_mseg-ebelp AND

gjahr = t_mseg-mjahr AND

bewtp = 'Q' AND

lfbnr = t_mseg-lfbnr .

-

---

SELECT belnr bukrs gjahr awkey blart FROM bkpf

INTO TABLE t_bkpf

WHERE bukrs IN s_bukrs AND

gjahr IN r_year.

Regards

chetan

Moderator message - Please see before posting - post locked

Edited by: Rob Burbank on Oct 8, 2009 9:24 AM

[Former Member]

Hi Chetan,

The EKPO query is perfectly ok as you are using PO no to get PO line items.

In the EKBO query you can add MBLNR also as your driver table is t_MSEG - but still it looks quite ok.

Put the initial check for the driver table in both these queries.

So where are you getting the performance problem ? In production or Quality ? It might be the case that database has too much data and it's time to archive some data so that the system runs better. Just check the no of entries in all these table.

About BKPF access - please see if yiu can use other tables like BSIS, BSID etc

-ashim