topic Re: Runtime increases with Collect statement in Application Development and Automation Discussions

Runtime increases with Collect statement

former_member308418 — Mon, 26 May 2014 07:59:38 GMT

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.

Re: Runtime increases with Collect statement

former_member302911 — Mon, 26 May 2014 08:08:05 GMT

Hi,

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

Regards,

Angelo.

Re: Runtime increases with Collect statement

former_member308418 — Mon, 26 May 2014 08:16:41 GMT

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.

Re: Runtime increases with Collect statement

former_member302911 — Mon, 26 May 2014 08:23:28 GMT

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.

Re: Runtime increases with Collect statement

Former Member — Mon, 26 May 2014 08:23:46 GMT

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

Regards,

Vadamalai A

Re: Runtime increases with Collect statement

RaymondGiuseppi — Mon, 26 May 2014 08:28:28 GMT

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

Re: Runtime increases with Collect statement

former_member308418 — Mon, 26 May 2014 09:42:37 GMT

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.

Re: Runtime increases with Collect statement

kiran_k8 — Mon, 26 May 2014 11:29:24 GMT

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.

Re: Runtime increases with Collect statement

former_member302911 — Mon, 26 May 2014 12:54:19 GMT

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.