Issue with 'Parent-Child' hierarchy

[former_member199876]

Hi all

We have set up a 'Parent-Child' hierarchy in an Attribute view. The physical table in question is a simple two column table with "Parent" and "Child".

The hierarchy output worked without any problems when the record was not too high (~50,000).

But when we loaded data in the range of 1.5 billion records, there is no response coming back from HANA.

The "Select" operation on the hierarchy waits on another jobworker thread named (PopHierarchyCreate), which seems to run forever.

We are currently on SP08 and the table is not partitioned. We were not sure if partitioning would negatively affect Hierarchy performance.

Please let me know if anyone has experience with this kind of an issue and if there is any upper limit which can be processed through a 'Parent-Child' Hierarchy.

- Your expertise is very much required here

Regards

Ajay

[lbreddemann]

The PopHierarchyCreate operation performs an initial traversal of the hierarchy data.

This process - afaik - does scale with the input data size (in terms of hierarchy nodes).

If the process never comes to an end, then my guess would be that there is something fishy about the data.

You may want to have support to take a more detailed look into this.

- Lars

[former_member199876]

Thanks Lars for your response.

We have sampled small chunks of the same table and the hierarchy seems to work fine without errors.

Would there be some index server settings which should be tweaked to improve hierarchy performance or would partitioning help.

We are planning to run a stored proc overnight to dump the hierarchy output into a table and check the actual response time (if it completes).

Regards

Ajay

[lbreddemann]

Right now it is not clear what causes the long runtime so there is no way to tell if any parameter setting might help.

I don't see how partitioning would help here - after all it is one single hierarchy that gets build up here and there is no way to know upfront, which parts of the hierarchy are stored in which partition.

Did you check CPU/memory utilization during the HierarchyCreate ?

[former_member199876]

It worked when i dumped the hierarchy output directly into a physical table.

I guess HANA tried to materialize the whole hierarchy in-memory, iterating recursively through all the records, before displaying the results and that's why it took forever returning the results.

Trying to figure out why the insert into a physical table worked without any issues.

[lbreddemann]

Looking back at this thread it is yet unclear how you actually query the hierarchy information.

SAP HANA tries to push the materialization as far to the end of processing as possible and also to only compute data that is requested and/or required.

This means it is important to know the query you use on top of your model.

[former_member199876]

Hi Lars,

The query is a simple insert from the Hierarchy.

INSERT INTO "CLIENT_SCHEMA"."HIERARCHY_OUTPUT_TABLE"
SELECT '500', 'ABC', "QUERY_NODE","LEVEL","LEVEL_NAME","ORDINAL","IS_LEAF","PATH","PARENTS"
  FROM "_SYS_BIC"."AT_HIERARCHY_MASTER/hier/HIER"

The underlying table has about 85 million records.This clocked for almost 9 hours with no response and we had to kill the thread.

While checking the indexserver trace, it looks like the process was deadlocked with the mvcc garbage collection process. Here is an excerpt from the log.

[160239]{-1}[-1/-1] 2014-11-12 12:31:42.807852 e Statement        mvcc_anti_ager.cc(01218) : There are too many un-collected versions. ('number of versions for one of the partitions > 1000000' or 'maximum number of versions per record > 100000')
The transaction blocks the garbage collection of HANA database.:
CONNECTION_ID = 207518, HOST = *, VOLUME_ID = 2,
TRANSACTION_ID = "43", TRANSACTION_TYPE = "USER TRANSACTION",
PRIMARY_TRANSACTION_ID = "-1", PRIMARY_TRANSACTION_TYPE = "",
UPDATE_TRANSACTION_ID = "12123003", MIN_MVCC_SNAPSHOT_TIMESTAMP = 45047842,
TRANSACTION TOTAL EXECUTED TIME = 13220 sec
[160239]{-1}[-1/-1] 2014-11-12 12:31:42.807932 e Statement        mvcc_anti_ager.cc(01264) : There are too many un-collected versions. ('number of versions for one of the partitions > 1000000' or 'maximum number of versions per record > 100000')
The cursor possibly block the garbage collection of HANA database.:
CONNECTION_ID = 207518, CONNECTION_STATUS = RUNNING, HOST = host:30203, IDLE_TIME = 13220 sec,
STATEMENT_STATUS = ACTIVE, STATEMENT_LIFE_TIME = 13220 sec, START_MVCC_TIMESTAMP = 45047842, ISOLATION_LEVEL = READ COMMITTED,
SQL string = "INSERT INTO "CLIENT_SCHEMA"."HIERARCHY_OUTPUT_TABLE"
SELECT '500', 'ABC', "QUERY_NODE","LEVEL","LEVEL_NAME","ORDINAL","IS_LEAF","PATH","PARENTS"
FROM "_SYS_BIC"."AT_HIERARCHY_MASTER/hier/HIER"",
CLIENT_HOST = WORKLAPTOP, CLIENT_PID = 4152,
APPLICATION = HDBStudio, APPLICATIONSOURCE = csns.sql.editor.SQLExecuteFormEditor$2$1.run(SQLExecuteFormEditor.java:831);, APPLICATIONVERSION = 1.80.3, APPLICATIONUSER = ajay.a.venkatesh.
[160239]{-1}[-1/-1] 2014-11-12 12:31:48.256712 e Statement        mvcc_anti_ager.cc(01098) : long running cursor detected:
CONNECTION_ID = 207518, CONNECTION_STATUS = RUNNING, HOST = host:30203, IDLE_TIME = 13225 sec,
STATEMENT_STATUS = ACTIVE, STATEMENT_LIFE_TIME = 13225 sec, START_MVCC_TIMESTAMP = 45047842, ISOLATION_LEVEL = READ COMMITTED,
SQL string = "INSERT INTO "CLIENT_SCHEMA"."HIERARCHY_OUTPUT_TABLE"
SELECT '500', 'ABC', "QUERY_NODE","LEVEL","LEVEL_NAME","ORDINAL","IS_LEAF","PATH","PARENTS"
FROM "_SYS_BIC"."AT_HIERARCHY_MASTER/hier/HIER"",
CLIENT_HOST = WORKLAPTOP, CLIENT_PID = 4152,
APPLICATION = HDBStudio, APPLICATIONSOURCE = csns.sql.editor.SQLExecuteFormEditor$2$1.run(SQLExecuteFormEditor.java:831);, APPLICATIONVERSION = 1.80.3, APPLICATIONUSER = ajay.a.venkatesh.
[160239]{-1}[-1/-1] 2014-11-12 12:31:48.471069 e Statement        mvcc_anti_ager.cc(01167) : long running uncommitted write transaction detected:
CONNECTION_ID = 207518, LOGICAL_CONNECTION ID = 207518, CONNECTION_STATUS = RUNNING, HOST = host:30203,
TRANSACTION_ID = "43", TRANSACTION_TYPE = "USER TRANSACTION",
UPDATE_TRANSACTION_ID = "12123003", MIN_MVCC_SNAPSHOT_TIMESTAMP = 45047842,
TRANSACTION TOTAL EXECUTED TIME = 13203 sec,
CLIENT_HOST = WORKLAPTOP, CLIENT_PID = 4152,
CURRENT_STATEMENT = "INSERT INTO "CLIENT_SCHEMA"."HIERARCHY_OUTPUT_TABLE"
SELECT '500', 'ABC', "QUERY_NODE","LEVEL","LEVEL_NAME","ORDINAL","IS_LEAF","PATH","PARENTS"
FROM "_SYS_BIC"."AT_HIERARCHY_MASTER/hier/HIER"",
LAST_STATEMENT = "",
DB_USER = "HANA_USER", APPLICATION_USER = "ajay.a.venkatesh"
[160239]{-1}[-1/-1] 2014-11-12 12:31:54.059640 e Statement        mvcc_anti_ager.cc(01610) : M_MVCC_TABLES HOST = host:30203, NUM_VERSIONS = 11648577, MAX_VERSIONS_PER_RECORD = 924, MIN_SNAPSHOT_TS = 45047842, GLOBAL_TS = 45302272, MIN_READ_TID = 12123002, NEXT_WRITE_TID = 12131405
[160239]{-1}[-1/-1] 2014-11-12 12:31:54.059704 e Statement        mvcc_anti_ager.cc(01610) : M_MVCC_TABLES HOST = host:30204, NUM_VERSIONS = 0, MAX_VERSIONS_PER_RECORD = 0, MIN_SNAPSHOT_TS = 45047842, GLOBAL_TS = 45302272, MIN_READ_TID = 12123002, NEXT_WRITE_TID = 12131395
[160239]{-1}[-1/-1] 2014-11-12 12:31:54.059709 e Statement        mvcc_anti_ager.cc(01610) : M_MVCC_TABLES HOST = host:30205, NUM_VERSIONS = 0, MAX_VERSIONS_PER_RECORD = 0, MIN_SNAPSHOT_TS = 45047842, GLOBAL_TS = 45302272, MIN_READ_TID = 12123002, NEXT_WRITE_TID = 12131398
  [160239]{-1}[-1/-1] 2014-11-12 12:31:54.059714 e Statement        mvcc_anti_ager.cc(01610) : M_MVCC_TABLES HOST = host:30207, NUM_VERSIONS = 0, MAX_VERSIONS_PER_RECORD = 0, MIN_SNAPSHOT_TS = 45047842, GLOBAL_TS = 45302272, MIN_READ_TID = 12123002, NEXT_WRITE_TID = 12131395

It seems to be a problem which has troubled some activities especially when the underlying table is huge. The OSS notes i am referring to is (1833835, 1979087, 2083809).

Please let me know if my inference is correct.

[lbreddemann]

First of all, you're running an INSERT not a QUERY here.

That's something profoundly different.

Also, you seem to insert the data into a row store table - why is that?

From what I see up to here, I'd say your problem is that you pushing too many records in a single transaction into a row store table.

There's no hint of a connection to a bug yet.

Besides: querying the hierarchy view like this is not it's intended use.

The whole point of the hierarchy view is that it is not materialized but instead serves as a interface for efficient hierarchy processing. That's what happens when e.g. data is loaded into MS Excel and the Pivot table sends MDX to get the required data for the current state of navigation.

Dumping the table flat out is not the way to use it.

- Lars