topic SELECT statement on EKPO table in Application Development and Automation Discussions

SELECT statement on EKPO table

Former Member — Fri, 09 Oct 2015 07:19:20 GMT

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

Re: SELECT statement on EKPO table

Former Member — Fri, 09 Oct 2015 07:48:13 GMT

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.

Re: SELECT statement on EKPO table

Former Member — Fri, 09 Oct 2015 07:54:09 GMT

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

Re: SELECT statement on EKPO table

Former Member — Fri, 09 Oct 2015 08:15:43 GMT

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

Re: SELECT statement on EKPO table

michael_kozlowski — Fri, 09 Oct 2015 08:19:52 GMT

Hi,

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

Regards

Michael

Re: SELECT statement on EKPO table

SujeetMishra — Fri, 09 Oct 2015 08:22:42 GMT

Hello Tim,

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

Thanks,

Sujeet

Re: SELECT statement on EKPO table

Former Member — Fri, 09 Oct 2015 10:16:58 GMT

Hi Tim,

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

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

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

Cheers,

Varun

Re: SELECT statement on EKPO table

Jelena_Perfiljeva — Fri, 09 Oct 2015 21:20:17 GMT

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.