SYMPTOM:
From Developer, when users try to edit one attribute with more than 10000 dependencies, it takes longer time than expected (more than 5 minutes) to load the attribute editor.
CAUSE:
By enabling the SQL trace, it is found that all dependencies of the attribute are listed and insert into a temp table and joined with other tables based on different object types. This takes a very long time. But actually, only specific dependent objects like logical tables (object subtype=3840) are used for displaying of attribute editor interface. When the number of dependencies increases, the performance gets worse.
--------------------------------------------------------------------------------------------
SQL Trace:
1: List all dependencies of the attribute:
CREATE TEMPORARY TABLE T_TP001C3F31D1DCD0 (id CHAR(32)) CHARACTER SET utf8;
SELECT OBJECT_ID, PROJECT_ID FROM DSSMDOBJDEPN aou WHERE aou.DEPN_OBJID='<attribute_id>' AND aou.DEPN_PRJID IN ('xxx', 'xxx') AND aou.PROJECT_ID='xxx' GROUP BY OBJECT_ID, PROJECT_ID HAVING COUNT(0) >= 1;
INSERT INTO T_TP001C3F31D1DCD0 VALUES (...);
DROP TABLE T_TP001C3F31D1DCD0;
2: List details of objects with below conditions:
SELECT * FROM DSSMDOBJINFO info LEFT JOIN DSSMDOBJTRNS ... INNER JOIN DSSMDOBJDEPN WHERE info.SUBTYPE=3840 AND ...
- info.SUBTYPE=3840
- info.OBJECT_TYPE IN (14) OR info.SUBTYPE IN (5632))
- info.OBJECT_TYPE=3 AND dep3.DEPNOBJ_TYPE=15
- info.OBJECT_TYPE=15 AND info.OBJECT_STATE=1
- info.OBJECT_TYPE=3 AND dep3.DEPNOBJ_TYPE=53
- info.SUBTYPE=3842
-------------------------------------------------------------------------------------------
ACTION:
This has been defined as a defect in Metadata SQL search, and has been fixed in version 10.4.6 and 10.10.
Article Reference Number: KB440313 KB440313
