topic Re: Problem with select fetching data and processing in Application Development and Automation Discussions

Problem with select fetching data and processing

Former Member — Sat, 17 Sep 2016 15:13:16 GMT

Hi Experts,

I am doing performance tuning for a program.

I am fetching data from EKET table based on EBELN , EBELP and EINDT field entries into I_EKET table.

Than I am processing the entries in I_EKET table as

SELECT EBELN EBELP EINDT

FROM EKET

INTO TABLE I_EKET

FOR ALL ENTRIES IN I_EKPO

WHERE EBELN EQ I_EKPO-EBELN

AND EBELP EQ I_EKPO-EBELP

AND EINDT IN S_EINDT.

IF I_EKET[] IS NOT INITIAL.

SORT I_EKET BY EBELN EBELP.

DELETE ADJACENT DUPLICATES FROM I_EKET COMPARING ALL FIELDS.

is it possible to write the sort and delete adjacent duplicates inside the select query itself.

Thanks,

Re: Problem with select fetching data and processing

Former Member — Sat, 17 Sep 2016 21:25:25 GMT

Hi Ramya G,

I think following select statement will cover your duplicates:

SELECT distinct EBELN EBELP EINDT

FROM EKET

INTO TABLE I_EKET

FOR ALL ENTRIES IN I_EKPO

WHERE EBELN EQ I_EKPO-EBELN

AND EBELP EQ I_EKPO-EBELP

AND EINDT IN S_EINDT.

Distinct will filter your duplicates. BUT, distinct also has a low performance.

(ABAP Performance tips - Contributor Corner [Read-only] - SCN Wiki)

AND, if you are using a HANA database, it is possible that your data will be unsorted.

()

If your data needs to be sorted and you are on a HANA database, then you will have to use the order by statement.

With a for all entry you can only use 'order by primary key'. However you do not use all the key fields in your select statement so it will not be possible to use the 'order by primary key'.

So, I think the best way to solve this, is by removing the for all entries.

I see that you are using a for all entries on EKPO. Did you do a select statement on EKPO before the select statement above? Maybe you can Join them, hereby avoiding the use of a 'for all entries' and nicely place the needed ekpo and eket data in one itab. And place a 'sort by ebeln, ebelp' and a 'group by ebeln ebelp...' in the joined select statement. This is of course only valid if you do not have a for all entries in you EKPO select statement.

Re: Problem with select fetching data and processing

matt — Mon, 19 Sep 2016 06:14:57 GMT

It doesn't matter whether you're using HANA database or ORACLE or any other. The order of returned records is never guaranteed to be sorted! If you want it ordered, you must specify an ORDER BY clause.

Re: Problem with select fetching data and processing

former_member405170 — Mon, 19 Sep 2016 06:42:49 GMT

HI Ramya,

Go for Secondary index to remove any duplicate entries while fetching data.

Thanks,

Paul