topic Re: itab and sorting efficiency in Application Development and Automation Discussions

itab and sorting efficiency

Former Member — Wed, 27 Dec 2006 07:17:21 GMT

Hi,

I only want the earliest date of a date field (non unique) in the database table...

my way of doing...

1) create a standard itab with only the date field.

2) select all the records for that date field into the itab.

3) collect into another standard itab with same structure to remove duplicates.

4) sort the itab in acsending.

5) read from the itab with index 1.

any other better ways in terms of performance or code efficiency?

if i do the sort in the select statement (eg. order by), it would be slower right?

Thanks,

Charles

abap newbie

Re: itab and sorting efficiency

Former Member — Wed, 27 Dec 2006 07:54:23 GMT

Do the following steps

1) create a standard itab with only the date field.

2) select all the records for that date field into the itab.

3)Sort them

4)remove duplicates using the keyword DELETE ADJACENT DUPLICATES FROM <itab> COMPARING <date(field)>

5)read itab with index 1.

Regards,

Balaji

Re: itab and sorting efficiency

jayanthi_jayaraman — Wed, 27 Dec 2006 07:57:47 GMT

Hi,

select min (dat) from db into variable.

Re: itab and sorting efficiency

Former Member — Wed, 27 Dec 2006 08:13:35 GMT

wont that cause performance issues?

because that is done at db level... & not at program level...

Re: itab and sorting efficiency

Former Member — Wed, 27 Dec 2006 08:14:26 GMT

I thought doing a collect is better than doing a delete adjacent duplicates...?

Re: itab and sorting efficiency

jayanthi_jayaraman — Wed, 27 Dec 2006 08:16:48 GMT

Hi,

Just use SE30 to analyze performance for the same.

Re: itab and sorting efficiency

Former Member — Wed, 27 Dec 2006 08:45:09 GMT

Hi,

It depends on the type of table you use.

1)With a COLLECT for standard tables, a hash function is created internally. Using this hash function results in good performance. The hash function for standard tables is, however, non-persistent, since it is destroyed by each INSERT, DELETE, and so on; after which performance is reduced with a COLLECT all fields that are not part of the key must have a numeric type.

2)With the COLLECT statement, the contents of the work area is added to an entry with the same key or added to the table as a new entry. This allows you to create aggregated internal tables, thereby reducing data volume.COLLECT searches in the internal table for a data record according to the table type and defined key. If it finds an entry in the table, it adds all numeric fields of the work area or header that are not part of the key to the corresponding fields of the entry. If no entry is found, the contents of the work area or header row are added as a new table entry.You can only use the COLLECT statement with internal tables whose non-key fields are all numeric (type I, P, or F). If not, you get a syntax error. From SAP Basis release 4.0A, the table key may contain numeric fields.

The COLLECT statement is internally optimized for standard tables so that the response time is constant. This optimization applies as long as the tables are not modified with DELETE, INSERT, or SORT (non-persistent hash function). Using the latter instructions loses the rapid internal access, and leads to linearly increasing access costs for the COLLECT.

3)With hashed and sorted tables, the cost of the COLLECT statement is constant, even if you use DELETE, INSERT or SORT.

For the DELETE ADJACENT DUPLICATES the internal table should be sorted one before you write the statement.

Thanks,

Balaji

Re: itab and sorting efficiency

Former Member — Wed, 27 Dec 2006 09:11:15 GMT

hmmm... ok..

so in my case... i do a collect to another standard itab and do a sort after that...

that would destroy the performance of the intended collect...?

so its better to do a sort then delete adj duplicates rather than collect..

am I right?

Thanks,

Charles

Re: itab and sorting efficiency

Former Member — Wed, 27 Dec 2006 09:15:43 GMT

Hi Charles,

Actually Collect has a different usage from deleting the duplicates.

like if original table has

abc 20.0

abc 40.0

def 50.0

after collect,

it would be

abc 60.0

def 50.0

after delete adjacent duplicates, it would look like:

abc 20.0

def 50.0

REgards,

Ravi