SELECT statement on EKPO table

[Former Member]

Hi,

I have a simple select statement on table EKPO, looks like this:

SELECT ebeln

              ebelp

              aedat FROM ekpo

              INTO CORRESPONDING FIELDS OF TABLE it_ekpo

              WHERE ebeln IN ra_ebeln AND

                            aedat IN ra_aedat.

Range for EBELN is usually empty and in the change date range there is one entry SY-DATUM - 2 days, so getting all entries changed during last 3 days.

The select takes over 10 minutes. The only improvement I see is to make an index for AEDAT which does not exist yest, or does anyone have another suggestion?

Thanks,

Tim

[Former Member]

I dont think there is any other solution. Try to create INDEX on AEDAT and put a select on AEDAT only as in your case EBELN is having blank range.

[Former Member]

I dont think there is any other solution. Try to create INDEX on AEDAT and put a select on AEDAT only as in your case EBELN is having blank range.

[Former Member]

Hi,

Put all fields in it_ekpo as in the order of select query. i.e. eblen, ebelp and aedat and after that remove CORRESPONDING FIELDS from select.

I hop this will improve the performance.

Regards

Ajay

[Former Member]

Hello Tim,

try to select last 1000 record this:

data: lv_ebeln type ekpo-vbeln.

select max(ebeln) from ekpo into lv_ebeln.

lv_ebeln = lv_ebeln - 1000.

SELECT ebeln

              ebelp

              aedat FROM ekpo

              INTO CORRESPONDING FIELDS OF TABLE it_ekpo

              WHERE ebeln IN ra_ebeln AND

                             ebeln > lv_ebeln

                            aedat IN ra_aedat.

This Solution works only if you have one number range for ekes-ebeln.

Regards,

Richard

[michael_kozlowski]

Hi,

please check table CDHDR OBJECTCLAS = EINKBELEG. Maybe you kann use this table in a JOIN to improve performance.

Regards

Michael

[SujeetMishra]

Hello Tim,

Yes.. best way is to create Index for date field.

Thanks,

Sujeet

[Former Member]

Hi Tim,

You can check in the following for SELECT query. Certain points to ponder upon -

1. Avoid using INTO CORRESPONDING FIELDS OF TABLE,
2. Use of primary key fields in WHERE clause, and
3. Avoid using SELECT * also

Apart from these you can also go for creating secondary indexes. There will definitely an performance improvement.

Cheers,

Varun

[Jelena_Perfiljeva]

I'm sure Tim appreciates the tips, except:

- he's clearly not using SELECT *

- his SELECT already using the primary key in this table and he's already clarified search has to be by date

- INTO CORRESPONDING is an urban legend - see this document.

As others already said - index is pretty much the only option, unfortunately. Change documents are not indexed by date either.

Thank you.