Runtime increases with Collect statement

[former_member308418]

Dear All,

I am fetching data from my custom table. The table holds a large amount of data. I need to do sum on some columns. So i used COLLECT to do it. But it takes more time than SUM in select statement. Please tell me why this is happening. I mean to say why Collect is taking more time than SUM.

With best regards.

[former_member302911]

Hi,

To increase performance, try to use TYPE SORTED TABLE with unique-key for yours internal tables.

Regards,

Angelo.

[former_member308418]

I have used it but could not use Unique key as i have no primary field in my internal table. So, I have used non unique key. But problem is the same.

[former_member302911]

Post your code for a better understanding of the problem.

You can also restrict the number of columns in select, with really necessary columns,  to increase performance of itab loop.

[former_member308418]

Here is my internal table declaration.

TYPES: BEGIN OF ty_tab,
vkorg  TYPE ztable-vkorg,
vtweg  TYPE ztable-vtweg,
bzirk  TYPE ztable-bzirk,
kvgr2  TYPE ztable-kvgr2,  " Zone
vkbur  TYPE ztable-vkbur,             " Sales office
kvgr1  TYPE ztable-kvgr1,  " Salaes Resion
vkgrp  TYPE ztable-vkgrp,  " Salaes Group
vmtyp  TYPE ztable-vmtyp,  " Market Type (Intercompany Code)
mtnmg  TYPE ztable-mtnmg,  " Main Material Group
tgvlu  TYPE ztable-tgvlu,  " Target Vales for the specific month
vvv02  TYPE ce11000-vvv02,  " Sale, sale cancel, return, return
vvv03  TYPE ce11000-vvv03,     " Discount
vvv27  TYPE ce11000-vvv27,     " zvat ampunt (TAX)
vvv02_ts  TYPE ce11000-vvv02,     " Todays sale (as on sale)
vvv02_zfd_s1 TYPE ce11000-vvv02,   " Sale Value (Cummilative value)
vvv02_re_s2  TYPE ce11000-vvv02,   "  (Cummilative value)
net_sale TYPE ce11000-vvv02,
remin    TYPE ce11000-vvv02,  " Remaning Amount ( tgvlu - net_sale )
perct    TYPE ce11000-vvv02,     " Achive percentage
bezei_sr TYPE t005u-bezei,       "Sales Resion name
bztxt_sd TYPE t171t-bztxt,       "Salaes Distric name
bezei_sg TYPE tvgrt-bezei,       "Sales Group Name
fkart    TYPE ce11000-fkart,

END OF ty_tab.

DATA  : itabTYPE SORTED TABLE OF ty_tab WITH NON-UNIQUE KEY table_line.

[kiran_k8]

Angelo,

Never used this kind of ITAB declaration.

Can you please let us know how the below declaration will improvise performance

"DATA  : itabTYPE SORTED TABLE OF ty_tab WITH NON-UNIQUE KEY table_line."

Thanks,

K.Kiran.

[former_member302911]

The best way is to use unique-key for faster accessing to the desidered record when use TYPE SORTED TABLE, when read table, binary search is performed automatically. Your declaration is the same of TYPE STANDARD TABLE with sorted records.

Angelo.

[Former Member]

Please post your code, because your non-numeric field characteristics length may be high, due to this performance will decrease.

Regards,

Vadamalai A

[RaymondGiuseppi]

COLLECT statement will be executed on Application Server, when Aggregates will be shared between Application Server and Database Server. Also look at online documentation for

COLLECT should only be used if internal tables are to be created that are genuinely unique or compressed. In this case, COLLECT can greatly benefit performance. If uniqueness or compression are not required, or the uniqueness is guaranteed for other reasons, the INSERT statement should be used instead.

Also don't use a SORTED TYPE, if it does not help the COLLECT statement, let the system build its own temporary hash administrator, especially do not use  a TABLE-LINE key that contains the summed quantity/amount. If you need a sorted type, group not summed field at start and define as key fields.

Regards,

Raymond