Q: ABAP code from db to memory decreases performance?

[mikael_kilaker]

Hi Gurus,

We have a problem with some ABAP code (a start routine in a BI load). Basically the situation is: we had some code that builds a hierarchy (and inserts into hierarchy table) based on an attribute load, which worked fine but was to slow.

As we do not need the hierarchy anymore we changed the code to only build the hierarchy in memory (the reason why we need it at all is because building this is the only way we can ensure to load the right records in the attribute load) and now, it is sloweru2026.which we do not understand.

In general we have replaced:

SELECT SINGLE * FROM /BIC/HZTVFKORG INTO nodelast

WHERE nodeid = lastnode2.

With:

READ TABLE VirtHierarchy INTO nodelast

WITH KEY nodeid = lastnode2.

And replaced:

UPDATE /BIC/HZTVFKORG FROM nodelast.

With:

MODIFY TABLE VirtHierarchy FROM nodelast.

And replaced:

INSERT INTO /BIC/HZTVFKORG VALUES node.

With:

APPEND node TO VirtHierarchy.

As we see it, this should increase the performance of the start routine and the load (it takes several hours for just 50000 records), but it is actually running slower now...

Does anybody have any idea about why this is not improving performance?

Thank you in advance,

Mikael

[mikael_kilaker]

Hi Gurus,

We have a problem with some ABAP code (a start routine in a BI load). Basically the situation is: we had some code that builds a hierarchy (and inserts into hierarchy table) based on an attribute load, which worked fine but was to slow.

As we do not need the hierarchy anymore we changed the code to only build the hierarchy in memory (the reason why we need it at all is because building this is the only way we can ensure to load the right records in the attribute load) and now, it is sloweru2026.which we do not understand.

In general we have replaced:

SELECT SINGLE * FROM /BIC/HZTVFKORG INTO nodelast

WHERE nodeid = lastnode2.

With:

READ TABLE VirtHierarchy INTO nodelast

WITH KEY nodeid = lastnode2.

And replaced:

UPDATE /BIC/HZTVFKORG FROM nodelast.

With:

MODIFY TABLE VirtHierarchy FROM nodelast.

And replaced:

INSERT INTO /BIC/HZTVFKORG VALUES node.

With:

APPEND node TO VirtHierarchy.

As we see it, this should increase the performance of the start routine and the load (it takes several hours for just 50000 records), but it is actually running slower now...

Does anybody have any idea about why this is not improving performance?

Thank you in advance,

Mikael

[Former Member]

If you are using standard tables, try using sorted or hashed tables instead. Reading standard tables without either an index or using the binary search option will be very slow for a large number of records.

The same if you modify a standard table without using the index position.

Rob

[Former Member]

You can use secondary indexes to internal table , use sorted internal table instead of standard.

This will increase ur performance since the number of internal table entries is quite very large.

Up to and including Release 7.0 EhP1, internal tables cannot include secondary indexes.

DATA : itab TYPE SORTED TABLE OF spfli

WITH UNIQUE KEY primary_key COMPONENTS carrid connid

WITH NON-UNIQUE HASHED KEY secondary_key COMPONENTS cityfrom cityto

LOOP AT ... USING KEY secondary_key

...

ENDLOOP.

Situations for using secondary keys for internal tables

1) Large internal tables with more than 50 entries

2) No or only few modifications internal tables

[Former Member]

Dear M,

Instead of Code

SELECT SINGLE * FROM /BIC/HZTVFKORG INTO nodelast

WHERE nodeid = lastnode2.

With:

READ TABLE VirtHierarchy INTO nodelast

WITH KEY nodeid = lastnode2.

use

1: select single <fieldname1>...fieldname n> FROM /BIC/HZTVFKORG INTO nodelast

WHERE nodeid = lastnode2.

"""try to use some more key.. ask client for the condition..

READ TABLE VirtHierarchy INTO nodelast

WITH KEY key1 key2.

2: create index for the table. It can minimize the runtime to 90%.

Good Luck.

Vivek Singh

SPRL

[Former Member]

Hello Mikael Kilaker ,

one issue we faced in our extraction is select single written inside loop statemnet thats will make code very slow or in some cases goes to dump. so i would suggest don't write select single statement inside any loop statement.

[Former Member]

Dear Mikael Kilaker,

There are few reason:

1. Data overload in memory.

, if you try to execute

SELECT SINGLE * FROM /BIC/HZTVFKORG INTO nodelast
WHERE nodeid = lastnode2.
With:
READ TABLE VirtHierarchy INTO nodelast
WITH KEY nodeid = lastnode2.
And replaced:
UPDATE /BIC/HZTVFKORG FROM nodelast.
With:
MODIFY TABLE VirtHierarchy FROM nodelast.
And replaced:
INSERT INTO /BIC/HZTVFKORG VALUES node.
With:
APPEND node TO VirtHierarchy.

inside any loop conditions, this approach will make the system slow because it will load entire table into memory then system still need to cater space for selected value thus make system not really effective when you execute large volume of data.

2. Unsorted data.

It is really great practice if you sort nodelast. It is extra steps but the effect greatly decreased response time when system manipulating sorted data in the internal table.

3. Use binary search in READ table.

Try to use this code

READ TABLE VirtHierarchy INTO nodelast

WITH KEY nodeid = lastnode2 BINARY SEARCH.

this practice also will increase performance when you execute large data inside internal table.

Do reward points if this helps you